[Appendix B] Calling Stored Procedures from PL/SQL Version 1.1

Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: A.2 Using the GuideAppendix BNext: B.2 Restrictions on Calling Stored Procedures
 

B. Calling Stored Procedures from PL/SQL Version 1.1

Contents:
Using Stubs to Talk to Server-Side PL/SQL
Restrictions on Calling Stored Procedures

The Oracle Developer/2000 tools use PL/SQL Version 1.1, while the PL/SQL versions on the server range from 2.0 to 8.x. The PL/SQL version inside the Oracle Developer/2000 tools will be upgraded to PL/SQL Release 2.3 sometime in 1998 with Release 2 of Oracle Developer/2000. In the meantime, the tools group at Oracle Corporation has had to come up with a way to allow applications based on Oracle Developer/2000 to call stored procedures in the most transparent fashion possible. The end result is a mechanism which:

Achieving this effect, however, imposes several restrictions on the use of stored procedures:

B.1 Using Stubs to Talk to Server-Side PL/SQL

The mechanism employed by the Oracle Developer/2000 tools to handle stored procedures is called stub generation. A stub is a PL/SQL procedure or function which has the same header as the actual procedure or function. A stub for a package contains a stub or specification for each module in the package.

When the Oracle Developer/2000 tool encounters an identifier in a PL/SQL code segment, it checks to see if it is a local PL/SQL variable, then a tool bind variable, table/view, synonym, sequence, and so on through the precedence order of object name resolution. If it is unable to resolve the reference, the PL/SQL compiler calls a stub generator to see if it can resolve the identifier as a stored function or procedure. In that case, a stub is generated for syntactical checking, and the compiler continues. Because the stub looks the same to the Oracle Developer/2000 tool as the stored module, the tool can continue to perform syntactical checks using that stub. Stub generation only occurs at compile time.

You can see what kind of stub PL/SQL generates in the Oracle Developer/2000 tool by executing the stub generator directly from SQL*Plus, as shown below:

VARIABLE not_needed VARCHAR2(2000);
VARIABLE stubtext VARCHAR2(2000);
DELETE FROM SYS.PSTUBTBL;
EXECUTE SYS.PSTUB ('&1', NULL, :not_needed, :stubtext);
PRINT stubtext;
DELETE FROM SYS.PSTUBTBL;

where "&1" is a substitution variable. Notice that I delete from the stub table, SYS.PSTUBTBL, before and after my call to the SYS pstub generator program. This is a temporary table and must be cleaned up manually if you are going to call the PSTUB program yourself.

Place this code in a file named showstub.sql and you can then call it as follows to show a module's stub:

SQL> start showstub calc_totals

The following is an example of the output from this showstub program:

SQL> CREATE PROCEDURE calc_totals
SQL>      (company_id_in IN NUMBER, type_inout IN OUT VARCHAR2)
SQL> IS
SQL> BEGIN
SQL>       ... all the code ...
SQL> END;
SQL> /

Procedure created.

SQL> start showstub calc_totals

STUBTEXT
----------------------------------------------------------------------
procedure calc_totals (COMPANY_ID_IN NUMBER, TYPE_INOUT in out CHAR) is
begin stproc.in('begin calc_totls(:COMPANY_ID_IN, :TYPE_INOUT); end;');
stproc.bind_i(COMPANY_ID_IN); stproc.bind_io(TYPE_INOUT);
stproc.execute;
stproc.retrieve(2, TYPE_INOUT); end;

If the output from showstub is `$$$ s_notv6Compat', you may be trying to use parameters with the %TYPE attribute, which are not allowed in the parameter list of a stored procedure called from Oracle Developer/2000 tools. If the output is `$$$ s_subp not found', the problem is that the stub generator cannot find the module. This will happen if you have not created a synonym for a module which is owned by another user. The stub generator cannot search across different users to resolve a named object reference. You will have to create a public synonym for this module and then grant EXECUTE authority on that module to PUBLIC.


Previous: A.2 Using the GuideOracle PL/SQL Programming, 2nd EditionNext: B.2 Restrictions on Calling Stored Procedures
A.2 Using the GuideBook IndexB.2 Restrictions on Calling Stored Procedures

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference