Hello, I can reproduce this using the jOOQ integration test schema. It seems that it is not possible to bind UDT values to stored procedures if UDTs belong to another user than the one currently connected. This is filed as #1693: https://github.com/jOOQ/jOOQ/issues/1693
Cheers Lukas 2012/8/9 [email protected] <[email protected]>: > Hi - > > > > I'm testing out the UDT portion of the database API that I reverse > engineered using JOOQ. I've run into the exception below (trimmed a bit). > Points to note… > > The API that I have exported is owned by a different user, 'WM'. This user > has the Views, UDTs, Packages, Tables, etc. > Regular uses only have permission to see certain parts of the schema owner's > API. > JOOQ exported the API while connected as a test user account, 'WMTEST' as > seen below. This ensures that only the permitted API parts are visible > externally. > > > I created a unit test against a package procedure that accepts an array of > UDTs and ran it as the test user, 'WMTEST'. The failure appears to be the > UDT being scoped to the test user rather than the schema owner? I think this > is true as I ran the same unit test as the schema owner ('WM') and the test > passes. Am I missing some call to associate the UDT with the proper schema? > > > > Test source: > > //connection established above as the test user. > > Wm_20Factory db = new Wm_20Factory(connection); > > > > //variables declared above to known good values. > > RetrieveTsMulti retrieveTsMulti = new RetrieveTsMulti(); > > retrieveTsMulti.setPEndInclusive(endTimeInclusive); > > retrieveTsMulti.setPMaxVersion(maxVersion); > > retrieveTsMulti.setPNext(getNextValue); > > retrieveTsMulti.setPOfficeId(officeId); > > retrieveTsMulti.setPPrevious(getPreviousValue); > > retrieveTsMulti.setPStartInclusive(startTimeInclusive); > > > > TimeseriesReqArrayRecord timeseriesReqArrayRecord; > > timeseriesReqArrayRecord = new TimeseriesReqArrayRecord(db); > > List<TimeseriesReqTypeRecord> list = new > ArrayList<TimeseriesReqTypeRecord>(); > > TimeseriesReqTypeRecord timeseriesReqTypeRecord = new > TimeseriesReqTypeRecord(); > > //is this needed? > > timeseriesReqTypeRecord.attach(db); > > timeseriesReqTypeRecord.setTsid(timeSeriesId); > > timeseriesReqTypeRecord.setUnit(units); > > timeseriesReqTypeRecord.setStartTime(startDate); > > timeseriesReqTypeRecord.setEndTime(endDate); > > > > list.add(timeseriesReqTypeRecord); > > timeseriesReqArrayRecord.setList(list); > > > > retrieveTsMulti.setPTimeseriesInfo(timeseriesReqArrayRecord); > > retrieveTsMulti.setPTimeZone(timeZone); > > retrieveTsMulti.setPTrim(trim); > > retrieveTsMulti.setPVersionDate(versionDate); > > > > long t1 = System.currentTimeMillis(); > > int execute = retrieveTsMulti.execute(db); > > Result<Record> tsvRc = retrieveTsMulti.getPAtTsvRc(); > > ConnectionPool.close(db.getConnection()); > > long t2 = System.currentTimeMillis(); > > assertNotNull(tsvRc); > > //further tests to confirm the call worked are below > > > > ===================================== > > The exception > > ===================================== > > > > org.jooq.tools.reflect.ReflectException: > java.lang.reflect.InvocationTargetException > > at org.jooq.tools.reflect.Reflect.on(Reflect.java:567) > > at org.jooq.tools.reflect.Reflect.call(Reflect.java:349) > > at org.jooq.impl.ArrayRecordImpl.createArray(ArrayRecordImpl.java:232) > > at org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:304) > > at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:146) > > at > org.jooq.impl.AbstractBindContext.bindValues(AbstractBindContext.java:136) > > at org.jooq.impl.ArrayConstant.bind(ArrayConstant.java:95) > > at > org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:161) > > at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:118) > > at org.jooq.impl.AbstractRoutine.bind(AbstractRoutine.java:354) > > at > org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:161) > > at org.jooq.impl.AbstractBindContext.bind(AbstractBindContext.java:118) > > at org.jooq.impl.Factory.bind(Factory.java:479) > > at > org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:309) > > at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:244) > > at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:237) > > at > usace.wm.db.oracle.packages.wm_ts.RetrieveTsMultiTest.test(RetrieveTsMultiTest.java:91) > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > > … > > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) > > Caused by: java.lang.reflect.InvocationTargetException > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > > … > > at java.lang.reflect.Method.invoke(Method.java:597) > > at org.jooq.tools.reflect.Reflect.on(Reflect.java:563) > > ... 35 more > > Caused by: java.sql.SQLException: invalid name pattern: > WMTEST.TIMESERIES_REQ_TYPE > > at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:554) > > at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:470) > > at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:324) > > at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:254) > > at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:135) > > at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:103) > > at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:72) > > at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:451) > > at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:329) > > at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:373) > > at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:110) > > at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1218) > > at oracle.sql.ARRAY.<init>(ARRAY.java:105) > > at > oracle.jdbc.driver.PhysicalConnection.createARRAY(PhysicalConnection.java:8913) > > at > oracle.jdbc.OracleConnectionWrapper.createARRAY(OracleConnectionWrapper.java:704) > > ... 40 more > > > >
