Solved

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

Posted on 2002-05-29
17
5,034 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
  • 7
  • 4
  • 2
  • +3
17 Comments
 
LVL 34

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
 

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

Accepted Solution

by:
Mark Geerlings earned 275 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now