Getting PLS-00258 when creating PL/SQL wrapper function for Java method

Question

I've got the following Java method (which I've loaded into an Oracle 11g database from its JAR using loadjava)

public int GatewayClientPoolHA( String[] DAUTAddresses,
            int[] DAUTPortArray,
            String sslKeystorePath,
            String sslKeystorePass )

Now, I want to wrap this Java method in a PL/SQL function, but I'm getting a PLS-00258 error with the following code. I presume this is because of the array input parameters? Any suggestions?

  CREATE OR REPLACE PACKAGE daut_2fa IS

  TYPE daut_addresses_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; 
  TYPE daut_port_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; 

  FUNCTION GatewayClientPoolHA (
      DAUTAddresses         IN daut_addresses_type,
      DAUTPortArray         IN daut_port_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN INTEGER;


END daut_2fa;
/
SHOW ERROR


CREATE OR REPLACE PACKAGE BODY daut_2fa IS

  FUNCTION GatewayClientPoolHA (
      DAUTAddresses         IN daut_addresses_type,
      DAUTPortArray         IN daut_port_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN INTEGER IS LANGUAGE JAVA
   NAME 'daut.GatewayClientPoolHA(java.lang.String[], int[], java.lang.String, java.lang.String) return int';



END daut_2fa;

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

It's actually the constrained return type it doesn't like; INTEGER is a constrained number, so that is causing the PLS-00258 error:

PLS-00258: constrained datatypes disallowed in CALL Specifications

Cause: A call specification for C or Java cannot have constraints on the PL/SQL formal parameter types. PL/SQL types which have have constraints are NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, INTEGER, INT, SMALLINT, DECIMAL, NUMERIC, DEC This includes NOT NULL constraints from POSITIVEN, NATURALN

Action: Use the unconstrained type for that PL/SQL formal declaration i.e NUMBER, BINARY_INTEGER or PLS_INTEGER

You need to RETURN NUMBER instead so it isn't constrained:

  FUNCTION GatewayClientPoolHA (
      DAUTAddresses         IN daut_addresses_type,
      DAUTPortArray         IN daut_port_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN NUMBER IS LANGUAGE JAVA
   NAME daut.GatewayClientPoolHA(java.lang.String[], int[], java.lang.String, java.lang.String) return int';

... but then you'll hit (in 11gR2 anyway):

PLS-00999: implementation restriction (may be temporary) INDEX TABLE parameters are disallowed

And even with unindexed PL/SQL tables you'll still get:

PLS-00999: implementation restriction (may be temporary) Non-schema collection parameters are disallowed in Java callout

So you need schema level (not PL/SQL) collections, created as separate types before your package is created:

CREATE TYPE daut_addresses_type IS TABLE OF VARCHAR2(50)
/
CREATE TYPE daut_port_type IS TABLE OF NUMBER
/

CREATE OR REPLACE PACKAGE daut_2fa IS

  FUNCTION GatewayClientPoolHA (
      DAUTAddresses         IN daut_addresses_type,
      DAUTPortArray         IN daut_port_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN NUMBER;


END daut_2fa;
/

CREATE OR REPLACE PACKAGE BODY daut_2fa IS

  FUNCTION GatewayClientPoolHA (
      DAUTAddresses         IN daut_addresses_type,
      DAUTPortArray         IN daut_port_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN NUMBER IS LANGUAGE JAVA
   NAME 'daut.GatewayClientPoolHA(java.lang.String[], int[], java.lang.String, java.lang.String) return int';

END daut_2fa;
/

Package body DAUT_2FA compiled

SHOW ERRORS

No errors.

The collection not being indexed may be a problem for you though, as you're presumably putting related values in the same index position in both lists. You may need an object type and a single table of those instead, if you can unpack that as a structure in Java. Something like:

import oracle.sql.STRUCT;
public class daut {
  public int GatewayClientPoolHA( STRUCT[] DAUTAddressesAndPorts,
              String sslKeystorePath,
              String sslKeystorePass )
  {
    ...
  }
}

and then

CREATE TYPE daut_addresses_port_type AS OBJECT (
  address VARCHAR2(50),
  port number
)
/

CREATE TYPE daut_addresses_port_tab_type AS TABLE OF daut_addresses_port_type
/

CREATE OR REPLACE PACKAGE daut_2fa IS

  FUNCTION GatewayClientPoolHA (
      DAUTAddressesAndPorts IN daut_addresses_port_tab_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN NUMBER;

END daut_2fa;
/

CREATE OR REPLACE PACKAGE BODY daut_2fa IS

  FUNCTION GatewayClientPoolHA (
      DAUTAddressesAndPorts IN daut_addresses_port_tab_type,
      sslKeystorePath       IN VARCHAR2,
      sslKeystorePass       IN VARCHAR2
 ) RETURN NUMBER IS LANGUAGE JAVA
   NAME 'daut.GatewayClientPoolHA(oracle.sql.STRUCT[], java.lang.String, java.lang.String) return int';

END daut_2fa;
/

There's an example in the documentation that passes an object type; this is just taking it further and passing a collection of objects, so you should be able to refer to each STRUCT element of the array, using the appropriate type mapping for each field of the object/structure. Though I haven't actually tried that part.

Or use a single varray of strings but concatenate the port value (e.g. '127.0.0.1:1521') and decompse that in Java - which might be easier...

Thanks Alex, very helpful response - you've gone above and beyond. You're correct that I need the collection to be indexed. However, I don't quite follow what you mean by "an object type and a single table of those" - could you expand a little on that? — J Dor, Sep 22 '16 at 16:23

Getting PLS-00258 when creating PL/SQL wrapper function for Java method

1 Answers1