ora procedure fails to execute

[rogerF]

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 ?

[m1l]

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 <procedure schema>.cre_backup_dbtest1_script;

Hope this is of help.

Mike

[rogerF]

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