Home » SQL & PL/SQL » SQL & PL/SQL » buffer overflow
buffer overflow [message #210799] Fri, 22 December 2006 07:46 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
After running this code:

DECLARE
v_Name VARCHAR2(60);
ItExist NUMBER;
CURSOR Get_ObjName IS SELECT OBJECT_NAME FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME,'VW') <> 0 ;
BEGIN
SELECT COUNT(*) INTO ItExist FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME,'VW') > 0;
DBMS_OUTPUT.ENABLE(1000000);

OPEN Get_ObjName;
FETCH Get_ObjName INTO v_Name;
WHILE Get_ObjName%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('Found the Following Conflicting VIEW NAME(S)');
DBMS_OUTPUT.PUT_LINE('104: Found '''||v_Name ||''' Existing VIEW Name(s).');
END LOOP;
CLOSE Get_ObjName;
END;
/

i received this Error:
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 12

Any idea??
Re: buffer overflow [message #210808 is a reply to message #210799] Fri, 22 December 2006 08:09 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everyone,
actueally this line code has nothing to do with the rest:

SELECT COUNT(*) INTO ItExist FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME,'VW') > 0;

So Is there any idea about the buffer overflow error...?

Thanks in Advance,
bahaa
Re: buffer overflow [message #210816 is a reply to message #210808] Fri, 22 December 2006 08:29 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

It's an error from DBMS_OUTPUT.PUT_LINE meaning you've reached the maximum buffer size you can output. You'll need to cut down your output or use another method.
Re: buffer overflow [message #210819 is a reply to message #210799] Fri, 22 December 2006 08:37 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your replay,

Which other method??

Thanks in Advance,
Bahaa
Re: buffer overflow [message #210822 is a reply to message #210799] Fri, 22 December 2006 08:43 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
You could use UTL_FILE to generate an output file
Re: buffer overflow [message #210836 is a reply to message #210822] Fri, 22 December 2006 10:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do this in pl/sql?
Just execute your query in plain sql!
Re: buffer overflow [message #210838 is a reply to message #210836] Fri, 22 December 2006 10:56 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

What Frank has said is all right you should use only sql to achive but if you want to use sql then you may try the following, it is just the modification of your's scriot :

Quote:
DECLARE
v_Name VARCHAR2(60);
ItExist NUMBER;
CURSOR Get_ObjName IS SELECT OBJECT_NAME FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME,'VW') <> 0 ;
BEGIN
SELECT COUNT(*) INTO ItExist FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME,'VW') > 0;
OPEN Get_ObjName;
FETCH Get_ObjName INTO v_Name;
for i in 1..itExist
LOOP
DBMS_OUTPUT.PUT_LINE('Found the Following Conflicting VIEW NAME(S)');
DBMS_OUTPUT.PUT_LINE('104: Found '''||v_Name ||''' Existing VIEW Name(s).');
END LOOP;
CLOSE Get_ObjName;
END;


also give set serveroutput on .

Bye
Ashu
Re: buffer overflow [message #210897 is a reply to message #210838] Sat, 23 December 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That will not help a single bit. The limit (prior to 10g) is 1000000 characters.
Re: buffer overflow [message #210902 is a reply to message #210897] Sat, 23 December 2006 01:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I think 1000000 characters was not the problem. If you see part of the original code
OPEN Get_ObjName;
FETCH Get_ObjName INTO v_Name;
WHILE Get_ObjName%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('Found the Following Conflicting VIEW NAME(S)');
DBMS_OUTPUT.PUT_LINE('104: Found '''||v_Name ||''' Existing VIEW Name(s).');
END LOOP;
CLOSE Get_ObjName;

in the WHILE loop only DBMS_OUTPUT is placed so if any row is fetched it loops infinitely.
Ashu's solution does not seem to contain this 'behaviour'.
Re: buffer overflow [message #210941 is a reply to message #210902] Sat, 23 December 2006 23:08 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
But my pl/sql is working and giving the result

Found the Following Conflicting VIEW NAME(S)
104: Found 'VW_DEPT10' Existing VIEW Name(s).

PL/SQL procedure successfully completed.


Problem with Bahy91 is giving

Quote:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes


Bye
Ashu
Re: buffer overflow [message #210952 is a reply to message #210941] Sun, 24 December 2006 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
flyboy is right. Yet another endless loop.
This is one of the reasons why you should use cursor-for loops!
Don't try to do by hand what Oracle automated for you; it is error prone.

But my original comment still stands: Use plain SQL for this, not pl/sql!

[Updated on: Sun, 24 December 2006 02:12]

Report message to a moderator

Re: buffer overflow [message #212785 is a reply to message #210952] Mon, 08 January 2007 05:58 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you all,
'But my original comment still stands: Use plain SQL for this, not pl/sql! '
you are absolutely right..it is so easy to solve it by using SQL rather than PLSQL..

Thanks In Advance,
bahaa
Previous Topic: PLease help with query Need to select distict from one table insert into another
Next Topic: my sql plus Abnormallly shutdown
Goto Forum:
  


Current Time: Wed Dec 07 12:33:13 CST 2016

Total time taken to generate the page: 0.05514 seconds