Skip to Main Content
  • Questions

  • Large dynamic Query - string literal too long error

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Srikanth.

Asked: April 29, 2008 - 1:57 pm UTC

Last updated: April 13, 2010 - 7:46 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi,

We have a database that uses ref cursors using the following syntax
OPEN v_ref_cursor FOR SELECT *FROM EMP

Now we have a requirement of introducing paging, sorting, filtering to the existing format

For that we have to make the query dynamic as below

OPEN v_ref_cursor FOR

'SELECT *FROM (' ||
'Select rnum as rowNumber, a.* from ('||
'SELECT *FROM EMP' ||
'Where empNo=' || v_empNo ||
'Order By ' || v_order_by_columns
||') a where rownum <' || v_max ||
') where rowNumber > v_min) '

This can be achieved but the hardest part is how to handle this if the dynamic query is very large( more than 4000 characters)
i have tried doing this it gives me "PLS-00172: string literal too long" error.

Is there any work arround for this?


Thanks in advance

and Tom said...

in 10g and before, native dynamic sql is limited to 32k


I hate your query by the way. A lot.

No bind variables!!!!
Using string concatention!!!!

google "sql injection" please - you are subject to it in a huge way.


You do not need to use string concatenation here at all. Using the technique outlined here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279
(yes, i know, that example uses dbms_sql, that is not relevant, we need to build that string)

the ONLY thing you might not bind in would be the order by - and that you need to run the inputs through dbms_assert to verify that they are proper and safe.



But, just use a long variable like I did in my example

AND BIND - bind or else.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-06502: PL/SQL: numeric or value error, I belive is similar issue as Srkikath's question

Randhir, March 31, 2010 - 11:56 am UTC

I am trying a execute a large dynamic query which will be built based off the business criteria, but below is a simple setup to show my test. To over come 32K issue I am using overloaded function of DBMS_SQL.parse to take clob, but still getting error in DBMS_SQL.PARSE "ORA-06502: PL/SQL: numeric or value error" (for simplicity I did not use binding in this example)

Any help would be greatly appreciated.


CREATE TABLE test_tb (a NUMBER(10));

DECLARE
counter NUMBER (10) := 1000; -- counter to produce large query
string1 VARCHAR2 (100) := 'update test set a = 1 where 1 = 1 ';
string2 VARCHAR2 (1000)
:= 'OR exists (select * from dual where ''my long sub query'' = ''my long sub query'') ';
d_sql CLOB;
i NUMBER := 0;
cursorid INTEGER;
rowsupdated NUMBER;
BEGIN
string3 := string1;

WHILE i <= counter
LOOP
d_sql := s_sql || string2;
i := i + 1;
END LOOP;

DBMS_OUTPUT.put_line ('length of query is ' || LENGTH (string3));
v_cursorid := DBMS_SQL.open_cursor;
DBMS_SQL.parse (v_cursorid, string3, DBMS_SQL.v7);
rowsupdated := DBMS_SQL.EXECUTE (v_cursorid);
DBMS_OUTPUT.put_line (rowsupdated || ' rows are updated.');
DBMS_SQL.close_cursor (v_cursorid);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

Tom Kyte
April 05, 2010 - 12:41 pm UTC 

EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;


what is the meaning or purpose of that??? The *ONLY THING* that does is hide the actual line number that cause the error. It adds nothing of value, it only detracts from your ability to diagnose anything. ugh. I'll never get it, why??? why do people do this?? anyone - why?



why no version information? It is relevant. I believe you are in 10g (where no such API exists, not with clobs) and not in 11g (where such functionality is supported)


and why doesn't your code even compile?? test_tb in the create table, test in the code. cursorid in the declare, v_cursorid in the body. ugh - why don't people take even the briefest of moments to *test things out*. 


ops$tkyte%ORA11GR2> CREATE TABLE test (a NUMBER(10));

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> DECLARE
  2     string1       clob  := 'update test set a = 1 where 1 = 1 ';
  3     string2       VARCHAR2 (1000) := 'OR exists (select * from dual where ''my long sub query'' = ''my long sub query'') ';
  4     v_cursorid      INTEGER;
  5  BEGIN
  6          for i in 1 .. 1000
  7      loop
  8         string1 := string1 || string2;
  9      end loop;
 10  
 11     DBMS_OUTPUT.put_line ('length of query is ' || LENGTH (string1));
 12     v_cursorid := DBMS_SQL.open_cursor;
 13     DBMS_SQL.parse (v_cursorid, string1, DBMS_SQL.v7);
 14  end;
 15  /
