We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQLPLUS ERROR

Medium Priority
541 Views
Last Modified: 2013-12-19
I am having some Oracle database problem, and I was given this query to run on the pc in question, " 

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
ERROR
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.ENABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Comment
Watch Question

Sr Software Engineer
CERTIFIED EXPERT
Commented:
If possible, post the bad code; we don't have enough information by far.  Has this code worked previously, and if so, what changed?
Syntax example:  Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000 dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000); exec dbms_output.enable(1000000);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
This is the part of the query that I was attempting to run and the ouput.

SQL> set pagesize 20000
SQL> set linesize 180
SQL> set pause off
SQL> set serveroutput on
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.ENABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


This is the full query that I was asked to run, but incountered the error above and wanted to resolve that before I proceeded with the rest, if notice any other error please advise me on how it should be excuted in SQL.  Thank you

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Spool recover.lst
archive log list
select file#,substr(name,1,50), substr(recover,1,1) R, substr(fuzzy,1,1) F,
to_char(checkpoint_time,'dd/mm/yyyy:hh24:mi:ss') ckpt_time,

checkpoint_change#, resetlogs_change#,
to_char(resetlogs_time,'dd/mm/yyyy HH24:MI:SS') tm from v$datafile_header;
select file#,substr(name, 1, 50), status,enabled,to_char(creation_time,'dd/mm/yyyy:hh24:mi:ss') from
v$datafile;
select incarnation#,resetlogs_change#,resetlogs_time,prior_resetlogs_change#,
prior_resetlogs_time,status from v$database_incarnation;
select * from v$backup;
select name, open_mode, controlfile_type,controlfile_change#,
checkpoint_change#, ARCHIVE_CHANGE#,resetlogs_change#,
to_char(resetlogs_time,'dd/mm/yyyy:hh24:mi:ss'),
prior_resetlogs_change#, to_char(prior_resetlogs_time,'dd/mm/yyyy:hh24:mi:ss') from v$database;
select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from
v$log;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$log_history;
select * from v$recover_file;
select * from v$recovery_log;
select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence, FHTHR
from X$KCVFH;
spool off
Is this a recent install of Oracle?  It may be that some system scripts haven't been run.  Have a look at the following experts exchange question which may help you:

http://www.experts-exchange.com/Database/Oracle/Q_20305827.html
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
I would also suggest to remove this line:


set serveroutput on

as you don't need that...
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
DBMS_OUTPUT is created as part of the catproc script.  Log in as the SYS user and run that script.

And, as angelll suggested you do not really need it in your particular script as you are not using it.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
It may simply be that you have not been granted execute privileges on the DBMS_OUTPUT package.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
When the DBMS_OUTPUT package is create, execute privilege is granted to PUBLIC.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.