Calling Oracle package function returning a nested table

[Sascha Herrmann]

Hi!

I am wondering whether that is supported. It is hard to tell from the documentation.

The generator created a wrapper alright. But when I call it I get a "Ungültiger Spaltentyp: 1111" exception. So my guess is it is not supported?!

I could create a new PL/SQL function wrapping the original function and return something else, of course. Just wanted to make sure that it is unsupported before doing it.

Using version 3.4.2.

Regards,

Sascha

[Lukas Eder]

Hello,

I agree that these elements are not well documented right now. We're working on a tutorial for this area, but it isn't ready yet.

This is a common question, not only for Java developers, but also for (Oracle) SQL developers. There is in fact no way to access PL/SQL RECORD or TABLE types from the Oracle SQL engine. This extends to JDBC, unfortunately, and is not a jOOQ-related limitation. Some details can be seen here:

http://stackoverflow.com/q/6410452/521799

If you want to use Oracle's object-relational features with jOOQ (or with JDBC in general), you will have to create SQL types as such:

CREATE TYPE o AS OBJECT (...);

CREATE TYPE t AS TABLE OF OBJECT (...);

A workaround to allow for using PL/SQL RECORD and TABLE types also from SQL / JDBC is to declare the FUNCTION to be PIPELINED. In that case, Oracle will generate a synthetic "bridge function" to bridge access from the SQL engine to the PL/SQL types. I've just recently discovered this myself at a customer site:

http://stackoverflow.com/a/24796673/521799

If you follow one of the above techniques, jOOQ will fully support OBJECT, TABLE, and VARRAY types.

Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sascha Herrmann]

Hi!

Thanks for the reply.

The type is declared in the database as:

create or replace TYPE NUMBER_22_TABLE
AS TABLE OF NUMBER( 22 )

The function:

  Function get_flattened_roles( p_staffMemberId In Integer )
  Return number_22_table
  Is

So ... that's supported?

Sascha

[Lukas Eder]

Hello Sascha,

Yes, that particular TYPE and FUNCTION are supported.

Are those causing any issues at your side?

Best Regards,

Lukas

[Sascha Herrmann]

Hi!

Yes.

We call:

    Object field = AuthUtil.getFlattenedRoles( context.configuration(), BigInteger.valueOf(1));

and get:

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [begin ? := "IRIS"."AUTH_UTIL"."GET_FLATTENED_ROLES"("P_STAFFMEMBERID" => ?); end;]; Ungültiger Spaltentyp: 1111

at org.jooq.impl.Utils.translate(Utils.java:1477)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:505)

at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:312)

at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:261)

at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:218)

at com.ge.med.medora.services.dbaccess.iris.packages.AuthUtil.getFlattenedRoles(Unknown Source)

at Test.<init>(Test.java:36)

at Test.main(Test.java:41)

Caused by: java.sql.SQLException: Ungültiger Spaltentyp: 1111

at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3916)

at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)

at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)

at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)

at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1568)

at org.jooq.tools.jdbc.DefaultCallableStatement.registerOutParameter(DefaultCallableStatement.java:85)

at org.jooq.impl.AbstractRoutine.registerOutParameters(AbstractRoutine.java:589)

at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:298)

... 5 more

Not sure yet why this happens. Any suggestions what I could try to do to troubleshoot this?

Sascha

[Lukas Eder]

That's really interesting. I quickly checked this on my instance using this schema:

BEGIN

  EXECUTE IMMEDIATE 'DROP PACKAGE p';

EXCEPTION WHEN OTHERS THEN NULL;

END;

/

BEGIN

  EXECUTE IMMEDIATE 'DROP TYPE NUMBER_22_TABLE';

EXCEPTION WHEN OTHERS THEN NULL;

END;

/

CREATE OR REPLACE TYPE NUMBER_22_TABLE

AS

  TABLE OF NUMBER( 22 );

  /

CREATE PACKAGE p

AS

  FUNCTION get_flattened_roles(

      p_staffMemberId IN INTEGER )

    RETURN number_22_table;

END p;

/

CREATE PACKAGE BODY p

AS

FUNCTION get_flattened_roles(

    p_staffMemberId IN INTEGER )

  RETURN number_22_table

IS

BEGIN

  RETURN NUMBER_22_TABLE(1, 2, 3, 4);

END get_flattened_roles;

END p;

/

Running the code generator yields:

[INFO] Generating package       : TEST.P

[INFO] Generating routine       : GetFlattenedRoles.java

And, essentially:

