how to use OUT mode in dynamic sql

Question

This is what, I'm trying

  1  declare
  2  stmt VARCHAR2(200):='&query';
  3  emprec emp%ROWTYPE;
  4  BEGIN
  5    EXECUTE IMMEDIATE stmt USING out emprec,in &id;
  6    DBMS_OUTPUT.PUT_LINE('Emp: '||emprec.ename);
  7* END;
SQL> /
Enter value for query: select * into :emprec from emp where empno=:no
old   2: stmt VARCHAR2(200):='&query';
new   2: stmt VARCHAR2(200):='select * into :emprec from emp where empno=:no';
Enter value for id: 7369
old   5:   EXECUTE IMMEDIATE stmt USING out emprec,in &id;
new   5:   EXECUTE IMMEDIATE stmt USING out emprec,in 7369;
  EXECUTE IMMEDIATE stmt USING out emprec,in 7369;
                                   *
ERROR at line 5:
ORA-06550: line 5, column 36:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

But, when I'm changing the 5th line to

EXECUTE IMMEDIATE stmt into emprec USING in &id;

and query select * from emp where empno=:no

Then it is working fine and showing the correct output. Anyone please help me, how to solve this problem

Have you tried using the `RETURNING` clause? – Rachcha Apr 02 '13 at 14:02 — Rachcha, Apr 02 '13 at 14:02

score 0 · Answer 1 · edited Aug 26 '14 at 04:09

To store the result set of the executed query you have to specify a compatible variable by using INTO clause.

INTO clause is used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

DECLARE
  stmt VARCHAR2(200):='select * from emp where empno=:no';
  emprec emp%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE stmt INTO emprec USING &id;
  DBMS_OUTPUT.PUT_LINE('Emp: '||emprec.ename);
END;

how to use OUT mode in dynamic sql

1 Answers1