create or replace procedure p1
(o1 out sys_refcursor, i1 in varchar2, i2 in varchar2, i3 in varchar2) is
/* ROUTINE #01.
THIS PROCEDURE RETURNS A REF CURSOR CONTAINING A VARIABLE NUMBER OF COLUMNS.
THE NUMBER OF COLUMNS RETURED IS BASED ON THE VALUES OF THE OTHER ARGUMENTS PASSED TO THE PROCEDURE.
THIS ROUTINE SUCCEEDS. */
begin
if i1 = 'Y' then
open o1 for select 'A' f1, 'B' f2 from dual;
end if; -- if i1 = 'Y' then.
if i2 = 'Y' then
open o1 for select 'A' f1, 'C' f3 from dual;
end if; -- if i1 = 'Y' then.
if i3 = 'Y' then
open o1 for select 'A' f1, 'B' f2, 'C' f3 from dual;
end if; -- if i1 = 'Y' then.
end p1; Procedure created.
<<routine_02>> declare
/* ROUTINE #02.
THIS ROUTINE WILL CALL PROCEDURE "P1" AND PRINT OUT THE VALUES OF THE VARYING COLUMNS.
WHERE I1 = Y, COLUMNS F1 AND F2 ARE RETURNED.
WHERE I2 = Y, COLUMNS F1 AND F3 ARE RETURNED.
WHERE I3 = Y, COLUMNS F1, F2 AND F3 ARE RETURNED.
PLEASE NOTE THAT THE METADATA OF THE REF CURSOR IS KNOWN IN ADVANCE AND COLUMN VALUES ARE PRINTED VIA HARD-CODED COLUMN NAMES.
THIS ROUTINE SUCCEEDS. */
lC1 sys_refcursor;
type t1 is record (f1 varchar2(1), f2 varchar2(1));
type t2 is record (f1 varchar2(1), f3 varchar2(1));
type t3 is record (f1 varchar2(1), f2 varchar2(1), f3 varchar2(1));
lT1 t1;
lT2 t2;
lT3 t3;
l1 varchar2(1) default 'Y';
l2 varchar2(1) default null;
l3 varchar2(1) default null;
lSQL varchar2(4000);
begin
p1(lC1, l1, l2, l3);
<<loo1>> loop
fetch lC1 into lT1;
exit when lC1%notfound;
lSQL := 'begin dbms_output.put_line(''lT1.f1: ['' || :x1 || ''], lT1.f2: ['' || :x2 || '']''); end;';
execute immediate lSQL using lT1.f1, lT1.f2;
end loop loop1;
close lC1;
l1 := null;
l2 := 'Y';
l3 := null;
p1(lC1, l1, l2, l3);
<<loo1>> loop
fetch lC1 into lT2;
exit when lC1%notfound;
lSQL := 'begin dbms_output.put_line(''lT2.f1: ['' || :x1 || ''], lT2.f3: ['' || :x3 || '']''); end;';
execute immediate lSQL using lT2.f1, lT2.f3;
end loop loop1;
close lC1;
l1 := null;
l2 := null;
l3 := 'Y';
p1(lC1, l1, l2, l3);
<<loo1>> loop
fetch lC1 into lT3;
exit when lC1%notfound;
lSQL := 'begin dbms_output.put_line(''lT3.f1: ['' || :x1 || ''], lT3.f2: ['' || :x2 || ''], lT3.f3: ['' || :x3 || '']''); end;';
execute immediate lSQL using lT3.f1, lT3.f2, lT3.f3;
end loop loop1;
close lC1;
end routine_02; lT1.f1: [A], lT1.f2: [B]
lT2.f1: [A], lT2.f3: [C]
lT3.f1: [A], lT3.f2: [B], lT3.f3: [C]
<<routine_03>> declare
/* ROUTINE #03.
THIS ROUTINE USES DMBS_SQL TO DYNAMICALLY DISCOVER COLUMN NAMES AND VALUES OF A DBMS_SQL CURSOR.
THIS ROUTINE IS SIMPLY TO ILLUSTRATE THE CLASSICAL USE OF THE DBMS_SQL PACKAGE.
THIS ROUTINE SUCCEEDS. */
c number;
d number;
e number;
lSQL varchar2(4000) := 'select ''A'' f1, ''B'' f2, ''C'' f3 from dual';
col_cnt number;
rec_tab dbms_sql.desc_tab;
rec_rec dbms_sql.desc_rec;
l1 varchar2(1);
l2 varchar2(1);
l3 varchar2(1);
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, lSQL, dbms_sql.native);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);
dbms_output.put_line('col_cnt: [' || trim(to_char(col_cnt)) || '].');
<<loop1>> for i in 1 .. rec_tab.last loop
rec_rec := rec_tab(i);
dbms_output.put_line(rec_rec.col_name);
end loop loop1;
dbms_sql.define_column(c, 1, l1, 1);
dbms_sql.define_column(c, 2, l2, 1);
dbms_sql.define_column(c, 3, l3, 1);
<<loop1>> loop
e := dbms_sql.fetch_rows(c);
if e > 0 then
dbms_sql.column_value(c, 1, l1);
dbms_sql.column_value(c, 2, l2);
dbms_sql.column_value(c, 3, l3);
dbms_output.put_line('l1: [' || l1 || '], l2: [' || l2 || '], l3: [' || l3 || ']');
else
exit;
end if; -- if e > 0 then.
end loop loop1;
end routine_03; col_cnt: [3].
F1
F2
F3
l1: [A], l2: [B], l3: [C]
<<routine_04>> declare
/* ROUTINE #04.
THIS ROUTINE USES DMBS_SQL TO DYNAMICALLY DISCOVER COLUMN NAMES AND VALUES OF A DBMS_SQL CURSOR.
THIS ROUTINE IS SIMPLY TO ILLUSTRATE THE CLASSICAL USE OF THE DBMS_SQL PACKAGE.
THIS ROUTINE IS EXACTLY THE SAME AS ROUTINE #03, EXCEPT THAT BIND VARIABLES ON THE DBMS_SQL CURSOR ARE USED.
AGAIN, THIS ROUTINE IS PROVIDED TO SIMPLY ILLUSTRATE THE CLASSICAL USE OF DBMS_SQL METHODS.
THIS ROUTINE SUCCEEDS. */
c number;
d number;
e number;
lSQL varchar2(4000) := 'select :bv1 f1, :bv2 f2, :bv3 f3 from dual';
col_cnt number;
rec_tab dbms_sql.desc_tab;
rec_rec dbms_sql.desc_rec;
l1 varchar2(1) := 'A';
l2 varchar2(1) := 'B';
l3 varchar2(1) := 'C';
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, lSQL, dbms_sql.native);
dbms_sql.bind_variable(c, ':bv1', l1);
dbms_sql.bind_variable(c, ':bv2', l2);
dbms_sql.bind_variable(c, ':bv3', l3);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);
dbms_output.put_line('col_cnt: [' || trim(to_char(col_cnt)) || '].');
<<loop1>> for i in 1 .. rec_tab.last loop
rec_rec := rec_tab(i);
dbms_output.put_line(rec_rec.col_name);
end loop loop1;
dbms_sql.define_column(c, 1, l1, 1);
dbms_sql.define_column(c, 2, l2, 1);
dbms_sql.define_column(c, 3, l3, 1);
<<loop1>> loop
e := dbms_sql.fetch_rows(c);
if e > 0 then
dbms_sql.column_value(c, 1, l1);
dbms_sql.column_value(c, 2, l2);
dbms_sql.column_value(c, 3, l3);
dbms_output.put_line('l1: [' || l1 || '], l2: [' || l2 || '], l3: [' || l3 || ']');
else
exit;
end if; -- if e > 0 then.
end loop loop1;
end routine_04; col_cnt: [3].
F1
F2
F3
l1: [A], l2: [B], l3: [C]
<<routine_05>> declare
/* ROUTINE #05.
THIS ROUTINE IS EXACTLY THE SAME AS ROUTINE #04, EXCEPT THAT IT ATTEMPTS TO RETRIEVE A REF CURSOR FROM PROCEDURE P1.
THIS ROUTINE THEN ATTEMPTS TO DYNAMICALLY DISCOVER COLUMN NAMES AND VALUES USING THE DBMS_SQL PACKAGE.
THE DBMS_SQL PACKAGE IS THE ONLY KNOWN WAY TO DYNMICALLY DISCOVER CURSOR PROPERTIES (CURSOR PROPERTIES LIKE COLUMN NAME AND VALUE).
THIS ROUTINE FAILS, AS THE DBMS_SQL PACKAGE CANNOT RETRIEVE REF CURSORS.
WHAT THE OBJECTIVE OF THIS SCRIPT IS, IS TO FIND A WAY TO DYNAMICALLY DISCOVER CURSOR PROPERTIES FROM A REF CURSOR.
IF THAT SOLUTION IS VIA DBMS_SQL PACKAGE, FINE. IF NOT, FINE.
THIS RESOURCE CLEARLY STATES THAT THE DBMS_SQL PACKAGE CANNOT BE USED TO PROCESS REF CURSORS:
https://asktom.oracle.com/pls/apex/asktom.search?tag=using-dbms-sql-and-returning-a-ref-cursor
SO THE QUESTION IS:
HOW DO WE DYNAMICALLY DISCOVER CURSOR PROPERTIES FROM A REF CURSOR?
THE REASON THIS IS DESIRED IS BECAUSE WE HAVE PROCEDURES THAT USE DYNAIC SQL THAT IS USED TO RETURN REF CURSORS FROM PROCEDURES.
WE WOULD LIKE TO BE ABLE TO WRITE ROUTINES (IN PLSQL) THAT RECEIVE THESE REF CURSORS AND DYNAMICALLY DISCOVER PROPERTIES.
THIS ROUTINE FAILS. */
c number;
d number;
e number;
lSQL varchar2(4000) := 'call p1(:o1, :i1, :i2, :i3)';
col_cnt number;
rec_tab dbms_sql.desc_tab;
rec_rec dbms_sql.desc_rec;
o1 sys_refcursor;
l1 varchar2(1);
l2 varchar2(1);
l3 varchar2(1);
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, lSQL, dbms_sql.native);
dbms_sql.bind_variable(c, 'o1', o1);
dbms_sql.bind_variable(c, 'i1', l1);
dbms_sql.bind_variable(c, 'i2', l2);
dbms_sql.bind_variable(c, 'i3', l3);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);
dbms_output.put_line('col_cnt: [' || trim(to_char(col_cnt)) || '].');
<<loop1>> for i in 1 .. rec_tab.last loop
rec_rec := rec_tab(i);
dbms_output.put_line(rec_rec.col_name);
end loop loop1;
dbms_sql.define_column(c, 1, l1, 1);
dbms_sql.define_column(c, 2, l2, 1);
dbms_sql.define_column(c, 3, l3, 1);
<<loop1>> loop
e := dbms_sql.fetch_rows(c);
if e > 0 then
dbms_sql.column_value(c, 1, l1);
dbms_sql.column_value(c, 2, l2);
dbms_sql.column_value(c, 3, l3);
dbms_output.put_line('l1: [' || l1 || '], l2: [' || l2 || '], l3: [' || l3 || ']');
else
exit;
end if; -- if e > 0 then.
end loop loop1;
end routine_05; ORA-06550: line 30, column 11: PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'More Details: https://docs.oracle.com/error-help/db/ora-06550
drop procedure p1 Procedure dropped.