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
Solved

limitation of varchar2 in pl/sql  in parameter

Posted on 2006-07-10
4
883 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 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 48

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 78
passing parameters to sql script oracle 4 60
error in my cursor 5 41
pivot rows to columns 1 34
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

861 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