Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: identifier 'procedure' must be declared
PLS-00201: identifier 'procedure' must be declared [message #280818] Wed, 14 November 2007 20:59 Go to next message
pigggy
Messages: 4
Registered: November 2007
Junior Member
Hi, all the experts out there, i am new to db admin.

i have two scripts. one of it is as below. i am trying to execute it but failed for some reason i still cannot figure out yet.
anyone has any idea/suggested, please feel free. thanks

bash-3.1$ cat ERARCH.ER_ARCH.sql
CREATE OR REPLACE PACKAGE "ERARCH"."ER_ARCH"
AS
PROCEDURE er_arch_and_purge (p_days_offset IN INTEGER);
END Er_Arch;
Edit/Delete Message

SQL> exec ERARCH.ER_ARCH.er_arch_and_purge (1) ;
BEGIN ERARCH.ER_ARCH.er_arch_and_purge (1) ; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'ERARCH.ER_ARCH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


it seems like it cannot find the sql. is there a specific location i need to put both the sql.script at?

SQL> select owner, object_type from all_objects where object_name='ER_ARCH'
2 ;

no rows selected

SQL> desc ERARCH.ER_ARCH
ERROR:
ORA-04043: object ERARCH.ER_ARCH does not exist
Re: PLS-00201: identifier 'procedure' must be declared [message #280821 is a reply to message #280818] Wed, 14 November 2007 21:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Have you ever done
SQL> @ERARCH.ER_ARCH.sql
Re: PLS-00201: identifier 'procedure' must be declared [message #280823 is a reply to message #280818] Wed, 14 November 2007 21:38 Go to previous messageGo to next message
pigggy
Messages: 4
Registered: November 2007
Junior Member
yeah..but it gave me this

SQL> @ERARCH.ER_ARCH.sql
5 ;
6
7
Re: PLS-00201: identifier 'procedure' must be declared [message #280824 is a reply to message #280818] Wed, 14 November 2007 21:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i am new to db admin.
I certainly hope not.
after
SQL> @ERARCH.ER_ARCH.sql
5 ;
6 
7 

hit the Enter Key
type in a forward slash character "/"
hit the Enter Key again
as in
SQL> set term on echo on
SQL> CREATE OR REPLACE PACKAGE "ERARCH"."ER_ARCH"
  2  AS
  3  PROCEDURE er_arch_and_purge (p_days_offset IN INTEGER);
  4  END Er_Arch;
  5  /
CREATE OR REPLACE PACKAGE "ERARCH"."ER_ARCH"
*
ERROR at line 1:
ORA-01435: user does not exist


Re: PLS-00201: identifier 'procedure' must be declared [message #280825 is a reply to message #280818] Wed, 14 November 2007 22:07 Go to previous messageGo to next message
pigggy
Messages: 4
Registered: November 2007
Junior Member
it gave me the user does not exist error as yours. but what does that means? is that means the user that created the sql script is not exist? is there a way to find that out?

Re: PLS-00201: identifier 'procedure' must be declared [message #280826 is a reply to message #280825] Wed, 14 November 2007 22:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
It means there is no user/schema named "ERARCH".
Re: PLS-00201: identifier 'procedure' must be declared [message #280827 is a reply to message #280818] Wed, 14 November 2007 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it gave me the user does not exist error as yours. but what does that means?
What part of "user does not exist" do you NOT understand?

>is that means the user that created the sql script is not exist?
No.
By the way, which USER create the PL/SQL procedure



>is there a way to find that out?
yes

From where did you copy the PL/SQL code that you are trying to run?

visit http://tahiti.oracle.com
Read The Fine Concepts Manual to learn answers to your questions

Re: PLS-00201: identifier 'procedure' must be declared [message #280828 is a reply to message #280818] Wed, 14 November 2007 22:53 Go to previous messageGo to next message
pigggy
Messages: 4
Registered: November 2007
Junior Member
i cannot seem to relate the "user" to the script. ie which user? cause i got the script from the dba for another production system. now i need to use the same script for a new migrated system which supposely to have the same tables/db as the production
Re: PLS-00201: identifier 'procedure' must be declared [message #280830 is a reply to message #280818] Wed, 14 November 2007 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
visit http://tahiti.oracle.com
Read The Fine Concepts Manual to learn answers to your questions

>cause i got the script from the dba for another production system
Then the other DBA should be able to answer ALL your questions!

[Updated on: Wed, 14 November 2007 23:20] by Moderator

Report message to a moderator

Re: PLS-00201: identifier 'procedure' must be declared [message #280874 is a reply to message #280830] Thu, 15 November 2007 01:25 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you should definitely read documentation as, obviously, you have no idea what are you doing.

Solution isn't that far, however - remove user name from package name and you'll get rid of that error. After that, create package body because - if you don't do that, there'll be nothing to execute.

See this example and, seriously, READ the documentation.
SQL> CREATE OR REPLACE PACKAGE ER_ARCH
  2  AS
  3    PROCEDURE er_arch_and_purge (p_days_offset IN INTEGER);
  4  END Er_Arch;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY er_arch
  2  AS
  3    PROCEDURE er_arch_and_purge (p_days_offset IN INTEGER)
  4    IS
  5    BEGIN
  6      NULL;
  7    END;
  8  END;
  9  /

Package body created.

SQL> EXEC er_arch.er_arch_and_purge(1);

PL/SQL procedure successfully completed.

SQL>
Previous Topic: DML statements notification
Next Topic: cross linking in same table
Goto Forum:
  


Current Time: Tue Feb 18 10:30:13 CST 2025