public class P extends org.jooq.impl.PackageImpl {

/**

* Call <code>TEST.P.GET_FLATTENED_ROLES</code>

*/

public static org.jooq.test.oracle.generatedclasses.test.udt.records.Number_22TableRecord 

            getFlattenedRoles(org.jooq.Configuration configuration, java.math.BigInteger pStaffmemberid) {

So, as you see, a org.jooq.ArrayRecord type is generated as a return type for this package function.

Calling the above method works fine:

System.out.println(P.getFlattenedRoles(configuration, BigInteger.ONE));

Output: TEST.NUMBER_22_TABLE(1, 2, 3, 4)

Now, since you're assigning your own call to getFlattenedRoles to "Object", I suspect that something went wrong when generating the ArrayRecord type. Can you please check:

- Is it in the same schema as the package?

- If not, did you include the other schema?

- If not, there should be a warning message in the log output.

- If the above doesn't apply, maybe there is an issue when rewriting types? I remember you used <forcedType/> to rewrite all NUMBER types using a regex. Maybe that regex also matched this particular type (NUMBER_22_TABLE)?

[Sascha Herrmann]

Hi!

Excellent points to check.

No, the type is not in the same schema. It's in a different one, with a public synonym which probably doesn't help. I wonder how the generator would find that type?!

And yes, it could be the re-write.

The generator created a method that returns Object which already made me suspicious. When I examine the DataType of the Field it is TABLE though.

I will try to add the type to the schema first and then re-check.

Sascha

[Lukas Eder]

Hi Sascha,

2014-08-13 13:24 GMT+02:00 Sascha Herrmann <goo...@maledictis.de>:

Hi!

Excellent points to check.

No, the type is not in the same schema. It's in a different one, with a public synonym which probably doesn't help. I wonder how the generator would find that type?!

Interesting detail

Currently, jOOQ does not explicitly support Oracle sysnonyms. This feature is on the roadmap:

https://github.com/jOOQ/jOOQ/issues/1563

I've changed my schema to this:

CREATE PUBLIC SYNONYM TTT FOR TEST.NUMBER_22_TABLE;

/

CREATE PACKAGE p

AS

  FUNCTION get_flattened_roles(

      p_staffMemberId IN INTEGER )

    RETURN TTT;

END p;

/

CREATE PACKAGE BODY p

AS

FUNCTION get_flattened_roles(

    p_staffMemberId IN INTEGER )

  RETURN TTT

IS

BEGIN

  RETURN TTT(1, 2, 3, 4);

END get_flattened_roles;

END p;

/

Quickly running this query here (similar to the one used by the code generator):

select type_owner, type_name

from   all_arguments 

where  owner = 'TEST'

and    object_name = 'GET_FLATTENED_ROLES'

and    package_name = 'P'

and    position = 0

yields

TYPE_OWNER | TYPE_NAME

-----------+----------

PUBLIC  | TTT

It looks like we should increase the priority of Oracle synonym support, then.

And yes, it could be the re-write.

The generator created a method that returns Object which already made me suspicious. When I examine the DataType of the Field it is TABLE though.

With the synonym, I could reproduce your issue. I have registered an issue for this. Even without formal SYNONYM support, this should be fixed. I suspect that synonym types should be substituted by their real types in procedure signatures, when generating code.

The issue is:

https://github.com/jOOQ/jOOQ/issues/3552

[Sascha Herrmann]

Hi Lukas,

wow you're fast. Yes, it was caused by the type living in a different schema.

I created the type in the same schema and now the generator generated properly Number_22TableRecord as return type.

So thanks for creating the issue. Remember ... when I talked about our special needs? :o)

Sascha

[Sascha Herrmann]

Hi!

I see that you planned it for 3.5.0. Probably this will also contain the fix for the "force of BigDecimal not working" (if I read the issue correctly).
Do you have a rough ETA for that? Just a ball park number?

Regards,

Sascha

[Lukas Eder]

Hi Sascha,

2014-08-13 14:20 GMT+02:00 Sascha Herrmann <goo...@maledictis.de>:

Hi Lukas,

wow you're fast. Yes, it was caused by the type living in a different schema.

I created the type in the same schema and now the generator generated properly Number_22TableRecord as return type.

I'll see that both synonyms and cross-schema type references will work. These things are normally part of our integration tests (e.g. cross-schema foreign key references). It seems that the possibility of importing types from other schemas might have been overlooked. It is only a code-generation issue, though.

 

So thanks for creating the issue. Remember ... when I talked about our special needs? :o)

Yes, I know :-)

Another customer with similar "special" needs (e.g. also Oracle AQ) will soon integrate with jOOQ and Oracle, so there will be a lot of synergies.

