Retrieving ref cursor results into a collection or referencing a execute immediate results outside of the executed command?
Posted on 2006-07-16
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?