Hi Urs, Thank you very much for your detailed report. This definitely looks like a bug. I have created an issue for this: https://github.com/jOOQ/jOOQ/issues/7890
Passing timestamp values where date values would be expected works in SQL, but not so well in PL/SQL. jOOQ should definitely handle this better. I will investigate this soon and report further findings directly on the issue linked above. Thanks again, Lukas On Sat, Sep 22, 2018 at 7:57 AM Urs Hofmänner <[email protected]> wrote: > Hi, > > In an oracle database I have a table containing DATE fields > > create table TBS_WORT_STZ > ( > > WORT_STZ_ID NUMBER(10) not null > > ... > ERFSG_DT DATE default SYSDATE not null, > > ... > ) > > > and a package function that uses its row type > > create or replace PACKAGE PKA_CD AUTHID DEFINER > IS > ... > > FUNCTION get_row_tbs_wort_stz (pi_wort_stz_id IN > TBS_WORT_STZ.WORT_STZ_ID%TYPE) > RETURN TBS_WORT_STZ%ROWTYPE; > > > The Package-Function generated by Jooq using option dateAsTimestamp > generates following sql to call the oracle package > > declare > type rowtype1 is record ( > "WORT_STZ_ID" number, > ... > "ERFSG_DT" timestamp, > ...); > "r1" rowtype1; > begin > "r1" := "..."."PKA_CD"."GET_ROW_TBS_WORT_STZ"("PI_WORT_STZ_ID" => ?); > ? := "r1"."WORT_STZ_ID"; > ... > ? := "r1"."ERFSG_DT"; > ...; > end; > > > But this causes > > java.sql.SQLException: ORA-06550: line 1, column 502: > PLS-00382: expression is of wrong type > ORA-06550: line 1, column 494: > PL/SQL: Statement ignored > > > If I modify and test the generated sql statement manually so that it uses > the types the table actually has it works > > declare > > type rowtype1 is record ( > > ... > > "ERFSG_DT" DATE, > > ...); > "r1" rowtype1; > begin > "r1" := "SCORPIO"."PKA_CD"."GET_ROW_TBS_WORT_STZ"(1); > end; > > > I didn't have this error without using dateAsTimestamp option to generate > the Java representation of the oracle db objects. > > From my point of view it looks like Jooq uses the wrong data type here. > > Or what else did I wrong? > > Regards, > Urs > > > > The generated package function looks like > > /* > * This file is generated by jOOQ. > */ > package ... > > > > > /** > * This class is generated by jOOQ. > */ > @Generated( > value = { > "http://www.jooq.org";, > "jOOQ version:3.11.5" > }, > comments = "This class is generated by jOOQ" > ) > @SuppressWarnings({ "all", "unchecked", "rawtypes" }) > public class GetRowTbsWortStz extends AbstractRoutine<GetRowTbsWortStzRecord> > { > > ... > > > /** > * The parameter > <code>SCORPIO.PKA_CD.GET_ROW_TBS_WORT_STZ.RETURN_VALUE</code>. > */ > public static final Parameter<GetRowTbsWortStzRecord> RETURN_VALUE = > createParameter( > > "RETURN_VALUE", > ch.scope.persistence.database.legacy.packages.pka_cd.udt.GetRowTbsWortStz.GET_ROW_TBS_WORT_STZ.getDataType(), > false, false); > > ... > > > and record type used looks like > > /* > * This file is generated by jOOQ. > */ > package ... > > > > > /** > * This class is generated by jOOQ. > */ > @Generated( > value = { > "http://www.jooq.org";, > "jOOQ version:3.11.5" > }, > comments = "This class is generated by jOOQ" > ) > @SuppressWarnings({ "all", "unchecked", "rawtypes" }) > public class GetRowTbsWortStzRecord extends > UDTRecordImpl<GetRowTbsWortStzRecord> implements Record17<... Timestamp, ...> > { > > ... > > > > public Timestamp getErfsgDt() { > return (Timestamp) get(12); > } > > > ... > > } > > -- > 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.
