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.