dbms_output - Error

[ggarrison]

I successfully compliled the following code:

Create or replace PROCEDURE TEST
IS
BEGIN
dbms_output.enable(10000) ;
dbms_output.put_line('SELECT TOURDATE FROM
USPS.REPORTSELECT');
END;

During the execute USPS.TEST; - I received the following
message -

SQL> execute USPS.TEST;
begin USPS.TEST; end;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYSTEM.DBMS_OUTPUT"
does not exist
ORA-04067: not executed, package body "SYSTEM.DBMS_OUTPUT"
does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "USPS.TEST", line 6
ORA-06512: at line 1

Any suggestions - Thanks in advance

**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

[Thomas Kyte]

A copy of this was sent to ggarrison <ggarr...@hotmail.com>
(if that email address didn't require changing)

On Thu, 24 Jun 1999 09:36:15 -0800, you wrote:

>I successfully compliled the following code:
>
>Create or replace PROCEDURE TEST
>IS
>BEGIN
>dbms_output.enable(10000) ;
>dbms_output.put_line('SELECT TOURDATE FROM
>USPS.REPORTSELECT');
>END;
>

somebody installed dbms_output under system -- it doesn't belong there. its
installed in the SYS account when you install.

log in as system and "drop package dbms_output". You'll probably have to drop
and recreate the synonym as well. "drop public synonym dbms_output". "create
public synonym dbms_output for sys.dbms_output"

>During the execute USPS.TEST; - I received the following
>message -
>
>SQL> execute USPS.TEST;
>begin USPS.TEST; end;
>*
>ERROR at line 1:
>ORA-04068: existing state of packages has been discarded
>ORA-04067: not executed, package body "SYSTEM.DBMS_OUTPUT"
>does not exist
>ORA-04067: not executed, package body "SYSTEM.DBMS_OUTPUT"
>does not exist
>ORA-06508: PL/SQL: could not find program unit being called
>ORA-06512: at "USPS.TEST", line 6
>ORA-06512: at line 1
>
>Any suggestions - Thanks in advance
>
>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Jim]

I'd be quite surprised if it did what I suspect you want it to do even once
you've fixed that.
Assuming you have serveroutput on it will just display the string:
SELECT TOURDATE FROM USPS.REPORTSELECT
on the screen.
(basically, dbms_output.put_line just takes a string, a date or a number and
outputs it to the
screen in the sql*plus session). If only it was that simple...

Thomas Kyte <tk...@us.oracle.com> wrote in message
news:3772736...@newshost.us.oracle.com...

[Thomas Kyte]

A copy of this was sent to "Jim" <lermo...@hotmail.com>

(if that email address didn't require changing)

On Thu, 24 Jun 1999 23:35:38 +0100, you wrote:

>I'd be quite surprised if it did what I suspect you want it to do even once
>you've fixed that.
>Assuming you have serveroutput on it will just display the string:
>SELECT TOURDATE FROM USPS.REPORTSELECT
>on the screen.
>(basically, dbms_output.put_line just takes a string, a date or a number and
>outputs it to the
>screen in the sql*plus session). If only it was that simple...
>

I sort of thought they were trying to debug a larger routine and posted a small
snippet for us to figure out why it was not working.....

One problem at a time...