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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
BlackSwan
Messages: 25040
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 Go to previous message
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

Previous Topic: Data as column heading
Next Topic: SQL - spaces in COLs
Goto Forum:
  


Current Time: Tue Dec 06 00:25:29 CST 2016

Total time taken to generate the page: 0.08447 seconds