RDBPROD: Calling Stored Procedures With Output Parameter Does Not Work Via OCI Services (Doc ID 261834.1)

Last updated on JULY 05, 2017

Applies to:

Oracle SQL/Services for Rdb on OpenVMS - Version 7.1 and later
HP OpenVMS VAX
HP OpenVMS Itanium
HP OpenVMS Alpha

Symptoms

When using stored procedures in Rdb which have an output value, various error messages can be observed if you are connected to the Rdb database through OCI Services (formerly SQL*Net for Rdb). Depending on the type of the return parameter and whether the connection to Rdb is direct through OCI Services or via a database link, these error messages can differ.

For example, these procedures defined in your Rdb database:

CREATE MODULE test_mod
LANGUAGE SQL
--
PROCEDURE Proc_int (:val INTEGER);
BEGIN
set :val = 100;
END;
--
PROCEDURE Proc_char (:val char(3));
BEGIN
set :val = '100';
END;
--
PROCEDURE Proc_real (:val REAL);
BEGIN
set :val = 100.0;
END;
--
END MODULE;

Calling one of the stored procedures via OCI Services without using a database link fails with

SQL> var i number
SQL> execute proc_int(:i);

PL/SQL procedure successfully completed.

SQL> print :i;
ERROR:
ORA-03106: fatal two-task communication protocol error

SQL> print :i;
ERROR:
ORA-09100: Message 9100 not found; No message file for product=NATCONN,facility=GTW
%SQL-F-DATTYPUNK, Data type unknown. Expression cannot use only host variables

Using SQL/Services 7.2.x the error message looks a little bit different:

SQL> print :c;
ERROR:
ORA-32800: internal error [No corresponding Oracle message for Rdb error]
%SQL-F-DATTYPUNK, Data type unknown. Expression cannot use only host variables

The same error messages are seen with character or floating return values.

The executor log file shows the %SQL-F-DATTYPUNK error message if full logging is enabled.

Using these procedures via database link produce different error messages:

SQL> var i number
SQL> execute proc_int@wksoci(:i);
BEGIN proc_int@wksoci(:i); END;

*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 1

The executor log file shows the following error message if full logging is enabled:

> />Stored Procedure PROC_INT
> />0 rows updated.
Oracle error: 1460 for function: PL/SQL RPC

In the previous example the value is 100. This is the output if the value is 7 for example:

SQL> var i number
SQL> execute proc_int@wksoci(:i);

PL/SQL procedure successfully completed.

SQL> print :i;

I
----------
55

55 is the ASCII value of 7.

This is the result if the procedure should return a character value via a database link:

SQL> var c char(3);
SQL> execute proc_char@wksoci(:c);
BEGIN proc_char@wksoci(:c); END;

*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from WKSOCI
ORA-03113: end-of-file on communication channel
ORA-06512: at line 1

The executor log file shows the following error message if full logging is enabled:

> />Stored Procedure PROC_CHAR
Rdb operation..: Executing Stored Procedure
Rdb error...(0): %RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime
-SYSTEM-F-ACCVIO, access violation, reason mask=04,
virtual address=0000000000010007, PC=FFFFFFFF808E52BC, PS=0000001B
Assertion failed: "find_mblock(current_mgroup,mblock) == current_mgroup" in
file NATCONN$SRC_V071573:[CODE]GTME.C;1 at line 468.
%NONAME-E-NOMSG, Message number 00000002

The same error messages have been observed in SQL*Plus when using a floating return value via a database link, but the error message in the executor log file is different:


> />Stored Procedure PROC_REAL
> />0 rows updated.
%SYSTEM-F-ACCVIO, access violation, reason mask=00,
virtual address=0000000000000000, PC=FFFFFFFF808E6760, PS=0000001B

Improperly handled condition, image exit forced.

SQL/Services 7.2 might additionally write an OCISERV_BUGCHECK.DMP file with this summary:

Oracle Rdb OCI Server Release 7.2.0.0.0 - Production, Level 1.7
SQL*Net for Rdb
OCI-F-BUGCHECK in UTL_BUGCHECK at line 928
HARD EXCEPTION ENCOUNTERED: 0000000C
SYSTEM-F-ACCVIO, access violation, virtual address=0000000000000000
Exception occurred at RDB$NATCONN72\gtorpc + 000013EC
Called from RDB$NATCONN72\gtorpc + 000003D8
Called from RDB$NATCONN72\gtwdre + 00000104
Running image RDB$NATCONN72.EXE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms