Register / Login
this forum made possible by our volunteer staff, including ...
Roel De Nijs
Stephan van Hulst
JDBC and Relational Databases
Size of string variable exceeding 32767
posted 11 years ago
I'm building a dynamic query in my PL/SQL stored procedure.
Like within a for loop, I keep concatinating the SQL statements into varchar2 variable:
//declaring the variable main_query varchar2(32767); //inside the loop, for each message_id , the main query is built like this.. main_query:= main_query || 'Select acct_name from accts where msg_id='||message_id||' and acct_id in(123456,654321,123442,........)'; ......... Open ss_cursor for main_query;
It works fine. But if there are more messages, the main_query grows large. The problem is the main_query is of type varchar2 and can only store upto 32767 chars.
Now since my query is being built dynamically - its size increases past 32767.
What options do I have. I've read about CLOB and BLOBS but they are tied with database columns. I dont know whether I can use them like standalone variables.
I tried doing something like this - but it did'nt work.
final_query CLOB; main_query:= main_query || 'Select acct_name from accts where msg_id='||message_id||' and acct_id in(123456,654321,123442,........)'; DBMS_LOB.WRITEAPPEND(final_query,length(main_query),main_query);
It comes back with Invalid LOB locator.
Any help would be appreciated.
It is sorta covered in the
JavaRanch Style Guide
Boost this thread!
Auto generate Index scripts
calling a PL/SQL procedure from a Java application
looping in oracle or java
performance using batch-size in set
Implementing CASCADE DELETE using Java