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 <https://blog.jooq.org/2018/05/02/how-to-run-a-bulk-insert-returning-statement-with-oracle-and-jdbc/> ). 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. On Sunday, May 20, 2018 at 6:00:53 AM UTC-3, Lukas Eder wrote: > > > > 2018-05-18 0:09 GMT+02:00 Rafael Ponte <[email protected] <javascript:>>: > >> 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 > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
