Consultor Eletrônico

Status: Verified

GOAL:

How to use ORACLE Packages with Progress ORACLE Dataserver

GOAL:

How to update schema holder if Oracle has packages that the dataserver application will use

FACT(s) (Environment):

Oracle DataServer

FIX:

This solution applies to ORACLE DataServer versions 7 and later.

It outlines the steps to call ORACLE packages from Progress DataServer for ORACLE.

ORACLE Packages are collections of PL/SQL stored procedures and functions. A package has two parts, package specification and package body. You must create both correctly and compile both without errors on the ORACLE side before you invoke it from Progress (see example below).
When creating or updating a Progress schema holder against the ORACLE database that contains the Package, you must select the Package object (Package Specification) defined in ORACLE during the schema pull process.

Once the schema holder is created or updated, the Package object selected earlier is not visible from the Progress Data Dictionary. You only see all stored procedures defined in the ORACLE Package Body. Invoke these procedures as normal stored procedures from Progress using the run STORED-PROCEDURE <procedure-name> syntax.

For example:

1) In ORACLE, create a Package called "mypack" defined as follows
(SQL Plus or other appropriate tool may be used for this purpose):

create package mypack as
procedure myproc;
end mypack;

--> Package created

create package body mypack as
procedure myproc;
select * from customer;
end myproc;
end mypack;

--> Package Body created

2) Using the Progress Data Administration tool, create or update
your schema holder and select the Package object "mypack" from
the ORACLE object list for the schema pull.

3) To call a stored procedure defined in the Package, use the
RUN STORED-PROCEDURE statement.