Register / Login
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.
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