Rattlesnake Rattlesnake - 12 days ago 6
SQL Question

What is wrong with my PLSQL dynamic code?

I'm learning PLSQL since 3 or 4 months, and now I'm practicing Dynamic SQL and I've write a simple procedure to update last name of an employee with Dynamic SQL. I don´t know what is the problem because when I call the procedure and insert the parameters I receive an error.

CREATE OR REPLACE PROCEDURE upd_emp_dyn(
p_tname VARCHAR2,
p_lname employees.last_name%TYPE,
p_empid employees.employee_id%TYPE)
IS
v_dyn_stmt VARCHAR2(200) := 'UPDATE ' || p_tname ||
' SET last_name = ' ||
p_lname || ' WHERE employee_id = ' ||
p_empid;
v_confirm_stmt VARCHAR2(200) := 'SELECT * FROM ' || p_tname ||
' WHERE employee_id = ' ||
p_empid;
BEGIN
EXECUTE IMMEDIATE v_dyn_stmt;
DBMS_OUTPUT.PUT_LINE(v_confirm_stmt);
END upd_emp_dyn;
/
SHOW ERRORS;

SET SERVEROUTPUT ON
BEGIN
upd_emp_dyn('employees', 'Rooney', 120);
END;
/


Error que empieza en la línea: 2 del comando :
BEGIN
upd_emp_dyn('employees', 'Rooney', 120);
END;
Informe de error -
ORA-00904: "ROONEY": invalid identifier
ORA-06512: at "HR.UPD_EMP_DYN", line 14
ORA-06512: at line 2
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Answer

The best practice (for both performance and security) is to use parameters:

CREATE OR REPLACE PROCEDURE upd_emp_dyn(
  p_tname VARCHAR2,
  p_lname employees.last_name%TYPE,
  p_empid employees.employee_id%TYPE)
  IS
    v_dyn_stmt VARCHAR2(200) := 'UPDATE ' || p_tname ||
                                ' SET last_name = :1 WHERE employee_id = :2';
    v_confirm_stmt VARCHAR2(200) := 'SELECT * FROM ' || p_tname ||
                                    ' WHERE employee_id = :1';
BEGIN
  EXECUTE IMMEDIATE v_dyn_stmt using p_lname, p_empid;
  DBMS_OUTPUT.PUT_LINE(v_confirm_stmt);
END upd_emp_dyn;
/
SHOW ERRORS;

SET SERVEROUTPUT ON
BEGIN
  upd_emp_dyn('employees', 'Rooney', 120);
END;
/

For select statements use following:

declare
  result my_table%rowtype;
  id number := 123;
begin
  execute immediate 'select * from my_table where id = :1' into result using id;
end;
/

Also, it helps you to avoid typo errors (like which caused this question). And of course, you should avoid the use of dynamic SQL if it is possible to use static.