Solved

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

Posted on 2004-09-07
10
3,380 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
10 Comments
 
LVL 34

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now