Service Stored Procedures Support on Oracle

Service stored procedures are supported for the Oracle database connector.

The stored procedure name used for execution by Uniface is derived from the component name and the operation name:

Component name and component operation name specification
Literal component name Literal operation name Stored procedure name


Not specified Not specified 4GLComponentName.UnifaceOperationName MY_CPT.SP1
Not specified Specified LiteralOperationName1 sp1
Specified Not specified LiteralComponentName.UnifaceOperationName sp.SP1
Specified Specified LiteralComponentName.LiteralOperationName sp.sp1

1 These stored procedures have no package. All other stored procedures are included within packages.

Parameter names are not used during the execution of stored procedures. However, their ordering (that is, their relative position) is used for mapping parameters.

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.)

Uniface to Oracle parameter data type mappings
Oracle data type String Boolean Numeric Float (L)Date (L)Time (L)Datetime Raw Image
char Y Y Y Y          
varchar2 Y Y Y Y   Note Note    
number Y Y Y Y Note Note Note    
float Y   Y Y          
date Y       Y Y Y    
Note: This data type conversion depends on Oracle behavior which can be changed by a query. For example: alter session set NLS_DATE_FORMAT='DDMMYYYY HH24:MI:SS'. For more information on alter session parameters, see your Oracle documentation.

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.


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:

Exception message numbers and meanings
$procerror Meaning
-21 Connection failure
-55 Input parameters failure
-56 Output parameter failure
-150 Connector or Oracle failure
-166 Operation is not stateless
-1406 Memory allocation failure

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:

sp_insert operation parameters
Name Type Data Type Input/Output
u_str Basic String Input
u_num Basic Numeric Input/Output

The Oracle SQL is as follows:

procedure sp_ent (entcur1 IN OUT ent1)
open entcur1 for select str, num from tab;
procedure sp_insert (str IN varchar2,num IN OUT number)
insert into tab values (str, num);
num := num + 1;

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:

sp_ent operation parameters
Name Type Data Type Input/Output
u_ent Entity - Output

The tab entity has the fields shown in the table:

tab entity structure
Name Type Data Type
str S VC40
num N C20

The Oracle SQL script is as follows:

CREATE TABLE tab (str varchar2(40), num number(20));
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);

Example: Using an Oracle Function

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

part1 IN NUMBER,
part2 IN NUMBER)
thetotal := part1 + part2;
create or replace procedure test_function ( p1 in number, p2 in number, p3 out number)
p3 := get_total(p1,p2);
end; /

Related Topics