Execute query stored in variable and read result in Oracle

Question

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.

If you are dynamically generating the query, do you know what the result set will look like at compile time? From your example, it looks like you are assuming that the dynamic query will have a column named val that is a varchar2 which could have a value of "TEST". Do you know at compile time the other columns that will be returned as part of the result set? — Justin Cave
– Justin Cave, Commented Dec 21, 2020 at 13:02
Yes my result set columns are defined, I have even modified the query for understanding. — Rohan Sampat
– Rohan Sampat, Commented Dec 21, 2020 at 13:06

Justin Cave · Accepted Answer · 2020-12-21 13:26:22Z

2

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

answered Dec 21, 2020 at 13:26

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rohan Sampat Over a year ago

Thanks a lot, it worked. What if I only want 1 column from select statement in cursor, because I have 10-15 columns to return from result set and only 3 columns to save in table

Justin Cave Over a year ago

@RohanSampat - You'd likely either have to modify the query to only return those 3 columns or declare local variables for the columns you don't care about that you ignore. If the query happens to select every column from a particular table, you could fetch the data into a local variable declared as a table_name%rowtype record and then reference fields from that record. Or you could declare a custom record type that matches the definition of the cursor and reference the fields from that but that probably doesn't meaningfully simplify the code over declaring additional local variables.

Collectives™ on Stack Overflow

Execute query stored in variable and read result in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related