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
>
