-
Oracle People:
I followed a thread last week that should have solved the following problem that I will describe. It didn't solve the problem so I'm starting a new thread.
In a nutshell, I get the following error when I(as SYS who has all procedure rights) try to execute the following procedure which creates a list of tablespaces for the nightly backup.
---------
error:
BEGIN cre_backup_dbtest1_script; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CRE_BACKUP_DBTEST1_SCRIPT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--------
The script for the procedure works fine on other machines and I'm confident that it's not a syntax problem. I've been out on the web and realize that its a "privledge" problem. Here is the procedure that fails:
create or replace procedure cre_backup_dbtest_script
as
cursor c1 is
select tablespace_name
from dba_tablespaces;
cursor c2(pin_tablespace_name varchar2) is
select file_name
from dba_data_files
where tablespace_name = pin_tablespace_name;
BEGIN
dbms_output.enable(20480);
for r1 in c1
loop
dbms_output.put_line('prompt');
dbms_output.put_line('prompt Altering tablespace '||r1.tablespace_name||' to backup');
dbms_output.put_line('alter tablespace '||r1.tablespace_name||' begin backup;');
for r2 in c2(r1.tablespace_name)
loop
dbms_output.put_line('prompt Making a backup of '||r2.file_name||'...');
dbms_output.put_line('host cp '||r2.file_name||' $BckDir');
dbms_output.put_line('host compress -f `ls -1 $BckDir/* | grep -v .Z`');
end loop;
dbms_output.put_line('alter tablespace '||r1.tablespace_name||' end backup;');
end loop;
END cre_backup_dbtest_script;
/
-------------------
Previous threads and web-messages have indicated that I don't have permission to execute. I've checked that I have execute, create procedure rights and after following last weeks thread which was quite similiar I created the following public synonym which I thought would solve this problem:
SQL> create public synonym cre_backup_dbtest1_script for cre_backup_dbtest1_script;
Synonym created.
------
So, I don't know to much about procedures ! What am I doing wrong ?
-
Roger,
All I can think is the synonym is wrong. Even though it as said synonym created, this doesn't mean the synonym is valid, you could put
create public synonym rubbish for nonexistant_script;
and it would say synonym created !
You may need to specify the schema of the owner of the script, so it would be.
create public synonym cre_backup_dbtest1_script for .cre_backup_dbtest1_script;
Hope this is of help.
Mike
-
fixed ...
Oracle People:
I finally got the procedure to work. It seems as if I ASSuMEd that the fact that the procedure states:
"create or replace procedure"
that it would do that when called after "execute". I ran the procedure as:
@/PATH/procedure_name
and made the "initial creation". It then ran w/o problem as execute. Sorry that I didn't understand as I inherited the script and never made it work from scratch.
-Roger
