Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Large SQL body for a Cursor

653718Aug 5 2008 — edited Aug 6 2008
I am trying to build a store procedure to return a rowset from DB. I am using a cursor that I build dynamically based on some input parameters. One of the parameters is actually the where clause of the sql statement and sometimes this can be really big, over 4000 characters. This will end up in having the body of the cursor greater than 4000 characters. As a matter of fact one sql statement I was testing had 16K. I can't get it to work, if the body of the cursor is that big. Same SQL statement runs perfect in SQLDeveloper Worksheet.
So I guess my question is: Is there a limitation in the size of the sql statement inside of a cursor?

This is the code:

Procedure doNothing(cursor1 OUT T_CURSOR, param1 VARCHAR2, param2 VARCHAR2)
IS
aaa VARCHAR2(32000);
BEGIN

aaa:='select f1,f2,f3,f4, ' || param1 ||'
from Table T1
where ' || param2 ||'
group by f1,f2,f3,f4, ' || param1 || ' ';

OPEN cursor1 FOR aaa;

END;

both param1 and param2 are larger than 4000 characters.
The error I am geting is:
Message "[1704] ORA-01704: string literal too long\nORA-06512: at \"XXXXXX.YYYYYYY\", line 75\nORA-06512: at line 1\n" string


Thank you!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 3 2008
Added on Aug 5 2008
2 comments
404 views