That's an interesting approach... I had thought about this at first, too. But jOOQ doesn't map PL/SQL data types to oracle.sql.* types like JPublisher, so jOOQ might have other problems, even if OUT parameters were supported that way. For instance, jOOQ couldn't disambiguate VARCHAR2, VARCHAR, CHAR, CLOB parameters, as they're all mapped to java.lang.String.
But how does JPublisher handle this package for instance? CREATE PACKAGE xx AS PROCEDURE P (X VARCHAR2); PROCEDURE P (Y VARCHAR2); END xx; Thanks to the usage of named parameters, this is possible in PL/SQL. This probably isn't best-practice in PL/SQL, but it can be done (at least in 11g). The disambiguation will take place at the call-site, by generating PL/SQL like this: BEGIN P(X => 'abc'); END; This is done by jOOQ, to be able to support this kind of overloading, along with DEFAULT procedure parameters, which cannot be used through standard JDBC. Cheers Lukas 2012/4/26 [email protected] <[email protected]>: > JPublisher handles OUT variables as arrays that are filled by reference, > thus changing the method signatures. I.e. instantiate an oracle.sql.CHAR[1] > and the JPublisher generated code fills it by reference. It is clunky but it > works. Here is the generated code for the above retrieve_text2 procedure > with 2 outs. > > > public void retrieveText2 ( > oracle.sql.CLOB P_TEXT[], > oracle.sql.CHAR P_DESCRIPTION[], > oracle.sql.CHAR P_ID, > oracle.sql.CHAR P_OFFICE_ID) > throws java.sql.SQLException > { > try { > /*@lineinfo:generated-code*//*@lineinfo:1210^5*/ > > // ************************************************************ > // #sql [getConnectionContext()] { CALL CWMS_20.CWMS_TEXT.RETRIEVE_TEXT2( > // :P_TEXT[0], > // :P_DESCRIPTION[0], > // :P_ID, > // :P_OFFICE_ID) }; > // ************************************************************ > > { > // declare temps > oracle.jdbc.OracleCallableStatement __sJT_st = null; > sqlj.runtime.ref.DefaultContext __sJT_cc = getConnectionContext(); if > (__sJT_cc==null) sqlj.runtime.error.RuntimeRefErrors.raise_NULL_CONN_CTX(); > sqlj.runtime.ExecutionContext.OracleContext __sJT_ec = > ((__sJT_cc.getExecutionContext()==null) ? > sqlj.runtime.ExecutionContext.raiseNullExecCtx() : > __sJT_cc.getExecutionContext().getOracleContext()); > try { > String theSqlTS = "BEGIN CWMS_20.CWMS_TEXT.RETRIEVE_TEXT2(\n :1 > ,\n :2 ,\n :3 ,\n :4 ) \n; END;"; > __sJT_st = > __sJT_ec.prepareOracleCall(__sJT_cc,"56usace.cwms.db.oracle.CwmsText",theSqlTS); > if (__sJT_ec.isNew()) > { > __sJT_st.registerOutParameter(1,oracle.jdbc.OracleTypes.CLOB); > __sJT_st.registerOutParameter(2,oracle.jdbc.OracleTypes.CHAR); > } > // set IN parameters > __sJT_st.setCHAR(3,P_ID); > __sJT_st.setCHAR(4,P_OFFICE_ID); > // execute statement > __sJT_ec.oracleExecuteUpdate(); > // retrieve OUT parameters > P_TEXT[0] = (oracle.sql.CLOB)__sJT_st.getCLOB(1); > P_DESCRIPTION[0] = (oracle.sql.CHAR)__sJT_st.getCHAR(2); > } finally { __sJT_ec.oracleClose(); } > > } > > > > On Wednesday, April 25, 2012 1:21:08 PM UTC-7, Lukas Eder wrote: >> >> Hello, >> >> > I am trying out jOOQ (v2.2.2) against an Oracle 11.2.0.3 database API >> > that is heavily weighted towards pl/sql packages, views, and UDTs. >> > Previously I have used JPublisher to generate Java classes that can >> > call against this API. Using jOOQ - I am receiving compile errors in >> > the generated code. Is there a preferred method for reporting errors? >> >> You can report issues on this user group, or directly in Trac, if >> you're sure it's a bug, and if you have a SourceForge account: >> https://sourceforge.net/apps/trac/jooq/newticket >> >> I have created Ticket #1358 for this: >> https://sourceforge.net/apps/trac/jooq/ticket/1358 >> >> > The DBAs that develop the database side like to overload their calls >> > to provide returned data in a variety of means. Here is one of the >> > errors I am encountering with Oracle package procedures. Here are 3 >> > retrieve text calls in the package (note one is called >> > retrieve_text2). >> >> I see. jOOQ uses indexes for disambiguation of overloaded stored >> procedures in packages. This is done, too, to prevent compilation >> errors, as Java: >> - doesn't support OUT parameters >> - doesn't support named parameters >> >> However, there is no check if disambiguated names would collide with >> other, similar names. Hence, disambiguation must become a bit more >> sophisticated. How does JPublisher do it? >> >> Cheers >> Lukas
