[Appendix B] Calling Stored Procedures from PL/SQL Version 1.1Appendix B B. Calling Stored Procedures from PL/SQL Version 1.1Contents:Using Stubs to Talk to Server-Side PL/SQLRestrictions on Calling Stored ProceduresThe 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: Does not require any changes to the PL/SQL Version 1.1 base productDoes allow the Oracle Developer/2000 application to find and execute stored procedures Does not require any special syntax to distinguish between stored and local PL/SQL modules Achieving this effect, however, imposes several restrictions on the use of stored procedures: Only four datatypes are supported for module parameters: DATE, VARCHAR2, BOOLEAN, and NUMBER. You cannot directly reference a package object using the dot notation <schema>.<package>.<objname>. You cannot directly execute remote procedure calls using the standard syntax <procedure><dblink> You must provide an argument for each parameter in a stored module's parameter list, even if that parameter has a default value. (This restriction applies only to PL/SQL Version 2.0; this restriction goes away in Release 2.1 and beyond.) A stored procedure called from Oracle Developer/2000 cannot have only OUT argument types. From Oracle Developer/2000, you can call stored procedures, but you cannot debug them from within the Oracle Developer/2000 tool. (You will be able to do so with PL/SQL Release 2.2 and above; this release runs against Oracle7 Server Release 7.2, which contains the hooks for the step debugging in PL/SQL.) You cannot look up a remote subprogram via a synonym until RDBMS Version 7.1.1. 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_totalsThe 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. A.2 Using the GuideB.2 Restrictions on Calling Stored Procedures Copyright (c) 2000 O'Reilly & Associates. All rights reserved.