Solved

Oracle PL/SQL Package

Posted on 2013-05-31
9
453 Views
Last Modified: 2013-06-17
I inherited a massive package from another university to implement. I got numerous errors on compile of a type "procedure is not defined in this scope". A workaround for some of these was to declare the procedure in the specifications, another was to change the order in which the package was declared in the specification and defined in the package body. This does not work for all cases. For an attempted example, procedure_4 has numerous calls to procedure_5, and procedure_5 has numerous calls to procedure_4,  and "not defined in this scope" errors are encountered during compile (I am using PL/SQL Developer, not Toad), are there Oracle hints that can be used, and where/how should they be inserted in the package?

Thank you,
Bill
0
Comment
Question by:pendlewe
[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
  • 3
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 39212556
1. Did it compile  on the other university or did you inherited it as work in progress ?
    What was de Oracle database version it was compiled, what version are you using now ?.

2. You don't need the workaround to specify the procedure in the specification:
     you als can use a "forward declaration"  in the body:
     Same code as declaration in the specification at the beginning of the body. (but i'm not sure of the exact order in respect to cursor and variable definition in the body:  when I get an error a just move them : try first to place them after such definitions)

3. Compilation is done by the Oracle database so I don't expect differences by calling the compilation from PL/SQL Developer, Toad or other tools.

4. If the procedures are declared (either in the specification or as forward declaration in the body)  "cross-calling"  should not give errors.

5. Check if the  declaration of the procedure matches exactly the body of the procedure :
    it might be that slight differences accepted in earlier versions are not allowed in later versions  of the database.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39213208
This was sort of covered in the previous post but not specifically asked.

Silly question but I have to ask:
Did you get both the package declaration and package body code?
0
 

Author Comment

by:pendlewe
ID: 39214803
I have both the declaration and package body. I am thinking this version never ran, especially not on production.
I do not understand "forward declaration". I am Oracle 11g, and do not know what version the package was from.

Thanks,
Bill
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!

 
LVL 20

Expert Comment

by:flow01
ID: 39214935
declaration in the package specification

create or replace package bc
is

procedure a(p1 varchar2);
procedure b;
procedure c;

end;
/

create or replace package body bc
is

procedure a(p1 varchar2)
is
begin
   dbms_output.put_line('p1=' || p1);
   CASE p1
   WHEN  'A'  THEN b;c;
   ELSE  c;b;
   END CASE;
end;

procedure b
is
begin
   dbms_output.put_line('b');
end;

procedure c
is
begin
   dbms_output.put_line('c');
end;

end;
/

forward declaration in the package body

create or replace package bc
is

procedure a(p1 varchar2);

end;
/

create or replace package body bc
is

procedure b; -- this is forward declaration
procedure c; -- this is forward declaration

procedure a(p1 varchar2)
is
begin
   dbms_output.put_line('p1=' || p1);
   CASE p1
   WHEN  'A'  THEN b;c;
   ELSE  c;b;
   END CASE;
end;

procedure b
is
begin
   dbms_output.put_line('b');
end;

procedure c
is
begin
   dbms_output.put_line('c');
end;

end;
/
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39216012
>>I have both the declaration and package body. I am thinking this version never ran, especially not on production.

Extract the version from production and compare them.

Check out DBMS_METADATA.GET_DDL:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#i1019414


You have the following options to extract packages/bodies/both:
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
0
 

Author Comment

by:pendlewe
ID: 39216189
Thanks for the link to DBMS docs, there are options I was unaware of. Unfortunately I do not, and never will, have access to the code origin. This is a state university system, and code is *supposed* to be standardized and shared. I was hopeful Oracle hints would be a help. I am thinking either someone sabotaged the code I received, or this was an early and unsuccessful attempt on an earlier version of Oracle and SCT's Banner HE software. I am working on Oracle 11g and Banner 8.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39216206
Since I doubt you can post the code and cannot reach out to the DBA's that provided the code to you, it looks like you will have to continue debugging this on your own.

Try the forward declarations posted by flow01.
0
 

Author Closing Comment

by:pendlewe
ID: 39254684
You may close this issue.
It turned out that opening the package from PL/SQL Developer objects was the wrong thing to do. The correct answer, from another source, was to open package specification and package body in separate windows.
When the package specification is compiled before the package body, Oracle knows about all the functions and procedures, and does not throw any error of the "not defined in this scope" area.
Thanks to all who replied, the answers were accurate, and the dbms reference was helpful.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 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