Re: Help Calling Stored Procedure from Forms30

DAVE -- Because of the client/server cooperation that occurs at

        compile time to resolve references to stored procedures --
        where in essence the client, Forms 3.0 or 4.0, asks the server
        if the current user has access to a stored procedure by some
        name that it can't find locally in the form -- any problems
        with the setup of the server can cause the form to fail to
        look up these stored procedures.

        Run the following SQL script from SQLPLUS while connected as
        the "SYS" user:

	REM
	REM Display Statuses of Key
	REM Database Objects
	REM
	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;
    
	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 is missing or invalid on your system,
        then as SYS, rerun the script DIUTIL.SQL which should be among
        all of the server setup SQL scripts (?/rdbms/admin on Unix, or
        \ORAWIN\DBS\ on Windows).

        Other reasons you might be having problems referring to a
        stored procedure from Forms are:



          (*) The stored procedure or function has arguments of


              datatype other than the four types which are documented
              as supported: VARCHAR2, NUMBER, DATE, BOOLEAN



          (*) You're calling a procedure or function in a package


              which *contains* a procedure or function (maybe not the
              one you're calling) that uses a datatype for its
              arguments that is not one of the four listed above.



          (*) You're calling a stored procedure or functions whose


              arguments (albeit of one of the four supported types)
              are declared as being of type TABLE.COLUMN%TYPE. This
              syntax will be supported in a future maintenance
              release, but is a reason what you are running now might
              be giving problems.

        The above might be the problem if you can successfully
        reference *some* but not all of the stored procedures you're
        trying to use from Forms. Another less common problem, however
        worth mentioning is:



          (*) You've fragmented (or Oracle has fragmented) your SGA


              such that there doesn't exist a contiguous free chunk of
              at least 32K -- which is required to perform some of the
              client/server lookup... Or the lookup mechanism may have
              been aged out of the shared pool after a period of
              non-use and didn't have enough contiguous memory to go
              back into after being "woken up" later.

        The 7.0.15 release of Oracle7 includes a package called
        DBMS_SHARED_POOL which has a procedure called KEEP that allows
        you to pin important program units in memory so that they stay
        in the SGA instead of being candidates for LRU aging. It can
        help to pin the 'DIUTIL' package in memory using the syntax:

              SQL>  execute dbms_shared_pool.keep('diutil');

        If this is not done immediately following a startup, you can
        clean up the shared pool area before issuing the above command
        by logging on as SYS and performing the command:

              SQL>  alter system flush shared pool;

        Hopefully, some of this will help you narrow down what might
        be the problem.

--

Steve Muench         Email: smuench_at_oracle.com
Forms Development      CIS: 73404,676
Product Manager
Oracle Corporation