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  |
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 #433557 is a reply to message #433554] |
Wed, 02 December 2009 23:41   |
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  |
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.
|
|
|
Goto Forum:
Current Time: Tue Feb 18 00:46:31 CST 2025
|