length of query is 79034

PL/SQL procedure successfully completed.


versus

ops$tkyte%ORA10GR2> CREATE TABLE test (a NUMBER(10));

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     string1       clob  := 'update test set a = 1 where 1 = 1 ';
  3     string2       VARCHAR2 (1000) := 'OR exists (select * from dual where ''my long sub query'' = ''my long sub query'') ';
  4     v_cursorid      INTEGER;
  5  BEGIN
  6      for i in 1 .. 1000
  7      loop
  8         string1 := string1 || string2;
  9      end loop;
 10
 11     DBMS_OUTPUT.put_line ('length of query is ' || LENGTH (string1));
 12     v_cursorid := DBMS_SQL.open_cursor;
 13     DBMS_SQL.parse (v_cursorid, string1, DBMS_SQL.v7);
 14  end;
 15  /
length of query is 79034
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13




So, you are using 10g and as written many times before - 10g has a strict limit of 32k - period. You can use the overloaded parse routine that takes a table of 32k strings to overcome this - and that is all.


the api you claim to be using (the one with a clob input) does not exist in your release of the database)

ORA-06502: PL/SQL: numeric or value error

randhir, March 31, 2010 - 12:40 pm UTC

I sincerely apologize for putting up a buggy code, I did test it before but at the time posting I did few variable name changes, below is the working code.

CREATE TABLE test_tb (a NUMBER(10));

DECLARE
counter NUMBER (10) := 1000; -- counter to produce large query
d_sql CLOB := 'update test_tb set a = 1 where 1 = 1 ';
string1 VARCHAR2 (1000)
:= 'OR exists (select * from dual where ''my long sub query'' = ''my long sub query'') ';
i NUMBER := 0;
cursorid INTEGER;
rowsupdated NUMBER;
BEGIN
WHILE i <= counter
LOOP
d_sql := d_sql || string1;
i := i + 1;
END LOOP;

DBMS_OUTPUT.put_line ('length of query is ' || LENGTH (d_sql));
cursorid := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursorid, d_sql, DBMS_SQL.v7);
rowsupdated := DBMS_SQL.EXECUTE (cursorid);
DBMS_OUTPUT.put_line (rowsupdated || ' rows are updated.');
DBMS_SQL.close_cursor (cursorid);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

Thank you
Randhir

Srikanth, April 08, 2010 - 2:54 am UTC

Hi Randhir,
We had a similar problem and we could manage to use Parameterized views to overcome this issue.

We were working on oracle 10.2.0.1 and encountered this string limitation problem. We could manage to replace the big query with parameterized views. If you can manage to replace your big query with parameterized view then this might work for you.

Lets have a brief and interesting introduction about Parameterised view.

Well, To my knowledge a parameterized view is a view that uses a SQL SELECT statement containing a WHERE clause that specifies a filter expression with a parameter.
The parameter values can be supplied later to the view by various means.

I would like to list some of ways of creating parameterized view.

A) you can use a working table(probably global temporary table) into which you insert your parameters. Your view is created to select from the working table to get its parameter values
B) you set parameter values in a memory area called SYSTEM CONTEXT ( using dbms_session.set_context) or set user environment variables (using dbms_application_info.set_client_info
) like client info and refer the parameters in your view.
C) you can create parameter/variable in a package and refer them in your view using a function that returns the values of these parameter/variable.

But before calling the view you need to set a value to the parameter/variable no matter which ever method you use.

What do you think on this tom ??

Thanks
Srikanth.

Tom Kyte
April 13, 2010 - 7:46 am UTC

I think it would be infinitely easier just to use the dbms_sql api that already exists that allows you to parse as big of a string as you like? don't you?


You have a big string in a clob, all you need to do is load it into a plsql index by table of 32k strings (just substr it). dbms_sql.parse is overloaded and has been overloaded to accept that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.