CONCEPTS
With V7 of the database came PL/SQL V2, which added the new functionality
of database stored procedures. Unfortunately, both SQL*Forms V3.0 and Oracle
Forms V4.0 still use PL/SQL V1. Because of this, when Oracle Forms comes
across a database stored procedure call in your code, it doesn't know how to
handle it.
In order to get around this limitation, the PL/SQL team wrote a set of
procedures that resolve the reference. When Oracle Forms comes across an
identifier, it checks to see if it is a variable, bind variable, table/view,
synonym, sequence, etc. If it is none of these things, the compiler calls a
stub generator to see if it can resolve the identifier as a database function
or procedure. In that case, a stub is generated for syntactical checking, and
the compiler continues. A stub is a PL/SQL procedure or function that has the
same header as the actual procedure or function so forms can perform
syntactical checks. (If the identifier is a package, the stub generator creates
a stub that references each program unit in the package.) From then on, the
stub is used, so the stub generation procedures are only needed at compile
time.
COMMON CAUSES OF ERRORS
(Causes listed below are explained in detail in POTENTIAL PROBLEMS)
PLS-103 Using SQL*Forms 3.0.16.11 or earlier
PLS-201 Server set up incorrectly
Invalid synonym
PLS-302 Procedure uses %type arugment
Extra rows in the PSTUBTBL table
Invalid synonym
PLS-303 Invalid synonym
Using SQL*Forms 3.0.16.11 or earlier
Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
PLS-306 Missing parameters/incorrect arguments
PLS-313 Invalid synonym
Procedure uses %type argument
Server set up incorrectly
Invalid datatype in procedure
PLS-363 Attempting to assign constant to in/out parameter
PLS-903 Stored procedure has only out argument types
ORA-4031 The SGA is too full to load the PSTUB procedure
FRM-40735 Type mismatch when calling database procedure
CORE DUMP Passing real value through integer
Attempting to declare variable precision or length
Stored procedure has only OUT argument types
DEBUGGING
The first step is to determine where the process is failing. We can do
this by calling the stub generator directly from SQL*Plus.
First off, you should be able to execute your procedure from SQL*Plus. If
you are unable to do this, check the permissions and syntax of your procedure.
When you know it can execute correctly, enter these lines:
VARIABLE a VARCHAR2(2000);
VARIABLE b VARCHAR2(2000);
DELETE FROM sys.pstubtbl;
EXECUTE sys.pstub('
PRINT b;
DELETE FROM sys.pstubtbl;
(For
the procedure is in a package, use the package name only instead.)
If PSTUB will not execute, or if you receive an error message referring to
another package, your server may not be set up correctly (see 'Server set
up incorrectly' in POTENTIAL PROBLEMS).
If you get a message about the shared pool, or about the package being invalid
or discarded, the problem may be that the SGA is to fragmented or full to
load the necessary stub generators (see 'The SGA is too full to load the
DIUTIL package' in POTENTIAL PROBLEMS).
If the result of the PRINT b is '$$$ s_notv6Compat', you may be trying to use
%type arguments (see 'Procedure uses %type argument' in POTENTIAL PROBLEMS).
If the result is '$$$ s_subp not found', the problem is that the stub
generator cannot find the procedure. This is probably because you do not
have a synonym set up. A limitation of stub generation is that it cannot
search across users for procedures or packages. You cannot get around this by
prefixing the package or procedure name with the name of the user
(username.procedure). You must declare a public synonym. To do this you need
to both create the synonym, and do a "grant execute on proc to public". If
the result is '$$$ s_other', this generally means that the procedure can't run
in sqlplus independently. The action to take would be to call RDBMS support.
If the stub was correctly generated, PRINT b will return the stub body for your
procedure (PRINT a will give you the package spec if your procedure is in a
package). If this is the case, the problem may be with your version of
SQL*Forms, or may be a problem with your procedure. You can scan the list of
POTENTIAL PROBLEMS to see if you can find the source of the problem.
The following is an example of a successful call to the stub generator:
SQL> create procedure donothing(var1 in number, var2 in out char) is
2 begin
3 null;
4 end;
5 /
Procedure created.
SQL> execute sys.pstub('donothing', null, :a, :b);
PL/SQL procedure successfully completed.
SQL> print b
B
----------------------------------------------------------------------
procedure donothing (VAR1 NUMBER, VAR2 in out CHAR) is begin stproc.in
it('begin donothing(:VAR1, :VAR2); end;'); stproc.bind_i(VAR1); stproc
_bind_io(VAR2); stproc.execute; stproc.retrieve(2, VAR2); end;
POTENTIAL PROBLEMS
*Server set up incorrectly
There are 13 objects that need to be on the server and VALID for it to
create a stub. You can check the status of these objects by running the
following script from SQLPLUS while connected as SYS:
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL',
'DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;
The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
If any of these objects are missing or invalid, run DIUTIL.SQL as SYS
($ORACLE_HOME/rdbms/admin for Unix, \ORAWIN\DBS\ for windows. Note: On
windows, DIUTIL must be run as SYS from SQL*DBA on the server side). A
version of DIUTIL.SQL is usually shipped with the TOOLS--it may or may not
be newer than the server version (the only way to tell is to look at the
comments in the file)--if it is newer, feel free to use it. If you
are still having problems, you may be able to correctly install the
objects by running these scripts in this order: STANDARD.SQL, PIPIDL.SQL,
PIDIAN.SQL, DIUTIL.SQL, and PISTUB.SQL.
*Invalid datatype in procedure
There are only four datatypes supported for use in stored procedures that
are called from Oracle Forms, or called by another procedure that is called
by forms: VARCHAR2, NUMBER, DATE, and BOOLEAN, as documented in the release
notes. If a package has procedures with non-V6 compatible arguments/return
types, it will be dropped from the stub. A stub will still be returned, but
it will be missing these procedures.
*Stored procedure has only OUT argument types
The workaround to this problem is to use IN/OUT arguments instead.
*Extra rows in the PSTUBTBL table
The PSTUBTBL table is a temporary storage space for stubs, and after a stub
has been created, this table should be cleared out. If there are any rows
in PSTUBTBL, this can cause the stub generation process to fail. How do
rows get committed into PSTUBTBL?
1) A user runs the sys.pstub procedure from SQL*Plus, and afterword
they commit. If this is the case, the solution is simple: log
into sqlplus and type "delete from sys.pstubtbl".
2) Two different version of Oracle Forms are referencing the same
procedure. If this is the case, try to upgrade the older version,
and if you are running both V3.0 & V4.0, make sure that you are
using V4.0.12.
3) The stored procedures are being referenced by and older version of
forms (older than V3.0.16.12.7 on UNIX systems). Solution:
upgrade version of SQL*Forms.
*The SGA is too full to load the DIUTIL package
The DIUTIL package must be loaded into the SGA in order for a stub to be
generated. This package takes 32K, and it is not uncommon for heavily
loaded systems to not have that much contiguous SGA available. You have a
couple of options in this case--you can wait until later and try again, but
this is only a temporary fix. If this is a common problem, you can increase
your SGA size, or you can 'pin' the PSTUB procedure into the SGA so that it
won't be aged out. 7.0.15 and later versions of the RDBMS includes a
package called DBMS_SHARED_POOL that will allow you to do this. If this
package is not installed, you can install it by running DBMSPOOL.SQL. To
make sure there is room in the SGA to load the package, you can do this
right after startup, or you can clear the SGA by logging into SQLPLUS as SYS
and typing:
ALTER SYSTEM FLUSH SHARED_POOL;
Do this three times to make sure the SGA is flushed. After that, run PSTUB
on DIUTIL, or generate your form, as this will put the DIUTIL package into
the SGA, then (from SQLPLUS as SYS) type:
EXECUTE DBMS_SHARED_POOL.KEEP('DIUTIL');
*Invalid synonym
Double-check that your synonym is valid, and granted to public.
*Missing parameters/incorrect arguments
Make sure the parameters you specify in Oracle Forms match those specified in
the database procedure.
*Attempting to assign constant to in/out parameter
In/out parameters expect to send a value back to Oracle Forms. You cannot
assign a value to a constant.
*Referencing synonym with access granted through a role
Access must be explicitly granted.
*Procedure uses %type argument (bug 190597)
%type is supported in newer versions of DIUTIL.SQL (but %type must still
evaluate to a V6 compatible datatype). There is a patch available for this
bug for SQL*Forms V3.0.16.12 and later and Oracle Forms V4.0.11 and later.
*Using V3.0.16.11 or earlier
Referencing stored procedures works only in V3.0.16.12 and later.
*Type mismatch when calling database procedure
Check that the datatypes of your parameters match what is specified in the
database stored procedure.
*Passing real value through integer
This type mismatch may cause a core dump.
*Attempting to declare variable precision or length
The precision and length of variables are determined by forms, and should
not be specified in the procedure.
*Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
This uses a binary_integer datatype, which is not supported.
LIMITATIONS
-----------
*In Oracle Forms, you may reference database stored procedures, but you cannot
debug them from forms. There are two tables called USER_SOURCE and
USER_ERRORS which store information about PL/SQL use and errors.
*You cannot reference database stored package variables or cursors from forms.
*On 7.0.13 and earlier of the RDBMS, Boolean values do not return the correct
values
*On 3.0.16.12 of forms and earlier, there may be some problems with BOOLEAN
values--better to use NUMBER and 0 and 1.
*You cannot look up a remote subprogram via a synonym until RDBMS 7.1.1.
*Performance:
A development group had this experience--"Generation performance seems to
degrade over time in a multi-user system when many forms are being generated
and they all have several stored procedure references. We analyzed this using
tkprof and it turned out that it was the query of SYS.PSTUBTBL which was
slowing everything down. The trace file statistics for the query were huge
(>100000 block visists). All I could think of was that the nature of the
transaction to get stored procedure stub text (i.e. run the PSTUB generator
which inserts into PSTUBTBL, select from PSTUBTBL, rollback) was demanding
large amounts of read consistent data per session, and there was some
(unidentified) problem in the server. We fixed it by dropping and recreating
the SYS.PSUBTBL table, and the stats dropped right down and generation
performance increased."
*References:
There is more information on calling stored procedures from Oracle Forms
in the Oracle Forms release notes.
No comments:
Post a Comment