Hello,
I am new to PL/SQL and I am trying to learn how to incorporate dynamic SQL into my procedure. In a made-up scenario, I want to try to creates a procedure, "update_emp", that updates the salary field by a percentage (that will be passed as an argument) in all records in the "emp" table using dynamic SQL. I want to use a substitution variable to get the percentage increase from the user.
After the procedure is created I want to use an anonymous block that will display the name, employee number, and salary (before the raise). Call the “update_emp procedure (passing the percentage). Then display the name, employee number, and salary (after the raise).
Below is my script which creates the procedure with no errors, but when I run the anonymous block I get an error message (listed below). I checked my table to make sure the procedure worked (and it did), but I still cannot get it to display the before and after parts to the screen. Can anyone help me figure out what I am missing or doing wrong. Thanks in advance.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE update_emp (percent_raised NUMBER, p_empno NUMBER) as
my_string VARCHAR2(2000);
BEGIN
my_string := 'UPDATE emp SET sal = sal * (1+(:percent_raised/100)) WHERE empno = :p_empno';
EXECUTE IMMEDIATE my_string USING percent_raised, p_empno;
END update_emp;
/
show errors;
DECLARE
v_percent NUMBER := &percent;
v_empno NUMBER := &empno;
BEGIN
dbms_output.put_line('Before update: ' ||emp.empno||' '||emp.ename||' '||emp.sal);
update_emp (percent_raised => v_percent, p_empno => v_empno);
dbms_output.put_line('After update: ' ||emp.empno||' '||emp.ename||' '||emp.sal);
END;
/
ERROR at line 5:
ORA-06550: line 5, column 46:
PLS-00357: Table,View or sequence reference ‘EMP.EMPNO’ not allowed in this context.
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
ORA-06550: line 7, column 45:
PLS-00357: Table,View or sequence reference ‘EMP.EMPNO’ not allowed in this context.
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored