jOOQ does not understand Oracle Dbms_Sql.Varchar2_Table

[Rafael Ponte]

Hi,

I'm using Oracle 11g XE and I have this specific package:

create or replace PACKAGE My_Pkg IS

     -- just my specific type
     TYPE Varchar2_List IS TABLE OF Varchar2(4000);
 
     -- trying to use my type and Dbms_Sql.Varchar2_Table
     procedure does_something(p_ora_list Dbms_Sql.Varchar2_Table, p_my_list Varchar2_List);

END My_Pkg ;

Notice my package has 2 associative array types: one for Oracle Dbms_Sql.Varchar2_table and the other one for my custom type (Varchar2_List).

So when I try to generate code via jOOQ generator (v3.10.6) I'm getting this class:

public class MyPkg extends PackageImpl {

    /**
     * @deprecated Unknown data type. Please define an explicit {@link org.jooq.Binding} to specify how this type should be handled.
     * Deprecation can be turned off using <deprecationOnUnknownTypes/> in your code generator configuration.
     */
    @java.lang.Deprecated
    public static void does_something(Configuration configuration, Object pOraList, Object pMyList) {
        // ...
    }

}

It seems like jOOQ doesn't know both types, so it's considering them as Unknown data types. As far as I know jOOQ supports associative arrays for default SQL types (Number, Varchar2 etc) and custom SQL types (create type "Product" as Object (...)).

Am I doing something wrong?

Thanks,

[Lukas Eder]

2018-05-18 0:09 GMT+02:00 Rafael Ponte <rpo...@gmail.com>:

Hi,

I'm using Oracle 11g XE and I have this specific package:

create or replace PACKAGE My_Pkg IS

     -- just my specific type
     TYPE Varchar2_List IS TABLE OF Varchar2(4000);
 
     -- trying to use my type and Dbms_Sql.Varchar2_Table
     procedure does_something(p_ora_list Dbms_Sql.Varchar2_Table, p_my_list Varchar2_List);

END My_Pkg ;

Notice my package has 2 associative array types: one for Oracle Dbms_Sql.Varchar2_table and the other one for my custom type (Varchar2_List).

So when I try to generate code via jOOQ generator (v3.10.6) I'm getting this class:

public class MyPkg extends PackageImpl {

    /**
     * @deprecated Unknown data type. Please define an explicit {@link org.jooq.Binding} to specify how this type should be handled.
     * Deprecation can be turned off using <deprecationOnUnknownTypes/> in your code generator configuration.
     */
    @java.lang.Deprecated
    public static void does_something(Configuration configuration, Object pOraList, Object pMyList) {
        // ...
    }

}

It seems like jOOQ doesn't know both types, so it's considering them as Unknown data types.

Yes, in general, in order for those types to be known, you have to include the SYS schema in the code generator, or at least those parts of the SYS schema that you want to reference. Unfortunately...

 

As far as I know jOOQ supports associative arrays for default SQL types (Number, Varchar2 etc) and custom SQL types (create type "Product" as Object (...)).

This is incorrect. jOOQ 3.10 doesn't support associative arrays yet. The relevant feature request is here:

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

Supporting this for Oracle 12c might be feasible relatively easily, as the JDBC driver (version 12.2) added support for associative arrays. If driver support isn't available, it might be possible to serialise / deserialise the arrays using some tricks. In 11g, it will be much harder as fewer tricks are available.

What you probably had in mind is the fact that jOOQ 3.11 internally uses DBMS_SQL types to serialise some data in some contexts, e.g. in the newly added INSERT/UPDATE/DELETE .. RETURNING emulation for multi-row DML statements, see:

https://blog.jooq.org/2018/05/02/how-to-run-a-bulk-insert-returning-statement-with-oracle-and-jdbc/

But again, this is about jOOQ 3.11 and about Oracle 12c, not 11g.

I hope this helps,

Lukas

[Rafael Ponte]

Hi Lukas,

Thanks again for all the explanation.

I thought jOOQ didn't support associative arrays and PLSQL tables only for Record Types (aka PLSQL Types) but as you said it doesn't support them for any PLSQL Type at all. At least that's what I understood now.

I also thought DBMS_SQL.Varchar2_Table was an Object Type (aka SQL Type) but it isn't. That's why jOOQ doesn't support it in Oracle 11g (and yes, you're right, I misunderstood that because your post).

So to solve my issue I had to create my custom SQL Type:

create or replace type Varchar2_List is table of varchar2(4000);

Now jOOQ is able to generate my classes without problems! 

Although I try to favor PLSQL Types over SQL ones as much as I can, unfortunately we still use Oracle 11g here and I have to deal with its limitations related to JDBC driver. For my sake, I'm learning some ways to work with jOOQ in my not so good reality.

Thanks, Lukas.

[Lukas Eder]

2018-05-22 14:54 GMT+02:00 Rafael Ponte <rpo...@gmail.com>:

Hi Lukas,

Thanks again for all the explanation.

I thought jOOQ didn't support associative arrays and PLSQL tables only for Record Types (aka PLSQL Types) but as you said it doesn't support them for any PLSQL Type at all. At least that's what I understood now.

I can't say yet if there will be any difference between the two types of table / array types (record elements or scalar value elements). It's not unlikely there will be.

 

