How to execute Dynamic sql with insert statement in Oracle-CodePudding

Below sql command is not working in procedure

PROCEDURE P_EMPDETAIL

AS

V_WHERE := 'E.EMP_ID = 123'B

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEE E ' || V_WHERE || ;

END;

CodePudding user response：

It seems to me there are various issues with your syntax and approach (you shouldn't be using dynamic SQL this way), perhaps you should learn PL/SQL and reference the manuals. The insert statement is also wrong. Below is the correct syntax.

CREATE OR REPLACE PROCEDURE P_EMPDETAIL as
V_WHERE varchar2(100);
BEGIN
    V_WHERE := 'E.EMP_ID = 123';
    EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEE E (colname) values (1) ' || V_WHERE;
END;

CodePudding user response：

Well, not exactly like that (obviously; otherwise, you wouldn't be asking for help).

It is unclear what you want to do because syntax is really strange. If you wanted to insert a row into the table, then:

SQL> CREATE TABLE employees
  2  (
  3     emp_id   NUMBER
  4  );

Table created.

SQL> CREATE OR REPLACE PROCEDURE p_empdetail (par_emp_id IN NUMBER)
  2  AS
  3     l_str  VARCHAR2 (200);
  4  BEGIN
  5     l_str := 'insert into employees (emp_id) values (:1)';
  6
  7     EXECUTE IMMEDIATE l_str
  8        USING par_emp_id;
  9  END;
 10  /

Procedure created.

Testing:

SQL> EXEC p_empdetail(123);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM employees;

    EMP_ID
----------
       123

SQL>