Solved

Handling CLOB, REF_CURSOR in Oracle 10g R2

Posted on 2011-03-22
15
959 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 76

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
 
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 76

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
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
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 76

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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 82
querying by the sum of a column in decimal 7 56
null value 15 93
Converting a row into a column 2 43
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

912 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

18 Experts available now in Live!

Get 1:1 Help Now