CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
nirvanastack
nirvanastack

Reputation: 463

inserting in table before declaring cursor

Can I use the insert statement with values coming from a select statement on another table in EXECUTE IMMEDIATE?

Like :

execute immediate('insert into table values(select rowid from table where column='NEW')');

if there is no possibility of this can I put an insert statement before the declaration of a cursor in a procedure.

thanks a lot.

Upvotes: 0

Views: 751

Answers (2)

Ollie
Ollie

Reputation: 17538

Yes you can but you'd need to sort out your syntax and quotes:

execute immediate('insert into table (column_name) select rowid from table where column=''NEW''');

EDIT:

Better still, use a bind variable instead of the hard coded reference to the column name.

Upvotes: 1

Tebbe
Tebbe

Reputation: 1372

What results did you get when you tested it?

Your syntax is a little off, but, as you can see below, it's definitely doable.

SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1 = 0;

Table created.

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'INSERT INTO emp2 (empno, ename, job) SELECT e.empno, e.ename, e.job FROM emp e WHERE e.deptno = 10';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM emp2;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

SQL>

Upvotes: 0

Related Questions