Home » SQL & PL/SQL » SQL & PL/SQL » Stuck in buffer overflow (Oracle 10g)
Stuck in buffer overflow [message #391091] |
Wed, 11 March 2009 00:12  |
J1357
Messages: 33 Registered: November 2008
|
Member |
|
|
The scripts for the table creation are given below along with my code which is giving buffer overflow error and also it is not printing the sequence right.Need your inputs to rectify and modify the same
Output required is irrespective of no of '&' character in string
1.program should be able to identify the sub-strings betn no of '&'
2.total no of occurrance in a given string '&'
3.able to concatenate the sub-strings at a time for a particular row only.
I'm doing this for col1=5 in the code.
Scripts:
DROP TABLE ABC CASCADE CONSTRAINTS;
CREATE TABLE GLA_PKG.ABC
(
COL2 VARCHAR2(100 BYTE),
COL1 NUMBER
)
insert into abc values (1,'filename=2GB_Final_Test.zip'||chr(38)||'filename2=280648119'||chr(38)||'filename3=null');
insert into abc values (2,'filename=c10k-k4u2p10-mz.120-30.S3.bin'||chr(38)||'filename2=268438016'||chr(38)||'filename3=null');
insert into abc values (3,'filename=oracle.exe'||chr(38)||'filename2=12345'||chr(38)||'filename3=23456');
insert into abc values (4,'filename=c12kprp-boot-mz.120-33.S1.bin'||chr(38)||'filename2=278725201'||chr(38)||'filename3=null');
insert into abc values (5,'filename=oracle.exe'||chr(38)||'filename2=123455'||chr(38)||'filename3=23456'||chr(38)||'filename4=test');
SQL> select col1,col2 from abc;
COL1 COL2
---------- ----------------------------------------------------------------------------------------------------
1 filename=2GB_Final_Test.zip&filename2=280648119&filename3=null
2 filename=c10k-k4u2p10-mz.120-30.S3.bin&filename2=268438016&filename3=null
3 filename=oracle.exe&filename2=12345&filename3=23456
4 filename=c12kprp-boot-mz.120-33.S1.bin&filename2=278725201&filename3=null
5 filename=oracle.exe&filename2=123455&filename3=23456&filename4=test
My Code :
declare
lv_count number;
i number:=1;
lv_great number;
lv_first_set varchar2(1000);
lv_sec_set varchar2(1000);
lv_str varchar2(1000);
begin
loop
select col2,nvl(instr(ltrim(rtrim(col2)),'&',1,i),0) into lv_str,lv_count from abc where col1=5;
exit when nvl(lv_count,0)=0;
i:=i+1;
end loop;
select nvl(i-1,0) into lv_great from abc where col1=5;
dbms_output.put_line ('Highest occurrance : '||to_number(i-1));
dbms_output.put_line ('No of strings in the entity : '||to_number(i));
dbms_output.put_line ('String is : '||lv_str);
i:=1;
lv_count:=0;
loop
select instr(col2,'&',i,i) into lv_count from abc where col1=5;
if lv_count=0 then
exit;
end if;
select substr(col2,1,(instr(col2,'&',i,1)-instr(col2,'&',i-1)-1)) into lv_first_set from abc where col1=5;
dbms_output.put_line (lv_first_set);
lv_first_set:=lv_first_set||chr(38);
loop
select substr(col2,instr(col2,'&',i,1)+1,(instr(col2,'&',1,2)-instr(col2,'&',i,1)-1)) into lv_sec_set from abc where col1=5;
dbms_output.put_line (lv_sec_set);
i:=i+1;
exit when lv_count=0;
end loop;
lv_sec_set:=lv_first_set||chr(38)||lv_sec_set||chr(38);
end loop;
--dbms_output.put_line(lv_set);
end;
/
|
|
|
Re: Stuck in buffer overflow [message #391096 is a reply to message #391091] |
Wed, 11 March 2009 00:23   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Use SQL*Plus and copy and paste your session getting error.
Also post the expected result for your test case.
Regards
Michel
[Updated on: Wed, 11 March 2009 00:25] Report message to a moderator
|
|
|
Re: Stuck in buffer overflow [message #391278 is a reply to message #391091] |
Wed, 11 March 2009 11:07   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
> which is giving buffer overflow error
Error? what error? I don't see any error.
Please read & follow Posting Guidelines!
DECLARE
lv_count NUMBER;
i NUMBER := 1;
lv_great NUMBER;
lv_first_set VARCHAR2(1000);
lv_sec_set VARCHAR2(1000);
lv_str VARCHAR2(1000);
BEGIN
LOOP
SELECT col2,
Nvl(Instr(Ltrim(Rtrim(col2)),'&',1,i),0)
INTO lv_str,lv_count
FROM abc
WHERE col1 = 5;
EXIT WHEN Nvl(lv_count,0) = 0;
i := i + 1;
END LOOP;
SELECT Nvl(i - 1,0)
INTO lv_great
FROM abc
WHERE col1 = 5;
dbms_output.Put_line('Highest occurrance : '
||To_number(i - 1));
dbms_output.Put_line('No of strings in the entity : '
||To_number(i));
dbms_output.Put_line('String is : '
||lv_str);
i := 1;
lv_count := 0;
LOOP
SELECT Instr(col2,'&',i,i)
INTO lv_count
FROM abc
WHERE col1 = 5;
IF lv_count = 0 THEN
EXIT;
END IF;
SELECT Substr(col2,1,(Instr(col2,'&',i,1) - Instr(col2,'&',i - 1) - 1))
INTO lv_first_set
FROM abc
WHERE col1 = 5;
dbms_output.Put_line(lv_first_set);
lv_first_set := lv_first_set
||Chr(38);
LOOP
SELECT Substr(col2,Instr(col2,'&',i,1) + 1,(Instr(col2,'&',1,2) - Instr(col2,'&',i,1) - 1))
INTO lv_sec_set
FROM abc
WHERE col1 = 5;
dbms_output.Put_line(lv_sec_set);
i := i + 1;
EXIT WHEN lv_count = 0;
END LOOP;
lv_sec_set := lv_first_set
||Chr(38)
||lv_sec_set
||Chr(38);
END LOOP;
--dbms_output.put_line(lv_set);
END;
|
|
|
Re: Stuck in buffer overflow [message #391413 is a reply to message #391278] |
Thu, 12 March 2009 03:52  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In your innermost loop: loop
select substr(col2,instr(col2,'&',i,1)+1,(instr(col2,'&',1,2)-instr(col2,'&',i,1)-1)) into lv_sec_set from abc where col1=5;
dbms_output.put_line (lv_sec_set);
i:=i+1;
exit when lv_count=0;
end loop;
You exit on a condition based on lv_count, but the value of lv_count doesn't change - so, if you get into this loop and don't leave imediately, you'll be there forever. This means that sooner or later, you'll send more data to dbms_output.put_line than it can cope with.
FYI, it wold have helped if you'd posted the error message:ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 33
|
|
|
Goto Forum:
Current Time: Mon Feb 17 01:18:49 CST 2025
|