[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

limitation of varchar2 in pl/sql  in parameter

Posted on 2006-07-10
4
Medium Priority
?
888 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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