Solved

Handling CLOB, REF_CURSOR in Oracle 10g R2

Posted on 2011-03-22
15
972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

752 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