Solved

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

Posted on 2004-09-07
10
3,499 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 96
how to trim oracle sql sentence in unix 17 71
form builder not starting 3 73
Oracle Insert not working 10 48
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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