Home » SQL & PL/SQL » SQL & PL/SQL » sql restictions of 4000 charactes in in clause
sql restictions of 4000 charactes in in clause [message #433552] Wed, 02 December 2009 23:22 Go to next message
mhdmehraj
Messages: 3
Registered: November 2008
Junior Member
if vlength <4000 then
open PLInventoryTable for
SELECT INV_ID,SRC_ID,EQ_NAME,INV_DT_TM, VOL_UNIT,LEVEL_UNIT,TEMP_UNIT,DENSITY_UNIT,MASS_UNIT,PRESSURE_UNIT,DATA_QUALITY,CHECKED,RELEASED,INFORMATION_MESSAGE,TOLERANCE FROM PL_INV
where INV_DT_TM >=p_startdate AND
INV_DT_TM <= p_enddate AND
EQ_NAME IN ( select * from THE
( select cast( PLWEB.str2tblText( p_equipments) as PL_TABLE_VARCHAR_TYPE )
from
dual
)
)
order by INV_DT_TM desc;
end if;

if vlength > 4000 then
vsplit:= SUBSTR(SUBSTRB(p_equipments,1,4000),1,INSTR(SUBSTRB(p_equipments,1,4000),',',-1)-1);
vsplit1:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,4000),',',-1)+1);
strqueryforexec_Union1 := ' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit1||''') AS PL_TABLE_VARCHAR_TYPE ) FROM dual )) ';
insert into t2 values( vsplit1);
commit;
end if;



But here vsplit is splitting correctly which is used in the first query of union all,
But vsplit1 is not taking the next 4000 characted which is left from vsplit,

