Solved

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

Posted on 2006-07-16
7
1,749 Views
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?
Thanks
Kurt
0
Comment
Question by:KurtRuckus
  • 2
  • 2
7 Comments
 
LVL 14

Expert Comment

by:sathyagiri
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
0
 

Author Comment

by:KurtRuckus
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.

declare
     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);
BEGIN
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.

END;

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.
Thanks!
Kurt






0
 
LVL 14

Accepted Solution

by:
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.

http://www.oracle.com/technology/oramag/code/tips2003/042003.html

http://forums.oracle.com/forums/thread.jspa?messageID=1356930
0
 
LVL 27

Expert Comment

by:sujith80
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.

----------------------------
declare
 l_str varchar2(4000);
begin
 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;
  begin
   select * bulk collect into l_arr
   from temp_tab;
   dbms_output.put_line(l_arr.count);
  exception
   when others then
     dbms_output.put_line(''errrror'');
  end;';
 execute immediate l_str;
 execute immediate 'drop table temp_tab';
end;

-----

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.

Rgds
Sujith.
0
 

Author Comment

by:KurtRuckus
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.
0

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.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now