Solved

Oracle PL/SQL Package

Posted on 2013-05-31
9
446 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
  • 3
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:flow01
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
 
LVL 20

Expert Comment

by:flow01
Comment Utility
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
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.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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

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

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…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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