Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error while accessing Packages (PLS-00201/PLS-00221 & ORA-06550)

Posted on 2004-09-07
10
Medium Priority
?
3,698 Views
Last Modified: 2010-05-18
I'm using Oracle8i Enterprise Edition Release 8.1.7.2.0.


Package & Package Body for ‘COPYPACKAGE’ compiled w/o any errors!


While executing the application, the error is:
ORA-06550: line 1, column 7:
PLS-00201: 'MANPOW.COPYPACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Some Additional Info (just in case)

EXEC COPYPACKAGE gives the following error:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'COPYPACKAGE' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored




Help!
Thanx in Advance!
0
Comment
Question by:ramchi22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11998170
When you try to execute the procedure, are you using the same login that created the package?  If not, have you granted "execute" rights on the package to the user(s) who need to be able to execute it?
0
 
LVL 12

Expert Comment

by:geotiger
ID: 11998180
Is  COPYPACKAGE  a package or procedure? You can call a procedure within a package or a standalone procedure directly.

If  COPYPACKAGE  is a package, then you have to call a procedure from it.

GT
0
 
LVL 12

Expert Comment

by:geotiger
ID: 11998201

Could you display the package header (all the function and/or procedure definitions)?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ramchi22
ID: 11998224
CREATE or REPLACE package CopyPackage as
                                                                               
   -- procedure to copy the bid period                                          
   procedure copy_bid_period( station_in_var in varchar2,                      
                              bid_group_in_var in number,                      
                              bid_period_name_in_var in varchar2 );            
                                                                               
  -- procedure to copy the rotation from one ( station, bid group, bid period )
  -- to another ( station, bid group, bid period )                              
  procedure copy_rotation ( from_station_in_var in varchar2,                    
                            from_bid_group_in_var in number,                    
                            from_bid_period_name_in_var varchar2,              
                            to_station_in_var in varchar2,                      
                            to_bid_group_in_var in number,                      
                            to_bid_period_name_in_var varchar2 );              
                                                                               
  -- procedure to copy the rotation and pattern from one ( station, bid group, bid period )
  -- to another ( station, bid group, bid period )                              

  procedure copy_pattern ( from_station_in_var in varchar2,                    
                           from_bid_group_in_var in number,                    
                           from_bid_period_name_in_var varchar2,                
                           to_station_in_var in varchar2,                      
                           to_bid_group_in_var in number,                      
                           to_bid_period_name_in_var varchar2 );                
                                                                               
  -- procedure to copy the rotation, pattern and shift from one ( station, bid group, bid period )
  -- to another ( station, bid group, bid period )                              

  procedure copy_shift ( from_station_in_var in varchar2,                      
                         from_bid_group_in_var in number,                      
                         from_bid_period_name_in_var varchar2,                  
                         to_station_in_var in varchar2,                        
                         to_bid_group_in_var in number,                        
                         to_bid_period_name_in_var varchar2 );                  
                                                                               
  -- procedure to copy the bid rules from one ( station, bid group )            
  -- to all other ( station, bid group )                                        
  procedure copy_bid_rule ( station_in_var      varchar2,                      
                            bid_group_in_var    number );                      
                                                                               
end CopyPackage;
0
 

Author Comment

by:ramchi22
ID: 11998236
Why does this error occur in the front-end?


While executing the application, the error is:
ORA-06550: line 1, column 7:
PLS-00201: 'MANPOW.COPYPACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 

Expert Comment

by:JuanCarlosReyesP
ID: 11998433
It seems privileges, if you call it from another schema you have to give g rant to it, or to the user where you g et that error message.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 1500 total points
ID: 11998451
ok,

You should do this:

log in as MANPOW in the database:



create a public synonym for this package like this;

SQL>create public synonym copypackage for manpow.copypackage;



then grant execution privelege to the PUBLIC;

SQL>grant execute on copypackage to PUBLIC;


then you should shoo away that error message.
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11998477
You have to execute a procedure of the package : MANPOW.COPYPACKAGE.<procedure_name>
0
 
LVL 12

Expert Comment

by:geotiger
ID: 11998564
You can either to use the login of the creator or use invoker rights clause to change the default to AUTHID CRRENT_USER.


invoker_rights_
clause
lets you specify whether the functions and procedures in the package execute with the
privileges and in the schema of the user who owns it or with the privileges and in the schema
of CURRENT_USER. This specification applies to the corresponding package body as well.
(For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i
Application Developer’s Guide - Fundamentals.)
This clause also determines how Oracle resolves external names in queries, DML operations,
and dynamic SQL statements in the package.
See Also: PL/SQL User’s Guide and Reference.
AUTHID
CURRENT_USER
specifies that the package executes with the privileges of CURRENT_USER.
This clause creates an "invoker-rights package."
This clause also specifies that external names in queries, DML operations,
and dynamic SQL statements resolve in the schema of CURRENT_USER.
External names in all other statements resolve in the schema in which the
package resides.
AUTHID
DEFINER
specifies that the package executes with the privileges of the owner of the
schema in which the package resides and that external names resolve in
the schema where the package resides. This is the default.
0
 
LVL 12

Expert Comment

by:geotiger
ID: 11998622
Here is one of my packages:

CREATE OR REPLACE PACKAGE df_pkg
    AUTHID CURRENT_USER    -- using invoker's privillege
AS
 
  TYPE a_refcur IS REF CURSOR;
 
  FUNCTION calDuration ( p_tm1 IN VARCHAR2, p_tp1 IN VARCHAR2,
    p_tm2 IN VARCHAR2, p_tp2 IN VARCHAR2 ) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES ( calDuration, WNDS, WNPS, RNDS );
 
  FUNCTION calAge ( p_cdt IN DATE, p_dob IN DATE,
    p_str IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES ( calAge, WNDS, WNPS, RNDS );
 
  FUNCTION calDuTime ( p_dt1  IN DATE, p_tm1a IN VARCHAR2,
      p_tm1b IN VARCHAR2, p_dt2  IN DATE, p_tm2  IN VARCHAR2
  ) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES ( calDuTime, WNDS, WNPS, RNDS );
 
  FUNCTION cvtD2H ( p_dt  IN NUMBER, p_tp  IN VARCHAR2 ) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES ( cvtD2H, WNDS, WNPS, RNDS );
 
  FUNCTION cvtSeconds ( p_sec IN NUMBER ) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (cvtSeconds, WNDS, WNPS, RNDS);
 
  FUNCTION fmtDate ( p_dn IN NUMBER ) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES (fmtDate, WNDS, WNPS, RNDS);
 
  FUNCTION object_exist (name VARCHAR2, type VARCHAR2)
      RETURN BOOLEAN;
 
  PROCEDURE copy_object_structure (
    p_sn   NUMBER,                   -- three digit study number
    p_vw   VARCHAR2,                 -- partial view name
    p_ot   VARCHAR2 DEFAULT 'table', -- object type
    p_oo   VARCHAR2 DEFAULT null     -- output object
  );
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

721 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