ORACLE 8.1.7.4 DBMS_OUTPUT bug? 1

[weberm]

After applying a patch upgrade to ORACLE 8.1.7.4, I discovered DBMS_OUTPUT.ENABLE does not work. I used to be able to execute PL/SQL packages with a UNIX shell script and get DBMS_OUTPUT put_line statements, but that is no longer the case now. Is there a fix or workaround for this problem?

 

[Beilstwh]

after the update, did you rerun the various setup scripts like the patch instructions said to do, such as catalog, catproc.... etc.

Bill
Oracle DBA/Developer
New York State, USA

 

[weberm]

I did not apply the patches. Sorry I did not make that clear. According to my DBA, there's a bug which prevents DBMS_OUTPUT.ENABLE from working so I guess I need to find a way to find a way to pass this to sqlplus as a stop-gap fix:

SET SERVEROUTPUT ON
EXEC hello_world;

Any suggestions?

 

[SantaMufasa]

Weberm,

I do not have an 8.1.7.4 database to run this against. I am not aware of the "bug" to which your DBA refers. I just successfully ran on an 8.1.7.3 instance the following code:

SQL> set serveroutput on
SQL> begin dbms_output.put_line('This is a test.'); end;
  2  /
This is a test.

Would you please run the above commands on your target instance and post the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

I have checked metalink and if there is a bug with 8.1.7 dbms_output, then oracle is unaware of it, which I find unlikely. What EXACTLY is the error that is being generated. How is it NOT working?

Bill
Oracle DBA/Developer
New York State, USA

 

[weberm]

That worked fine. However, this is what happened when I tried to execute a stored procedure containing a DBMS_OUTPUT statement:

SQL> set serveroutput on
SQL> begin execute hello;end;
  2  /
begin execute hello;end;
              *
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00103: Encountered the symbol "HELLO" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "HELLO" to continue.


SQL>

Stored procedure hello contains one DBMS_OUTPUT line which runs fine in SQL*PLUS.

 

[Beilstwh]

This error has NOTHING to do with dbms_output, it is saying that it doesn't know anything about a procedure called HELLO. issue the following select in sqlplus

select object_type,owner
from all_objects
where object_name = 'HELLO';


Bill
Oracle DBA/Developer
New York State, USA

 

[weberm]

I have checked metalink and if there is a bug with 8.1.7 dbms_output, then oracle is unaware of it, which I find unlikely. What EXACTLY is the error that is being generated. How is it NOT working?

I have a UNIX shell script which executes PL/SQL stored procedures via the sqlplus statement. The stored procedure contains some DBMS_OUTPUT statements which are displayed when the script is run but now nothing is displayed. Here is a before and after example using a stored procedure with a single DBMS_OUTPUT statement:

{.rcpi}/u003/users/rcpi==> sqlpl -s scott/tiger "@hello"
Hello

PL/SQL procedure successfully completed.

{.rcpi}/u003/users/rcpi==>

{.rcpi}/u003/users/rcpi==> sqlpl -s scott/tiger "@hello"

PL/SQL procedure successfully completed.

{.rcpi}/u003/users/rcpi==>

I hope this is specific enough...

 

[weberm]

Here's what SQL*PLUS had to say:

SQL> ed
Wrote file afiedt.buf

  1  select object_name, object_type,owner
  2  from all_objects
  3* where object_name = 'HELLO'
SQL> /

OBJECT_NAME                    OBJECT_TYPE        OWNER
------------------------------ ------------------ ----------------------------
HELLO                          PROCEDURE          RCPI

SQL>

Weird, huh?

 

[SantaMufasa]

Not weird...just wrong syntax in your earlier invocation. Correct syntax is simply:

SQL> set serveroutput on
SQL> execute hello

Try it that way and post results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[weberm]

Try it that way and post results.

SQL> set serveroutput on
SQL> exec hello
Hello

PL/SQL procedure successfully completed.

SQL>

Hey, that worked! Thank you! I'm going to see if I can make it work from the UNIX prompt next...

 

[Beilstwh]

The -s flag suppresses all messages, including error messages. Remove it and rerun the script and see what happens.

Bill
Oracle DBA/Developer
New York State, USA

 

[weberm]

Thanks for all your help, guys. I think I got it (and a work-around until our DBA fixes it).

{.rcpi}/u003/users/rcpi==> sqlplus -s rcpi/owner@test @mrw
Hello

PL/SQL procedure successfully completed.

{.rcpi}/u003/users/rcpi==>

 

[SantaMufasa]

According to my DBA, there's a bug which prevents DBMS_OUTPUT.ENABLE from working

Yet again, one more proof of "Mufasa's First Law of Experts"

One test is worth 1000 expert opinions.

Also, a clarification:

The -s flag suppresses all messages, including error messages.

Here are the contents of a SQL*Plus script containing my earlier test code:

set serveroutput on
begin dbms_output.put_line('This is a test.'); end;
/
exit

Here are the results of successfully running that script from the command line without using the "-s" parameter:

C:\DHunt\SQLDBA>sqlplus test/test @tt_416

SQL*Plus: Release 9.2.0.6.0 - Production on Thu May 3 13:42:23 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

This is a test.

PL/SQL procedure successfully completed.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Here are the results using the "-s" parameter:

C:\DHunt\SQLDBA>sqlplus -s test/test @tt_416
This is a test.

PL/SQL procedure successfully completed.

Here are the results when I inject a syntax error (removing a terminating quote) into the script, but still using the "-s" parameter:

C:\DHunt\SQLDBA>sqlplus -s test/test @tt_416
ERROR:
ORA-01756: quoted string not properly terminated

C:\DHunt\SQLDBA>

So, from these tests, we see that "-s" suppresses header and trailer banners that Oracle generates by default, but the normal output and error messages still appear.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Thanks Dave, hoisted on my own sword again.... Sigh have a star

Bill
Oracle DBA/Developer
New York State, USA

 

[weberm]

I still don't understand why now I have to use SET SERVEROUTPUT ON, but at least I know a temporary fix to this problem.

 

[SantaMufasa]

Weberm,

The "SET SERVEROUTPUT ON" command is a SQL*Plus command that opens up a communication channel between the Oracle Server and your session's screen.

This is not a "new thing"; it is not a "workaround"...it has always been there if you have been using SQL*Plus and successfully been getting DBMS_OUTPUT displayed on your screen...you just didn't see it; somehow, someone coded that command (or its equivalant) without your being aware of it.

Let us know if you have additional questions on this issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]