Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared

Posted on 2002-05-29
17
Medium Priority
?
5,466 Views
Last Modified: 2012-05-04
When I start ./sqlplus I get the following two errors.
Anyone ideas?

Jeffeny



./sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 29 16:24:07 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter user-name: sys
Enter password:
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL>
0
Comment
Question by:harryv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +3
17 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7042246
While it is possible to log in to SQL*Plus as "SYS", this is not the account you should use very often.  "SYS" is the most privileged and most powerful account in an Oracle database (something like "root" in Unix).  Usually when the database is set up, another account is created and given DBA privileges along with additional accounts for "normal" users.  Then that DBA account is the one used for most DBA work.

How was your database created?  Were the normal post-installation setup scripts run (catalog, catproc, catexp, etc.)?  It sounds like one or more of them may have been missed.  Also, there is usually a script that should be run as SYSTEM (not SYS) to set up SQL*Plus options and security.
0
 
LVL 11

Expert Comment

by:pennnn
ID: 7042272
I think that the problem is with the SQL*Plus Global Login startup file. Try to find the glogin.sql file which is usually located in the sqlplus directory under your Oracle Home ($ORACLE_HOME/sqlplus/admin/glogin.sql).
It should contain the commands that are executed when a user connects to SQL*Plus.
You can see what the commands in that file are and that will help you find out why you get the error messages.
Hope that helps!
0
 

Author Comment

by:harryv
ID: 7043026
Hi,

I think that I have to give a little more information.

Markgeer: the errors are on every user (so also normal users), not only SYS. The installtion of Oracle is on Slackware 8.0 and the database is created whith ./svrmgrl
(create database .....). In the beginning I runned some commands as SYSTEM to get rid of some other errors. Maybe it is the same problem here, although I don't know what command  to run.

Pennnn: I have found the file, but I don't see the connection with the errors.

Jeffeny

--
--  Copyright (c) Oracle Corporation 1988, 1999.  All Rights Reserved.
--
--  SQL*Plus Global Login startup file.
--
--  Add any sqlplus commands here that are to be executed when a user
--  starts SQL*Plus on your system

-- Used by Trusted Oracle
column ROWLABEL format A15

-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR    format A65  WORD_WRAPPED

-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24

-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE

-- For backward compatibility
set pagesize 14

-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:harryv
ID: 7043029
Hi,

I think that I have to give a little more information.

Markgeer: the errors are on every user (so also normal users), not only SYS. The installtion of Oracle is on Slackware 8.0 and the database is created whith ./svrmgrl
(create database .....). In the beginning I runned some commands as SYSTEM to get rid of some other errors. Maybe it is the same problem here, although I don't know what command  to run.

Pennnn: I have found the file, but I don't see the connection with the errors.

Jeffeny

--
--  Copyright (c) Oracle Corporation 1988, 1999.  All Rights Reserved.
--
--  SQL*Plus Global Login startup file.
--
--  Add any sqlplus commands here that are to be executed when a user
--  starts SQL*Plus on your system

-- Used by Trusted Oracle
column ROWLABEL format A15

-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR    format A65  WORD_WRAPPED

-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24

-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE

-- For backward compatibility
set pagesize 14

-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7043088
The file "glogin.sql" is read by SQL*Plus when it starts.  You may modify it to add commands you want to have executed automatically.  The file you posted here looks like the default one from Oracle, and I agree, this is probabl not the source of the errors you see (but it could have been if someone had modified it).

Does the package "dbms_application_info" exist in your database?  Is there a public synonym for it?  Is there a public execute grant for it?  That is one of the standard PL\SQL packages that is usually set up in one of the post-installation steps that run a number of scripts from your Oracle_home\rdbms\admin directory.

The package "dbms_output" is another one of these that is usually created during or just after the original database creation.  Does it exist?  Does it have a public synonym and public execute grant?

If you don't have these packages or their synonyms or grants, then one or more of the usual post-installation scripts was not run.
0
 
LVL 1

Expert Comment

by:rmyufa
ID: 7043101
Hi!

