Solved

limitation of varchar2 in pl/sql  in parameter

Posted on 2006-07-10
4
880 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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 47

Assisted Solution

by:schwertner
schwertner earned 150 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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now