(*long*) Re: Package procedure call from forms

The README.30 (sometimes named 'release.doc' on Unix ports) file shipped with Forms 3.0 documents the various restrictions (most of which have quite simple equivalences to achieve your result). Here's the relevant section for your reference. In particular, see the section on the restriction regarding directly referencing SCHEMA.PACKAGE.SUBPROGRAM which can be easily solved by creating a synonym for SCHEMA.PACKAGE or a synonym for PACKAGE.SUBPROGRAM, such that the reference in PL/SQL ends up looking like SYMBOL.SYMBOL instead of SYMBOL.SYMBOL.SYMBOL.

Hope this helps.

Steve Muench         Email: smuench_at_oracle.com
Forms Development      CIS: 73404,676

(*) OVERVIEW

       A database procedure is a PL/SQL block designed to be executed by
       the server-side PL/SQL engine. It may accept inputs, and may return
       outputs, neither of which is mandatory. It runs under the security
       domain (or schema) of the *creator* of the procedure, and not the
       current user. The current user needs EXECUTE privilege on the
       procedure to use it.  One important difference is that server-side
       PL/SQL procedures do not understand references to SQL*Forms bind
       variables (like :BLOCK.FIELDNAME, :GLOBAL.VARNAME, or
       :SYSTEM.CURSOR_FIELD). Any data which procedures need for processing
       must be passed into the "black box" via parameters of appropriate
       datatype, package variables, or by selecting from tables.

       For this reason it is advisable to structure your SQL*Forms
       procedures now in such a way that they accept inputs and return
       results in parameters. This will make the eventual migration of the
       procedure into the database as painless as adding the word CREATE in
       front of the PROCEDURE declaration, and running the script resulting
       script in SQLPLUS.

       


   (*) SYNTAX


       ~~~~~~
       Call a stored procedure/function from within SQL*Forms exactly as
       you would invoke a form-level procedure/function:


            DECLARE    ---------------/ Example SQLFORMS Trigger /---------
               ld DATE;
               ln NUMBER;
               lv VARCHAR2(30);
            BEGIN
               /*
               || Calling Form-Level Procedure/Function
               */
               forms_procedure_name( ld, ln, lv );
               ld := forms_function_name( ln, lv );
       
               /*
               || Calling Database Procedure/Function
               */
               database_procedure_name( ld, ln, lv );
               ld := database_function_name( ln, lv );
            END;



   (*) SUPPORTED DATATYPES FOR


       PARAMETERS & FUNCTION RETURN VALUES
       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       Stored Procedures, Functions, and Packages are created using version
       2.0 of PL/SQL within the Oracle7 database. Since SQL*Forms 3.0 is
       built on PL/SQL version 1.0, for backward compatibility reasons only
       the following PL/SQL 2.0 datatypes are supported for Parameters and
       as Function Return values.

                VARCHAR2   -- Maximum of VARCHAR2(2000)
                NUMBER
                DATE
                BOOLEAN

       Recall that your stored procedures and functions can *internally*
       be written using all new PL/SQL version 2.0 datatypes and
       functionality. The above restriction on datatypes only applies to
       the interface that your stored routines have with their version 1.0
       PL/SQL counterparts, namely parameters and function return values.

       An attempt to reference a stored procedure or stored function which
       uses unsupported parameter or return-value datatypes will result in
       the failure to "recognize" the stored subprogram, and an error will
       be generated:

           PL/SQL error 313 at line xxx, column yyy
              'PROCNAME' not declared in this scope

       or

           PL/SQL error 201 at line xxx, column yyy
              identifier 'FUNCTNAME' must be declared.



   (*) SUPPORTED CONSTRUCTS 


       ~~~~~~~~~~~~~~~~~~~~
       When invoking a stored procedure or function, only the following
       subset of possible usages is supported:

                   ProcName(arg1,...,argN)
                   FuncName(arg1,...,argN)
          PackName.ProcName(arg1,...,argN)
          PackName.FuncName(arg1,...,argN)

       To access a subprogram (i.e. Procedure or Function) in another
       user's schema or one at a remote database you must create a synonym
       to "hide" the username and/or Db_Link name from the PLSQL compiler
       such that the result takes the form:

                     ProcSynonym(arg1,...,argN)
                     FuncSynonym(arg1,...,argN)
            PackSynonym.ProcName(arg1,...,argN)
            PackSynonym.FuncName(arg1,...,argN)

       You can create synonyms to nickname:

           Subprogram_at_DbLink
           Package_at_DbLink
           Package.Subprogram_at_DbLink
           Schema.Subprogram_at_DbLink
           Schema.Package
           Schema.Package_at_DbLink
           Schema.Package.Subprogram_at_DbLink

       Where 'Subprogram' is either Procedure or Function.

       For example to call the package function 'LIBOWNER.LIB_HR.GET_SSN'
       you would create a synonym for the LIB_HR package which includes the
       Schema name as follows:

           CREATE SYNONYM lib_hr_syn
                      FOR libowner.lib_hr;

       Then invoke the function from within your form like:

          ss_num := lib_hr_syn.get_ssn(:Emp.Empno);
              
       If the package function were at a remote site accessible via a
       database link named 'BASEL', for example, then you could create a
       synonym for the package, including the database link name:

           CREATE SYNONYM basel_lib_hr_syn
                      FOR libowner.lib_hr_at_basel;

       and invoke the function within your PL/SQL code as:

          ss_num := basel_lib_hr_syn.get_ssn(:Emp.Empno);

       Alternately you can create a synonym for the function itself,
       "hiding" both the Schema and DbLink information:

           CREATE SYNONYM basel_lib_hr_get_ssn_syn
                      FOR libowner.lib_hr.get_ssn_at_basel;

       and invoke the function from forms as:

          ss_num := basel_lib_hr_get_ssn_syn(:Emp.Empno);

       Of course, any of the synonyms above could have been created as
       PUBLIC SYNONYMS if appropriate.



   (*) NAME RESOLUTION


       ~~~~~~~~~~~~~~~
       When encountering the name of an identifier which could be
       a procedure or function, the PLSQL compiler uses a precedence
       mechanism to resolve possible ambiguities.

       If an identifier like 'PROCNAME' is encountered which has the
       structure of a procedure or function, the compiler will use the
       first match found in the following search order:

          (1) Is it defined within the current PLSQL block?
          (2) Is it a standard PLSQL command?
          (3) Is it a SQL*Forms packaged procedure/function?
          (4) Is it a user-defined, Form-Level procedure/function?
          (5) Is it defined in package DBMS_STANDARD on the server-side?
          (6) Does the current user have access to any such procedure/function
              on the server side?

       If the answer is NO to all of the above, then the compiler signals
       an error:

           PL/SQL error 313 at line xxx, column yyy
              'PROCNAME' not declared in this scope

       or

           PL/SQL error 201 at line xxx, column yyy
              identifier 'FUNCTNAME' must be declared.



   (*) IMPLEMENTATION


       ~~~~~~~~~~~~~~
       When you generate a form containing references to stored procedures,
       functions, or packages, SQL*Forms must perform the following:

            (1) Insure that the stored subprogram to which you make
                reference exist on the server during compilation.

            (2) Include an intermediate "access routine" for each
                stored subprogram you reference. There will be one
                access routine for each procedure, function or package 
                that is referenced. If you reference functions or
                procedures within a package, they will use the access 
                routine associated to the entire package. The access 
                routines handles the integration between PL/SQL version 1 
                and version 2, and are included in the .FRM file of your 
                generated form. These access routines are not visible
                by the designer.
        


   (*) CONSIDERATIONS


       ~~~~~~~~~~~~~~
       Processing within the form is on hold until the stored procedure or
       function completes execution, so the network and database load come
       into play when considering response time. Recall that the first time
       some user executes a stored procedure or function, it's executable
       code gets cached in the Oracle7 SGA. Subsequent executions by you or
       any other user are thus faster. The first time any subprogram within
       a package is referenced, the entire package is loaded and shareable.

       Use a database procedure instead of a Forms procedure when: 

       (1) It provides standard functionality that other tools will want
           to share -- like validation and calculations, 

       (2) performs a significant amount of DML operations -- in order to
           have them performed in a bundle by the server, 

       (3) If new PL/SQL V2 functionality like TABLES, RECORDS, or TYPES is
           required -- since the PL/SQL on the SQL*Forms side will remain 
           V1 in Version 3.0 of SQL*Forms.
              


   (*) RESTRICTIONS


       ~~~~~~~~~~~~
       SQL*Forms disables commits in stored procedures and functions that
       are called while SQL*Forms (Runform) is active. A stored procedure
       or function may not issue a COMMIT or ROLLBACK when called by forms.
       An attempt to do so will raise the error:

       ORA-0034: Commit and Rollback from PL/SQL disabled for this session

       However, if your C program calls SQL*Forms by way of the IAPCAL()
       interface, your stored procedures can issue commits once control is
       returned to the C program from the iapcal routine.

       
       Since SQL*Forms creates the "access routine" for each stored
       subprogram to which your trigger/form-level-procedure PL/SQL code
       makes reference, it is NOT possible to write a form containing calls
       to stored procedures or functions that will generate against both
       Oracle version 6.0 and version 7.0. Once a form includes a reference
       to at least one stored subprogram, then it must be generated
       against a Version 7.0 database, otherwise compilation errors will
       result when the names of stored subprograms cannot be resolved by
       the PL/SQL compiler. 

       
       Character (CHAR or VARCHAR2) values passed-IN to a stored procedure
       or function, passed-OUT from a stored procedure, or returned from
       a stored function may not exceed 2000 characters. Actual character
       parameters exceeding 2000 characters will be truncated to 2000 
       before passing to the stored procedure/function. Any OUT character
       parameters or character return values exceeding 2000 characters will
       be truncated to a length of 2000. The truncation which may occur
       on IN, OUT or return character values is performed without raising
       the PL/SQL VALUE_ERROR exception, and without raising a SQL*Forms
       truncation error.

