Hi Tom,
Is there a way to fetch opened cursor into a generic type?
I have this scenario,
PACKAGE test_pkg as
TYPE ref_cur IS REF CURSOR;
...
--the procedure definitions
...
END test_pkg ;
PACKAGE BODY test_pkg AS
procedure one (pi_transaction_id IN Number ,po_ref_cur OUT ref_cur )
is
begin
open ref_cur for
select t1.name, t2.age, t1.country from t1
inner join t2 on t1.Pk = t2.FK;
end;
End one ;
procedure two(pi_transaction_id IN Number ,po_ref_cur OUT ref_cur )
is
begin
open ref_cur for
select * from t;
end;
End two;
I want to call the above mentioned procedures logically in a new procedure and insert the returned result into a Temp table.
My question again is, the data set (or the number of columns, sequence/names of the columns) returned from procedures are different from each other.
So can I have a common oracle type to fetch the result in my new procedure?
My new procedure is going to be something like this.
procedure common (pi_trans_id IN Number) is
ref_cur test_pkg.ref_cur;
begin
if (100 = pi_trans_id) then
test_pkg.one(2,ref_cur);
--here i want to read ref_cur and insert into a temp table row by row
else
test_pkg.two(4,ref_cur);
--here i want to read ref_cur and insert into a temp table row by row
end if;
Thank you very much
I'm a bit lost here - if you have a separate IF test for each type of cursor as you have provided:
procedure common (pi_trans_id IN Number) is
ref_cur test_pkg.ref_cur;
begin
if (100 = pi_trans_id) then
test_pkg.one(2,ref_cur);
--here i want to read ref_cur and insert into a temp table row by row
else
test_pkg.two(4,ref_cur);
--here i want to read ref_cur and insert into a temp table row by row
end if;
Then why wouldnt you have just a separate inserts under each IF.
If you are looking for a SINGLE code set to handle *any* number of rows and columns, you need type 4 dynamic SQL. An example of that below
SQL> create or replace procedure print_vertical(p_query varchar2) is
2
3 l_theCursor integer default dbms_sql.open_cursor;
4 l_columnValue varchar2(4000);
5 l_status integer;
6 l_descTbl dbms_sql.desc_tab;
7 l_colCnt number;
8 n number := 0;
9 procedure p(msg varchar2) is
10 l varchar2(4000) := msg;
11 begin
12 while length(l) > 0 loop
13 dbms_output.put_line(substr(l,1,80));
14 l := substr(l,81);
15 end loop;
16 end;
17 begin
18 execute immediate
19 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
20
21 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
22 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
23
24 for i in 1 .. l_colCnt loop
25 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
26 end loop;
27
28 l_status := dbms_sql.execute(l_theCursor);
29
30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31 for i in 1 .. l_colCnt loop
32 dbms_sql.column_value( l_theCursor, i, l_columnValue );
33 p( rpad( l_descTbl(i).col_name, 30 )
34 || ': ' ||
35 l_columnValue );
36 end loop;
37 dbms_output.put_line( '-----------------' );
38 n := n + 1;
39 end loop;
40 if n = 0 then
41 dbms_output.put_line( chr(10)||'No data found '||chr(10) );
42 end if;
43 end;
44 /
Procedure created.
Check the DBMS_SQL doc for full details on how to parse, describe and fetch the columns.
Hope this helps