I also thought DBMS_SQL.Varchar2_Table was an Object Type (aka SQL Type) but it isn't. That's why jOOQ doesn't support it in Oracle 11g (and yes, you're right, I misunderstood that because your post).

Yes, DBMS_SQL is a package, not a schema.

 

So to solve my issue I had to create my custom SQL Type:

create or replace type Varchar2_List is table of varchar2(4000);

Now jOOQ is able to generate my classes without problems! 

Yep, that's the current workaround.

 

Although I try to favor PLSQL Types over SQL ones as much as I can, unfortunately we still use Oracle 11g here and I have to deal with its limitations related to JDBC driver. For my sake, I'm learning some ways to work with jOOQ in my not so good reality.

I also favour those types and I keep hinting at Oracle that they should better integrate the PL/SQL types with the SQL types. From how they feel to programmers, they're the same thing. It's a pity there's so much bridging and glue code that needs to be written to convert between the two, even when writing PL/SQL.

Luckily, they're moving towards the right direction, including newer versions of the JDBC driver, which can serialise / deserialise the types uniformly (give or take 1-2 bugs, unsupported edge cases)

Lukas

[Rafael Ponte]

Interesting to know Oracle is investing some effort in improving JDBC driver to make developer's life easier. Not supporting boolean and other types is very annoying and bothers me too much. 

As far as I know Oracle 12c doesn't serialize Record Types but converts them to temporary Object types. If I'm not wrong I think I read this in the own 12c documentation. Or are you talking about another thing?

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

--

Rafael Ponte

TriadWorks | Formação Java
http://cursos.triadworks.com.br

[Lukas Eder]

2018-05-24 2:31 GMT+02:00 Rafael Ponte <rpo...@gmail.com>:

Interesting to know Oracle is investing some effort in improving JDBC driver to make developer's life easier. Not supporting boolean and other types is very annoying and bothers me too much. 

Boolean types, I believe, are supported with ojdbc 12.1. But of course, why worry about that when jOOQ can emulate them for you as well :-)

 

As far as I know Oracle 12c doesn't serialize Record Types but converts them to temporary Object types. If I'm not wrong I think I read this in the own 12c documentation. Or are you talking about another thing?

I don't know how they managed to pull it off. I wouldn't be surprised if they went through as many hoops as jOOQ does :-)

 

[Rafael Ponte]

According to documentation Oracle creates a temporary SQL type with the same name of PLSQL Type:

All PL/SQL package types are mapped to a system-wide unique name that can be used by JDBC to retrieve the server-side type metadata. The name is in the following form:
[SCHEMA.]<PACKAGE>.<TYPE>

I didn't find it but I read a good example how it works in Oracle 12: Oracle creates a new Object Type to workaround this!

[Lukas Eder]

Excellent, thanks for digging that up!

[Rafael Ponte]

Lukas,

Do you think is it possible to use Pipelined Functions to workaround the JDBC driver support to array of record types?

I mean, the driver doesn't understand PLSQL types but it does cursors, so jOOQ could generate a pipelined function to convert an array of record types to a sys_refcursor. Something like this:

declare
    l_p      jOOQ.Product_t;
    l_cursor Sys_refcursor;
begin
    -- converts array of record types to sys_refcursor
    open l_cursor for
        select pf.*
          from table(jOOQ.pipelined_function) pf; -- this function returns an array of Product_t

    -- iterates over cursor
    loop
        fetch l_cursor into l_p;
        exit when l_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('code=' || l_p.code);
        DBMS_OUTPUT.PUT_LINE('name=' || l_p.name);
    end loop;
    close l_cursor;
end;

I created a gist with details here.

What do you think?

[Lukas Eder]

Hi Rafael,

That's an interesting idea, thanks for your suggestion.

Currently, all my efforts go into making jOOQ 3.11 happen next week. I hope I will have time to play around with pipelined functions, soon.

Thanks,

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+unsubscribe@googlegroups.com.

[Lukas Eder]

Hi Rafael,

Thanks for your patience in this matter.

I've had another look at your idea. Essentially, you're suggesting that jOOQ should generate a synthetic package that wraps an existing, non-pipelined, PL/SQL table returning function call? Because to my knowledge, pipelined functions cannot be declared in anonymous blocks, so there's a requirement for synthetic stored objects.

Once we go down that route, I don't see the advantage of pipelined functions over generating synthetic SQL OBJECT and TABLE types, as you earlier suggested the JDBC driver is doing. With that path, we can pass data both in and out of the database. Pipelined functions would only allow for passing data out of the database. Both approaches require storing synthetic objects, which will be useful anyway, in the long run. The relevant feature request is here:

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

I hope this helps,

Lukas

[Rafael Ponte]

Hi Lukas,

Thanks for answering!

Yeah, hours later I sent that email I realized that pipelined functions don't work in anonymous blocks, so it would be necessary to generate a synthetic package/procedure for this. This may be a little bit challenging for some projects where developers (and jOOQ) don't have permission to create objects in a target schema. Although I don't see much problems with this, I wanted to avoid that.

Well, my initial idea was creating a synthetic pipelined function without the need to create synthetic object types, but as you said it may be better and more powerful to generate synthetic object types instead, similar what Oracle 12c does.

Thanks again, Lukas!

Lukas

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.

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