How do I fix an identifier to be declared in a stored procedure?
I create a procedure and then execute it in SQL plus. The procedure is correctly created / updated as you can see from the following code.
SQL> create or replace procedure add_sal(i_empno number, addsal out number)
2 is
3 begin
4 select sal+1000
5 into addsal
6 from emp
7 where empno=i_empno;
8 end;
9 /
Procedure created.
When I try to follow the procedure, I get an error that I don't understand.
SQL> exec add_sal(i_empno,addsal);
BEGIN add_sal(i_empno,addsal); END;
*
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00201: identifier 'I_EMPNO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Where am I going wrong?
+3
source to share
2 answers
SQL> exec add_sal (i_empno, addal);
You need to pass in values , not parameter names .
Also, addsal
is an OUT parameter , you need to declare first .
In SQL * Plus :
SQL> CREATE OR REPLACE PROCEDURE add_sal(
2 i_empno NUMBER,
3 addsal OUT NUMBER)
4 IS
5 BEGIN
6 SELECT sal+1000 INTO addsal FROM emp WHERE empno=i_empno;
7 END;
8 /
Procedure created.
SQL>
SQL> SHO ERR
No errors.
SQL>
SQL> variable addsal NUMBER
SQL>
SQL> EXEC add_sal(7369, :addsal);
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT addsal
ADDSAL
----------
1800
SQL>
Alternatively, regardless of SQL * Plus , you can execute it as an anonymous block :
SQL> set serveroutput on
SQL> DECLARE
2 o_addsal NUMBER;
3 BEGIN
4 add_sal(7369, o_addsal);
5 DBMS_OUTPUT.PUT_LINE('The output is : '||o_addsal);
6 END;
7 /
The output is : 1800
PL/SQL procedure successfully completed.
SQL>
+2
source to share