Rattlesnake Rattlesnake - 1 year ago 79
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download