Solved

Oracle PL/SQL Package

Posted on 2013-05-31
9
454 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

635 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