Service Stored Procedures Support on Oracle

Parameter types

Oracle supports multiple output entity parameters. You can use both basic and entity parameters for output. The maximum number of parameters is 100, of which 25 can be entity parameters.

Data conversions

The data types of operation parameters must closely match the stored procedure parameters. If necessary, explicit and implicit conversions are used to achieve an optimal mapping for all data types.

The supported parameter data type mappings for Oracle are shown in the following table. (The data type mappings shown in the table apply to both basic parameters and entity parameters.)

IN, OUT, and INOUT parameters

All parameter data is passed to Oracle as bound data. To achieve optimal mapping for all parameters, Oracle parameter data type and operator parameter data type information is used to determine the Oracle binding data types.

NULL support

Empty input string values are mapped to Oracle values as NULL values. There is one exception to this: empty Uniface boolean data types are always mapped to FALSE. This is standard functionality.

USYS$ORA_PARAMS

There are no specific connector settings for Oracle Service Stored Procedures.

Exception behavior

The exceptions shown in the following table are detected by the Oracle connector:

The following messages are generated by the Oracle connector when signature matching:

• Operation/Procedure name mismatch. This is detected by Oracle, and is reported with the message:

  ORA-6550 Component <stored procedure> must be declared

• Signature mismatch in number of arguments. This is detected by Oracle, and is reported with the message:

  ORA-1123 Wrong number of parameters

• Entity parameters with non-matching number of fields. This is detected by Oracle, and is reported with the message:

  ORA-1007 A reference was made to a variable not listed in the SELECT clause.

Example: SSP with Two Parameters

The following examples illustrate the use of Service Stored Procedures. The first example shows a Service Stored Procedure with two basic parameters. It is executed with the following activate ProcScript statement:

activate "EXAMPLE".sp_insert($in$, $inout$)

The sp_insert operation has the parameters shown in the table:

The Oracle SQL is as follows:

CREATE OR REPLACE
PACKAGE BODY "WORKTODO".EXAMPLE
  as
  procedure sp_ent (entcur1 IN OUT ent1)
  is
    BEGIN
      open entcur1 for select str, num from tab;
    END;
  procedure sp_insert (str IN varchar2,num IN OUT number)
  is
    BEGIN
     insert into tab values (str, num);
     num := num + 1;
   END;
END;
/

Example: SSP with Entity Parameter

The second example shows the use of a Service Stored Procedure with one entity parameter. It is executed with the following activate ProcScript statement:

activate "EXAMPLE".sp_ent("tab.model")

The sp_ent operation has the parameters shown in the table:

The tab entity has the fields shown in the table:

The Oracle SQL script is as follows:

CREATE TABLE tab (str varchar2(40), num number(20));
/
CREATE OR REPLACE
PACKAGE "WORKTODO".EXAMPLE
  as
  cursor c1 is select str, num from tab;
  type ent1 is ref cursor return c1%ROWTYPE;
  procedure sp_ent (entcur1 IN OUT ent1);
  procedure sp_insert (str IN varchar2,num IN OUT number);
END;
/

Example: Using an Oracle Function

An Oracle function needs to be wrapped in an Oracle procedure to use SSP functionality. For example:

CREATE OR REPLACE FUNCTION GET_TOTAL(
 part1 IN NUMBER,
 part2 IN NUMBER)
 RETURN NUMBER IS thetotal NUMBER;
BEGIN
  thetotal := part1 + part2;
  RETURN(thetotal);
END;
				
create or replace procedure test_function (
  p1 in number, 
  p2 in number, 
  p3 out number)
  as
  begin
  p3 := get_total(p1,p2);
  end;
/