============================================================================

(*) OVERVIEW

       Procedures and functions can be organized into Packages, to form a
       "suitcase" of PL/SQL objects. Packages can contain variables which
       are persistent for a user's entire session  (akin to a server-side
       GLOBAL variable and just as useful), as well as Cursors which may
       remain open across calls to functions and procedures in the
       package. 

       See the PL/SQL version 2.0 User's Guide and Reference for more
       information on packages.



   (*) EXAMPLE


       ~~~~~~~
       CREATE OR REPLACE PACKAGE LIB_HR AS
          -- Retrieve Social Security Number for an Employee
          FUNCTION get_ssn( theEmpNo NUMBER ) RETURN NUMBER;

          -- Hire a new Employee and return his/her Employee Number
          FUNCTION hire_employee( theName VARCHAR2,
                                  theDept NUMBER,
                                  theSal  NUMBER,
                                  theDate DATE,
                                  theSSN  NUMBER ) RETURN NUMBER;

          -- Terminate an existing Employee
          PROCEDURE fire_employee( theEmpno NUMBER,
                                   theReason VARCHAR2 );

          -- Set Signing Bonus Variable
          PROCEDURE set_bonus( newValue NUMBER );

       END LIB_HR;

       CREATE OR REPLACE PACKAGE BODY LIB_HR AS
          -- Package Variables --
          signingBonus NUMBER := 1000;

          -- Package Cursor --
          CURSOR next_empid
              IS SELECT empid_sequence.NEXTVAL
                   FROM dual;

          -- Retrieve Social Security Number for an Employee
          FUNCTION get_ssn( theEmpNo NUMBER ) RETURN NUMBER IS
             tmpSSN NUMBER;
          BEGIN
             SELECT ssn
               INTO tmpSSN
               FROM emp
              WHERE empno = theEmpNo;
             RETURN( tmpSSN );
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                RETURN( -1 );
          END;

          -- Hire a new Employee and return Employee Number
          FUNCTION hire_employee( theName VARCHAR2,
                                  theDept NUMBER,
                                  theSal  NUMBER,
                                  theDate DATE,
                                  theSSN  NUMBER ) RETURN NUMBER IS
             tmpEmpNo NUMBER;
          BEGIN
             IF (NOT next_empid%ISOPEN) THEN
                OPEN next_empid;
             END IF;
             FETCH next_empid INTO tmpEmpNo;
             -- We don't have to CLOSE the cursor because it will
             -- remain open between calls

             INSERT INTO EMP( empno,    ename, deptno, sal, 
                              hiredate, ssn,   bonus )
             VALUES( tmpEmpNo, theName, theDept, theSal, 
                     theDate, theSSN, signingBonus );
             RETURN( tmpEmpNo );
          EXCEPTION
             WHEN OTHERS THEN
                RETURN( -1 );
          END;

          -- Terminate an existing Employee
          PROCEDURE fire_employee( theEmpno NUMBER,
                                   theReason VARCHAR2 ) IS
          BEGIN
             DELETE FROM emp
                   WHERE empno = theEmpno;
             INSERT INTO terminations( empno, reason )
                  VALUES( theEmpno, theReason );
          END;

          -- Set Signing Bonus Variable
          PROCEDURE set_bonus( newValue NUMBER ) IS
          BEGIN
             signingBonus := newValue;
          END;

       END LIB_HR;



   (*) CONSIDERATIONS


       ~~~~~~~~~~~~~~
       The first time a package is referenced (by any user on the instance)
       the entire package is loaded into the Oracle7 SGA to make subsequent
       invocation of any procedures or functions it contains very fast.



   (*) RESTRICTIONS


       ~~~~~~~~~~~~
       As SQL*Forms only provides direct support for invoking stored
       procedures and functions, and not for directly referencing package
       variables or package cursors, you must design a function or
       procedure to manipulate package variables and package cursors within
       the code on the server side. If their datatypes are among the four
       supported above, values of package variables and fetched cursor data
       can be returned to the (SQL*Forms) caller via parameters or a
       function return value.

       SQL*Forms requires that both Package Specification and Body be valid
       in order to compile a Forms PL/SQL block which makes reference
       to the package.

============================================================================

       The following packages are create then the Oracle7 kernel is
       installed:

           Dbms_Standard
           Dbms_Transaction
           Dbms_Session
           Dbms_Ddl
           Dbms_Utility

       All packages listed above run as the invoking user rather than the
       package owner.

       Only package dbms_standard is a 'standard extension'.  This means
       that its procedure names are inserted into the scope just outside of
       a package or top-level procedure, but before the kernel's package
       'STANDARD'.

       Public synonyms are created during execution, and EXECUTE privilege
       is granted to public, for the above packages.

       From SQL*Forms, you can invoke procedures included in any of these
       packages by using the syntax <package>.<procedure>, for example:

                DBMS_SESSION.SET_ROLE('role');

       For information on the procedures included in these packages refer
       to the Oracle7 documentation.

--

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