asked on

Oracle PL/SQL Package

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

flow01

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.

slightwv (䄆 Netminder)

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?

pendlewe

ASKER

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

flow01

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

slightwv (䄆 Netminder)

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

pendlewe

ASKER

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pendlewe

ASKER

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.