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
Solved

Oracle PL/SQL Package

Posted on 2013-05-31
9
450 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
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 76

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

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 76

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

Suggested Solutions

Title # Comments Views Activity
constraint check 2 48
Input a SQl, output tables amd columns used in the SQL 19 55
update statement in oracle 9 29
Toad 12.10 Enterprise visual interface 4 18
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

808 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