Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Viraj.

Asked: January 22, 2016 - 1:53 am UTC

Last updated: January 22, 2016 - 2:20 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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

and Connor said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the very quick reply and for the suggestion of DBMS_SQL

Viraj Perera, January 22, 2016 - 2:36 am UTC

Hi Connor,

DBMS_SQL is what exactly I wanted.
Thank you very much.
Missed it out as I am quite less experienced in Oracle pl/sql.

Thanks again.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library