can anybody help on this please
Re: sql restictions of 4000 charactes in in clause [message #433554 is a reply to message #433552] Wed, 02 December 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>can anybody help on this please
I have no idea of neither what is input nor what is expected/desired output.

Before proceeding, PLEASE read section the "How to format your post?" in URL below:
http://www.orafaq.com/forum/t/88153/0/
Be sure to properly format all subsequent posts!
It is requested that you use sqlplus for all subsequent posts to your thread.
CUT & PASTE whole sqlplus session so we can see exactly what you do & how Oracle responds.
Re: sql restictions of 4000 charactes in in clause [message #433557 is a reply to message #433554] Wed, 02 December 2009 23:41 Go to previous messageGo to next message
mhdmehraj
Messages: 3
Registered: November 2008
Junior Member
thanks for your reply.

this is my procedure , here for the input parameter p_equipments i will get a list of comma separated values. if my list goes above 4000 character, it will split up with a union all having first 4000 in one query and the rest in in another query.

My substring usage for vsplit is working properly.
but my vsplit1 function is not working properly when i get more than 8000 characters ,

PROCEDURE GETPLINVTABLEDATA (p_startdate DATE,p_enddate DATE,p_equipments VARCHAR2,PLInventoryTable in OUT iCursor_PLInvTable1)
IS
vlength NUMBER;
strqueryforexec   VARCHAR2 (32767);
strqueryforexec_0 VARCHAR2 (32767);
strqueryforexec_Union  VARCHAR2 (32767);
strqueryforexec_Union1  VARCHAR2 (32767);
strqueryforexec_Union2   VARCHAR2 (32767);
strqueryforexec_Union3   VARCHAR2 (32767);
strqueryforexec_Union4   VARCHAR2 (32767);
strqueryforexec_Union5  VARCHAR2 (32767);
strqueryforexec_Union6   VARCHAR2 (32767);
strqueryforexec_Union7  VARCHAR2 (32767);
strfinalquery    VARCHAR2 (32767);   
vsplit VARCHAR2(32767);
vsplit1 VARCHAR2(32767);
vsplit2 VARCHAR2(32767);
vsplit3 VARCHAR2(32767);
vsplit4 VARCHAR2(32767);
vsplit5 VARCHAR2(32767);
vsplit6 VARCHAR2(32767);
vsplit7 VARCHAR2(32767);
vsplit8 VARCHAR2(32767); 
v_startdate VARCHAR2 (512);
v_enddate VARCHAR2 (512);

BEGIN
vlength:=LENGTHB(p_equipments);
v_startdate :='TO_DATE('''
            || TO_CHAR (p_startdate, 'MM/DD/YYYY HH:MI AM')
            || ''', ''MM/DD/YYYY HH:MI AM'')';
v_enddate:='TO_DATE('''
            || TO_CHAR (p_enddate, 'MM/DD/YYYY HH:MI AM')
            || ''', ''MM/DD/YYYY HH:MI AM'')';
vsplit:= SUBSTR(SUBSTRB(p_equipments,1,4000),1,INSTR(SUBSTRB(p_equipments,1,4000),',',-1)-1);
strqueryforexec_0 :='SELECT INV_ID,SRC_ID,EQ_NAME,INV_DT_TM, VOL_UNIT,LEVEL_UNIT,TEMP_UNIT,DENSITY_UNIT,MASS_UNIT,PRESSURE_UNIT,'
         ||'DATA_QUALITY,CHECKED,RELEASED,INFORMATION_MESSAGE,TOLERANCE FROM PL_INV '  
         ||' WHERE INV_DT_TM >='||v_startdate||' AND INV_DT_TM <= '||v_enddate||''
         ||' AND EQ_NAME IN (SELECT *  FROM THE '
         ||'( SELECT cast( PLWEB.str2tblText( '''||vsplit||''') AS PL_TABLE_VARCHAR_TYPE ) FROM  dual ))';
strqueryforexec :='SELECT INV_ID,SRC_ID,EQ_NAME,INV_DT_TM, VOL_UNIT,LEVEL_UNIT,TEMP_UNIT,DENSITY_UNIT,MASS_UNIT,PRESSURE_UNIT,'
         ||'DATA_QUALITY,CHECKED,RELEASED,INFORMATION_MESSAGE,TOLERANCE FROM PL_INV '  
         ||' WHERE INV_DT_TM >='||v_startdate||' AND INV_DT_TM <= '||v_enddate||''
         ||' AND EQ_NAME IN (SELECT *  FROM THE ';
    
if vlength <4000 then 
open PLInventoryTable for
     SELECT INV_ID,SRC_ID,EQ_NAME,INV_DT_TM,
    VOL_UNIT,LEVEL_UNIT,TEMP_UNIT,DENSITY_UNIT,MASS_UNIT,PRESSURE_UNIT,
    DATA_QUALITY,CHECKED,RELEASED,INFORMATION_MESSAGE,TOLERANCE FROM PL_INV
    where INV_DT_TM >=p_startdate AND
        INV_DT_TM <= p_enddate AND
        EQ_NAME IN ( select *  from THE
                ( select cast( PLWEB.str2tblText( p_equipments) as PL_TABLE_VARCHAR_TYPE )
                from
                dual
              )
          )
     order by INV_DT_TM  desc;
end if; 

if vlength > 4000 then 
vsplit:= SUBSTR(SUBSTRB(p_equipments,1,4000),1,INSTR(SUBSTRB(p_equipments,1,4000),',',-1)-1);
vsplit1:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,4000),',',-1)+1);
strqueryforexec_Union1 := ' UNION ALL '||  strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit1||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
insert into t2 values( vsplit1);
commit;
end if;
if vlength >8000 then
vsplit2:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,8000),',',-1)+1);
insert into t2 values(vsplit2);
commit;
strqueryforexec_Union2:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit2||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if; 
if vlength >12000 then
vsplit3:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,12000),',',-1)+1);
strqueryforexec_Union3:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit3||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if; 
if vlength >16000 then
vsplit4:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,16000),',',-1)+1);
strqueryforexec_Union4:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit4||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if; 
if vlength >20000 then
vsplit5:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,20000),',',-1)+1);
strqueryforexec_Union5:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit5||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if;           
if vlength >24000 then
vsplit6:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,24000),',',-1)+1);
strqueryforexec_Union6:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit6||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if;
if vlength >28000 then
vsplit7:=SUBSTR(p_equipments,INSTR(SUBSTRB(p_equipments,1,28000),',',-1)+1);
strqueryforexec_Union7:=' UNION ALL '|| strqueryforexec || ' ( SELECT cast( PLWEB.str2tblText( '''||vsplit7||''') 
AS PL_TABLE_VARCHAR_TYPE ) FROM  dual )) ';
end if;
if vlength >4000 then    
strfinalquery:=strqueryforexec_0||strqueryforexec_Union1||strqueryforexec_Union2||strqueryforexec_Union3||
               strqueryforexec_Union4||strqueryforexec_Union5||strqueryforexec_Union6||strqueryforexec_Union7||
               ' order by INV_DT_TM  DESC' ;
insert into t1 values(strfinalquery);
commit;

open  PLInventoryTable FOR strfinalquery  ;
end if;
end GETPLINVTABLEDATA;  


[split long lines]

[Updated on: Thu, 03 December 2009 03:41] by Moderator

Report message to a moderator

Re: sql restictions of 4000 charactes in in clause [message #433594 is a reply to message #433557] Thu, 03 December 2009 03:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
but my vsplit1 function is not working properly when i get more than 8000 characters ,


What does 'not working' mean in this case?

I can see that if the string is longer than 8000 then vsplit1 will always be > 4000 chrs, which will make the query fall over.
Previous Topic: How to retrieve values from PL/SQL Table Type in Procedure
Next Topic: Error: ORA-30372: fine grain access policy conflicts with material
Goto Forum:
  


Current Time: Sat Dec 10 20:35:47 CST 2016

Total time taken to generate the page: 0.09455 seconds