[Lukas Eder]

The bugfixes will be backported also to 3.4.3 (https://github.com/jOOQ/jOOQ/issues/3553), 3.3.4 (https://github.com/jOOQ/jOOQ/issues/3554), 3.2.7 (https://github.com/jOOQ/jOOQ/issues/3555). Unfortunately, GitHub doesn't allow to associate more than one milestone with an issue, which is why the issue is first associated with the upcoming minor release milestone.

jOOQ 3.5.0 is due for early Q4 2014. jOOQ 3.4.3 in the next 2-3 weeks.

Regards

Lukas

--

[Lukas Eder]

... note, of course, that a 3.5.0 snapshot release with a fix may already be available before that. I'll keep you posted

[Lukas Eder]

Hello,

The issue with cross-schema type references is resolved for 3.5.0. It was rather easy to fix.

Synonyms are a bit harder, because there is a possibility of transitive synonyms, such as:

CREATE SYNONYM Z.C FOR MY_SCHEMA.MY_TYPE;

CREATE SYNONYM Y.B FOR Z.C;

CREATE SYNONYM X.A FOR Y.B;

With a CONNECT BY query, it would be simple to retrieve tuples

(X.A, MY_SCHEMA.MY_TABLE)

(Y.B, MY_SCHEMA.MY_TABLE)

(Z.C, MY_SCHEMA.MY_TABLE)

The trouble is, PUBLIC synonyms are reported slightly differently, so the resulting query to produce the above tuples is this monster here:

SELECT "s3"."OWNER",

  "s3"."SYNONYM_NAME",

  connect_by_root "s3"."TABLE_OWNER" "TABLE_OWNER",

  connect_by_root "s3"."TABLE_NAME" "TABLE_NAME",

  SUBSTR(((((sys_connect_by_path((("s3"."TABLE_OWNER"

  || '.')

  || "s3"."TABLE_NAME"), ' <- ')

  || ' <- ')

  || "s3"."OWNER")

  || '.')

  || "s3"."SYNONYM_NAME"), 5)

FROM (

  SELECT "s2"."OWNER",

    "s2"."SYNONYM_NAME",

    "s2"."TABLE_OWNER",

    "s2"."TABLE_NAME"

  FROM (

    SELECT "s1"."OWNER",

      "s1"."SYNONYM_NAME",

      "s1"."TABLE_OWNER",

      "s1"."TABLE_NAME"

    FROM "SYS"."ALL_SYNONYMS" "s1"

    UNION

    SELECT "s1"."OWNER",

      "s1"."SYNONYM_NAME",

      'PUBLIC',

      "s1"."TABLE_NAME"

    FROM "SYS"."ALL_SYNONYMS" "s1"

  ) "s2"

  WHERE ("s2"."OWNER", "s2"."SYNONYM_NAME") <> (("s2"."TABLE_OWNER", "s2"."TABLE_NAME"))

) "s3"

START WITH EXISTS (

  SELECT 1 "one"

  FROM "SYS"."ALL_OBJECTS"

  WHERE ("s3"."TABLE_OWNER"              = "SYS"."ALL_OBJECTS"."OWNER"

  AND "s3"."TABLE_NAME"                  = "SYS"."ALL_OBJECTS"."OBJECT_NAME"

  AND "SYS"."ALL_OBJECTS"."OBJECT_TYPE" <> 'SYNONYM')

)

CONNECT BY ("s3"."TABLE_OWNER" = prior "s3"."OWNER"

AND "s3"."TABLE_NAME"          = prior "s3"."SYNONYM_NAME")

Before I implement this query in the jOOQ code generator, would you mind running it at your side to measure its execution time against your rather large database? That would be very helpful.

I think that a fix for this particular issue should be ready by Friday. I could then provide you with a 3.5.0-SNAPSHOT version.

Best Regards,

Lukas

[Sascha Herrmann]

Hi,

sure. The statement takes about 4 seconds on our newer development database. Not a great system equipment-wise but okay.

On our old (crappy) development database it takes 11 seconds.

The newer database should be typical for a current, moderately equipped database system, IMHO.

Sascha

[Lukas Eder]

Hello,

Thank you very much for this info. I think that is just within the reach of reasonable for a first step. The results are cached by jOOQ's code generator, so the query is run only once per code generation.

Best Regards,

Lukas

--

[Lukas Eder]

Hello,

As promised, both #3552 and #3556 are now fixed. I'll contact you shortly off-list for a download link to a 3.5.0-SNAPSHOT version

Cheers

Lukas