Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Handling CLOB, REF_CURSOR in Oracle 10g R2

Posted on 2011-03-22
15
Medium Priority
?
984 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
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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

704 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