Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

limitation of varchar2 in pl/sql  in parameter

Posted on 2006-07-10
4
Medium Priority
?
890 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:MaheshArun
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 17078936
>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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 600 total points
ID: 17079184
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
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 400 total points
ID: 17081609
>>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
 
LVL 19

Expert Comment

by:actonwang
ID: 17081660
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question