For the oracle.sql types, it is a configuration parameter to use those
types and was a design decision. I'd rather be using the Java types, but
I've had trouble in the past where Jpublisher decided that Java Double
should be have an intermediate mapping to NUMBER in the JPublisher code
when the Oracle UDT variable is declared as a BINARY_DOUBLE. It ended up
translating as Double -> NUMBER -> BINARY_DOUBLE. This caused the value
retrieved from the database to not bitwise match the value that was stored.
Using double mapped to BINARY_DOUBLE (IEEE-754) solved this as there should
be no translation between Java's Double and BINARY_DOUBLE (they are both
IEEE-754). Oracle also had some time zone issues with Date objects going in
so a decision was made to stick with the oracle.sql types and handle the
type mapping in our code.
Here is the mapping of your sample package.
CREATE PACKAGE xx AS
PROCEDURE P (X VARCHAR2);
PROCEDURE P (Y VARCHAR2);
END xx;
====================
public void p (
oracle.sql.CHAR Y)
throws java.sql.SQLException
{
try {
/*@lineinfo:generated-code*//*@lineinfo:73^5*/
// ************************************************************
// #sql [getConnectionContext()] { CALL CWMS_20.CWMS_TEST.P(
// :Y) };
// ************************************************************
{
// declare temps
oracle.jdbc.OraclePreparedStatement __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_TEST.P(\n :1 ) \n; END;";
__sJT_st =
__sJT_ec.prepareOracleStatement(__sJT_cc,"0usace.cwms.db.oracle.CwmsTest",theSqlTS);
// set IN parameters
__sJT_st.setCHAR(1,Y);
// execute statement
__sJT_ec.oracleExecuteUpdate();
} finally { __sJT_ec.oracleClose(); }
}
public void pvC (
oracle.sql.CHAR X)
throws java.sql.SQLException
{
try {
/*@lineinfo:generated-code*//*@lineinfo:94^5*/
// ************************************************************
// #sql [getConnectionContext()] { CALL CWMS_20.CWMS_TEST.P(
// X => :X) };
// ************************************************************
{
// declare temps
oracle.jdbc.OraclePreparedStatement __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_TEST.P(\n X => :1 ) \n;
END;";
__sJT_st =
__sJT_ec.prepareOracleStatement(__sJT_cc,"2usace.cwms.db.oracle.CwmsTest",theSqlTS);
// set IN parameters
__sJT_st.setCHAR(1,X);
// execute statement
__sJT_ec.oracleExecuteUpdate();
} finally { __sJT_ec.oracleClose(); }
}
On Wednesday, April 25, 2012 11:12:25 PM UTC-7, Lukas Eder wrote:
>
> 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
>