Have you activated ON LOGIN trigger?
May be this trigger contains calls to DBMS_OUTPUT.DISABLE?
For clearing buffers?

Make the package DBMS_OUTPUT accessible to PUBLIC.
Or disable ON LOGIN trigger?

Regards,
pm
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7043794
try to run the build script for the sys.dbms built-ins; it may be that one of them is invalid and needs to be rebuilt or re-compiled.

good luck!
daniel
0
 

Author Comment

by:harryv
ID: 7043944
Hi,

Markgeer: The user SYS has two packages: DBMS_STANDARD and STANDARD (as I can see in DBA studio)

Rmyufa: I am learning Oracle so forgive me if I don't understand it immediate.
I get some ORA-00942 errors in Oracle DBA so can not see the triggers there.
I want to know how I can see it on the console.


Drs66: how to build the script? or recompile? As I said, I am a kind of newbie as far as it has to do with Oracle DBA.

Jeffeny

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7044466
When this database was set up initially did you let the Oracle installer build it for you?  Or did you create it with a wizard, or maybe manually via Server Manager?  It certainly looks like some of the typical post-installation steps were not done.  Do you have the operating-system specific documentation for Oracle8.1.7 for your O/S?  If so, check that and run the post-installation steps.
0
 

Author Comment

by:harryv
ID: 7044555
Hi,

I created the database manualy via the Server Manager (./svrmgrl)
For some previous errors I had to run some commands at ./sqlplus or ./srvmgrl. But for those errors I have still left, I cannot find a solution on the net. I think that the solution is in running some commands but as I said before, I am learning Oracle and still learing the commands :-)

Jeffeny
0
 

Author Comment

by:harryv
ID: 7044659
Ok, I have found that DBMSOTPT.SQL is used to create the DBMS_OUTPUT

I have have started it and get some new errors.

How do I know what scripts to run? It is clear that the problem is in the creation scripts.

Jeffeny


 ./sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 30 16:55:59 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter user-name: sys
Enter password:
ERROR:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_OUTPUT" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1


Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL>
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1100 total points
ID: 7044754
If you create a database manually there are a number of scripts you need to run.  Here is the list I used with our 8.1.7 database on NT:

as SYS (or connect internal)
Oracle_home\admin\catalog.sql
Oracle_home\admin\catsnmp.sql
Oracle_home\admin\catexp7.sql
Oracle_home\admin\catproc.sql
Oracle_home\scott.sql
Oracle_home\demo.sql
Oracle_home\admin\caths.sql

as SYSTEM (not SYS)
Oracle_home\admin\catdbsyn.sql
Oracle_home\dbs\pupbld.sql

Some of these in turn call other scripts.  Try running each of these, or if you have the operating-system specific installation instructions for your version and O/S, run the scripts it lists.
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7044790
catproc.sql is the one that will probably do the trick.

good luck,
daniel
0
 
LVL 2

Expert Comment

by:mccallw
ID: 7044905
Make sure you run at a minimum catalog.sql, catproc.sql and pupbld.sql

Warren
0
 

Author Comment

by:harryv
ID: 7045509
Hi,

I think it made the database completer but I get still tge dbms_output error. After running the scripts markgeer showed, started /.sqlplus again:

ERROR:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_OUTPUT" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1


Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_APPLICATION_INFO" does not
exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1



Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL>



Then I runned:


@/oracle/u01/app/oracle/product/8.1.7/rdbms/admin/dbmsotpt.sql
create or replace package dbms_output as
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.SOURCE$ by 25 in tablespace SYSTEM



Synonym dropped.


Synonym created.


Grant succeeded.

SQL>


The package dbms_output cannot be created for some reason.

Jeffeny
0
 
LVL 2

Expert Comment

by:mccallw
ID: 7045534
It can't be created because your SYSTEM tablespace is full and cannot extend.  Alter your system tablespace to add a new datafile and have the ability to extend.
0
 

Author Comment

by:harryv
ID: 7046675
The problem of the error was solved by running these scripts (after I had increased the tablespace)

Thankx
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question