Solved

Handling CLOB, REF_CURSOR in Oracle 10g R2

Posted on 2011-03-22
15
968 Views
Last Modified: 2012-05-11
Hi Experts,
We are using Oracle 10 R2

Here is problem description:

We have a dynamic query which is being returned as ref_cursor
We do lot of calculations and build a variable containing csv values which is being used in dynamic query.

The problem here is because Oracle 10 g R2 doesn’t support CLOB’s as REF CURSOR, our procedure is failing when variable holding csv values grows to large extent.  The error is ORA-06502: PL/SQL: numeric or value error

Please let us know the workaround

Please find sample code statement

DECLARE
   var_csv   CLOB;
   var_cur   SYS_REFCURSOR;
BEGIN
   -- processing logic to build var_csv
   LOOP
      var_csv := var_csv || ',' || v_some_values;
   END LOOP;

   -----

   OPEN var_cur FOR 'SELECT COL1 FROM TABLE1
 WHERE COL2 IN ' || var_csv;
END;

Open in new window

0
Comment
Question by:ajexpert
  • 8
  • 6
15 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 35197351
You can create a  temporary table, put csv values to it and use this table in your procedure:

OPEN var_cur FOR 'SELECT COL1 FROM TABLE1 WHERE COL2 IN (SELECT csv_value_col FROM T_TEMP);

I hope this will be a little help for you.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35197852
I cannot use TEMP TABLE

Experts, Is there any work around, need to come up with implementation idea by today
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35198275
What all can you use?

If you can declare a pipelined function and/or external TYPE, you might be able to get around this but a Global Temp table is so much easier.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Author Comment

by:ajexpert
ID: 35204274
@slightwv:
ok, in dev environment, I created type array of  NUMBER

Also I wrote a PIPELINED table function FN_RET_VAR_RECS, where I incorporated all the processing logic.  So, instead of building CSV's now I am creating a table of values.


But I cannot use in SELECT statement.


-- this gives error
SELECT COL1 FROM TABLE1 WHERE COL1 IN
(SELECT FN_RET_VAR_RECS FROM DUAL);

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35204304
Well, I figured out

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35206312
Is there anything more you need on this?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35211777
There are lot more challenges.

Piplelined function is bit slower in performance.  So I am trying to explore other options.

Is there any way apart from using temporary tables or pipelined functions


0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35211892
I experimented a little with pl/sql tables and varrays but they would still require an external TYPE to be created.

Never got anything fully working.  Once things got 'complicated' I went back to a global temporary table or pipelined function.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35214531
I decided to take one last run through the docs and think I found it buried there.  I initially looked into this but failed to come across the 'Note' before.

Now to just find a working example using this version:

DBMS_SQL.PARSE

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#i997676

Note:
The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35426355
Well, breaking the statement will cause much of code change as the package is already complicated and it uses associative arrays.

Breaking statement means I have to tweak the associative arrays in the manner they are being populated.

Anyways, I will close the question in day or two.

Thanks
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35434001
@slightwv:

Do you think PIPLELINED TABLE function should perform better than REF CURSOR?

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35434038
Never really times the differences.  I suggest you set up some quick tests if performance is a concern.

Since the old ref cursor code dies, does it really matter though?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35707347
Today, I got some time to work on this issue.

I am not sure if this can be NEW question.

 I have made few changes, but its taking 20 minutes to execute.

There are 2 cursors which loops for about 100,000 records and we do lot of checks (IF ELSE).

Now the question

Is there anyway to Debug or TRACE PL/SQL pacakge WITHOUT DBA's assistance i.e. not using TRACE and TKPROF?

I have limited priveleges on the SCHEMA but I cannot create a file which can be used for tracing.


0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35707788
I just saw your new question.  You are in good hands over there so I'll not post anything here.
0
 
LVL 14

Author Closing Comment

by:ajexpert
ID: 35723723
The comments didn't solve the problem though I got some knowledge on various other apporaches, have opened another question.

Thanks for efforts sligtwv
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 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