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! On Mon, Jun 4, 2018 at 5:05 AM Lukas Eder <[email protected]> wrote: > 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 > > > > 2018-06-02 8:58 GMT+02:00 Lukas Eder <[email protected]>: > >> 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 >> >> >> Am Freitag, 25. Mai 2018 schrieb 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 >>> <https://gist.github.com/rponte/1a437f9084f7c18847682eca437cbc16>. >>> >>> What do you think? >>> >>> On Thursday, May 17, 2018 at 7:09:54 PM UTC-3, Rafael Ponte wrote: >>>> >>>> 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, >>>> >>> -- >>> 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. >>> >> > -- > 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. > -- Rafael Ponte TriadWorks | Formação Java http://cursos.triadworks.com.br -- 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.
