Solved

Handling CLOB, REF_CURSOR in Oracle 10g R2

Posted on 2011-03-22
15
956 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á
Comment Utility
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
Comment Utility
I cannot use TEMP TABLE

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

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 14

Author Comment

by:ajexpert
Comment Utility
@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
Comment Utility
Well, I figured out

0
 
LVL 76

Expert Comment

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

Author Comment

by:ajexpert
Comment Utility
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
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.

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
@slightwv:

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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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

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

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

762 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

10 Experts available now in Live!

Get 1:1 Help Now