Retrieving ref cursor results into a collection or referencing a execute immediate results outside of the executed command?

Posted on 2006-07-16
Last Modified: 2012-06-27
Is there any way to return a ref cursor value to a varray collection?
Here's my situation.
Based on a set of parameters I receive, I assemble a query string.
The number of columns in the query string varies for each call to the procedure based on the parameters it receives.
Currently I use a ref_cursor to pull the values into a predefined table structure that has 150 columns (each is a varchar2(400), not ideal I know, but for my needs that's ok). If the query string only needs to return three columns, I slap on 147 ',0's to the end of the query string, so that I'm retrieving the number of columns that matches the table structure. Once the ref cursor runs, I assign each column that is actually needed to a varray() and continue on to perform various calculations with those values. All of the 0s get ignored and are just wasted space unless the query happens to return up to 150 columns. If the query returns over 150 columns, the procedure breaks of course. There are several instances where I need to exceed 150 columns.
I of course could define the ref cursor table as 400 columns or some other higher number, but then what happens when I exceed that number? Realistically, I'd like to max out the number of columns returned by the query at around 7000 or 8000.
Here are my current known possible solutions.
1) Define a table for the ref cursor with 7000 columns.
The downside of this is that most of the time I will be using less than 100 columns and just have a lot of wasted overhead.
2) Define the ref cursor table to be inserted into as one column which is of datatype long. Convert my query string into one column of all the columns I want, concatenated together by some unique identifier that I can then parse and break out all column values to my array values once it's retrieved by the ref cursor.
The downside of this is that I have the overhead of parsing ever single row returned by the cursor to assign all the values in each.
3) Use dbms_sql and execute immediate to create the results I need, including a table that changes sizes to meet the needs of each specific procedure call based on the parameters passed.
The downside of this is that once the ref_cursor has run and it's values have been assigned to the proper arrays, I need to do many more things with the code. Probably a couple thousand lines more. I would need to put all those lines into the query string definition to be executed.

What I ideally need and will grant points based on a solution provided for either.
1) The ability to retrieve a query string into a ref cursor that can assign the retrieved values into an array. I can define the array with a maximum of 7000 records and only poplulate as many values as columns actually exist. At least the values won't be assigned to a hard coded column name.
-- OR --
2) The ability to run execute immediate in the middle of my procedure and have the execute immediate assign values to variables that the rest of my procedure can reference.

So far I haven't been able to get either of these options working. Would either be possible and could someone provide me an example for either?
Question by:KurtRuckus
  • 2
  • 2
LVL 14

Expert Comment

ID: 17118541
For the 2 part
>>2) The ability to run execute immediate in the middle of my procedure and have the execute immediate assign values to variables that the rest of my procedure can reference.

To assign the results of a dynamic query to a variable you could do this

EXECUTE IMMEDIATE <qry_string> into <var_name>

This var_name could be either a simple varchar2 or a rowtype column

Author Comment

ID: 17118869
Thanks for the reply.
This is definitely a help, but doens't quite get me where I need to be.
As the number of columns in the query_string is variable, so would be the number of columns in the execute into string.
Here's a very simple example.

     type t1 is varray(10) of varchar2(400);
     type nt1 is varray(10) of t1; -- multilevel varray type
     -- initialize multilevel varray
     nva nt1;
     query_string varchar2(32000);
query_string := 'select owner, table_name from all_tables where rownum <  10';
execute immediate query_string into nva;

-- I can then do things with my data in the nva collection.


Unfortunately I get an error.
execute immediate query_string into nva;
ERROR at line 9:
ORA-06550: line 9, column 37:
PLS-00597: expression 'NVA' in the INTO list is of wrong type
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

The next time I run the procedure, my query_string may have 5, 10 columns depending on inputs from the enduser (which I didn't bother setting up for the example.
If I could dynamically create the execute immediate query and then execute immediate within an execute immediate and return those results, that would work too.

execute immediate 'execute immediate select appno, dimno, dim_level from bhmd_dim_items where rownum = 1; into nva(1)(1), nva(2)(1),nva(3)(1)';

Unfortunately that throws an error as well.
If I'm overlooking something here, please let me know.

LVL 14

Accepted Solution

sathyagiri earned 500 total points
ID: 17119039
I think you can do this using dbms_sql.

Check this link, I guess this is something similar to what u're looking for.
LVL 27

Expert Comment

ID: 17121924
Hi Kurt,
My suggestion here is why dont u create the table on the fly and do your procedural logic using dynamic SQL.
Something like the following piece of code.

 l_str varchar2(4000);
 execute immediate 'create table temp_tab(id number)';
 execute immediate 'insert into temp_tab(id) values(20)';
 execute immediate 'insert into temp_tab(id) values(40)';

 -- your code goes below.
 l_str := 'declare
   type tab_type is table of temp_tab%rowtype index by binary_integer;
   l_arr tab_type;
   select * bulk collect into l_arr
   from temp_tab;
   when others then
 execute immediate l_str;
 execute immediate 'drop table temp_tab';


Here you generate the table creation script using your number of arguments. And use the array to do your processing.
But your processing has to be done as a dynamic pl/sql block thats all.

Hope this helps. Let me know.


Author Comment

ID: 20001430
Somehow I overlooked Sujith80's solution. I'm going to have to play with it for a while, but it may open the door to create the solution I was looking for. Sorry for the delayed response, it's been a crazy year. I hope to have an answer in the next month or so.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question