limitation of varchar2 in pl/sql in parameter

how to pass a comma separated String from java to store procedure so that it can be used in the IN Clause of aquery in store procedure. The lenght of the String is 80K. is there any option other than using Clob. Will Clob work if we replace it for varchar
MaheshArunAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The lenght of the String is 80K. is there any option other than using Clob.
yes, LONG
>Will Clob work if we replace it for varchar
no, varchar is limited to 4000 (4K)

what I did is I created a function that returns pipelined table results from the clob parameter, which I then can use the in query:

select * from yourtable
where somefield in ( select COLUMN_VALUE from TABLE(GET_NUM_LIST(long_parameter))


SQL> CREATE OR REPLACE TYPE MYNUMTYPE AS TABLE OF NUMBER
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION GET_NUM_LIST(
  2     P_STR   IN   LONG )
  3     RETURN MYNUMTYPE PIPELINED
  4  AS
  5     L_STR         LONG      DEFAULT P_STR || ',';
  6     L_N           NUMBER;
  7     V_ID          NUMBER;
  8     V_MYNUMTYPE   MYNUMTYPE;
  9  BEGIN
 10     LOOP
 11        L_N := INSTR( L_STR, ',' );
 12        EXIT WHEN( NVL( L_N, 0 ) = 0 );
 13        V_ID := NVL( LTRIM( RTRIM( SUBSTR( L_STR, 1, L_N - 1 ))), -9999999999
);
 14  
 15        IF V_ID != -9999999999 THEN
 16           V_MYNUMTYPE := MYNUMTYPE( V_ID );
 17           PIPE ROW( V_ID);
 18        END IF;
 19  
 20        L_STR := SUBSTR( L_STR, L_N + 1 );
 21     END LOOP;
 22  
 23     RETURN;
 24  END;
 25  /


0
 
schwertnerCommented:
No, you have no option. You have to use CLOB.
Reasons:
1. Look here http://www.ss64.com/orasyntax/datatypes.html
LONG as column can be 2 Gigabytes but as PL/SQL variable only 2 Gigabytes 32760 bytes!!! i.e 32K and you need 80K

2. Long is depricated in Oracle

To manipulate CLOBs you have to use the functions in the package DBMS_LOB.

Workaround: Use 3 LONG parameters instead one and divide the 8OK string in chunks.
But I will not recommend this.
0
 
actonwangCommented:
>>how to pass a comma separated String from java to store procedure so that it can be used in the IN Clause of aquery in store procedure. The lenght of the String is 80K. is there any option other than using Clob. Will Clob work if we replace it for varchar

As you are probably aware of and other experts said, VARCHAR2 or even LONG has a limitation of 32767 in PL/SQL. Also LONG is depreciated and not recommended to use which is preferred over by CLOB.

Not sure why you don't want to use CLOB, if your string is over 32767, you are better off to use CLOB.

0
 
actonwangCommented:
Actually I understand your concern( you are using java store prcedure call), your trade-off could be:

create or replace proc1(p1 varchar2, p2 varchar2, p3 varchar3 ...)
as
   p  clob;
begin
   p := p1;
   dbms_lob.append(p,p2);
   dbms_lob.append(p,p3);
   
   -- use p afterwards
end;
/


hopefully it helps.
acton
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.