Pass value out of dynamic select statement
SQL>
SQL> CREATE TABLE MyTable(yourRow INTEGER, yourDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 block_to_execute VARCHAR2(200) :=
3 'BEGIN
4 SELECT YourRow,YourDesc
5 INTO :1, :2 FROM myTable
6 WHERE YourRow = 2;
7 END;';
8
9 YourRow NUMBER;
10 YourDesc VARCHAR2(100);
11 BEGIN
12 EXECUTE IMMEDIATE block_to_execute USING OUT YourRow, OUT YourDesc;
13 DBMS_OUTPUT.PUT_LINE(YourRow || ' ' || YourDesc);
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at line 2
ORA-06512: at line 12
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --
Related examples in the same category