I couldnt find a way to edit the post I just made, sorry. I just wanted to also mention that JPublisher also maps the whole package to a single class. Each individual procedure or function in the package becomes methods on that class.
On Wednesday, April 25, 2012 5:03:45 PM UTC-7, [email protected] wrote: > > 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 >> >
