CODEWITHSUNDEEP
oracle-databaseplsql
Rohan Sampat
Rohan Sampat

Reputation: 970

Execute query stored in variable and read result in Oracle

I have a procedure which returns result set from query which is dynamically generated in oracle. It do returns the result set but what I want is to process from information from the generated result set and save it in a table.

This is my query..

PROCEDURE GetItem(pitem_list in varchar2,
                            PGetData OUT SYS_REFCURSOR)
is
strsql2 long;
BEGIN
strsql2 :='SELECT val, val1, val2 from table1'; ----- This is a sample query as the main query is complex so just for simplicity I wrote this here.
open PGetData for strsql2; ----- This do returns me the result set generated from the query;

Now what I want is to execute the query stored in "strsql2" variable and read the result and process some information..

I thought of executing it from FOR LOOP.

strsql2 :='SELECT val, val1, val2 from table1'; 

for log in (select strsql2 from dual) ---- I even tried execute immediate
loop
if(log.val = 'TEST')
then
insert into table ----
else
update table --
end if;
end loop;
open PGetData for strsql2; --- After saving the result in table then return the result set..

Where I m going wrong here, or is there any other way to do it? I m stuck here.

Upvotes: 1

Views: 1036

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

In that case, you can simply fetch from the cursor into local variables. In this case, I know that my query returns three columns, one an integer, one a string, and one a date so I'm declaring three local variables to hold the results.

declare
  l_sql      varchar2(1000);
  l_rc       sys_refcursor;
  
  l_integer  pls_integer;
  l_string   varchar2(100);
  l_date     date;
begin
  l_sql := q'{select 1 int, 'foo' str, date '2020-12-21' dt from dual}';
  open l_rc for l_sql;
  
  loop
    fetch l_rc 
     into l_integer, l_string, l_date;
    exit when l_rc%notfound;
    
    dbms_output.put_line( 'l_string = ' || l_string ||
                            ' l_integer = ' || l_integer ||
                            ' l_date = ' || to_char( l_date, 'dd-mon-yyyy' ) );
  end loop;
end;
/

A liveSQL link

Upvotes: 2

Related Questions