calling an oracle funtion part of the package from teiid returns error.

[amitagrawal]

Hi,

i am able to call the oracle function from Teiid without any issue. However, if the same function is part of package then it returns the following erro. I am also making Name In Source property value to have full path including package ("MSS4"."PKG_Teiid_test.TEIID_USER_APP_ENT1") but still it gives the error.  any help to resolve this is highly appreicated.  I have also attached screen print of fucntion from teiid designer.

This my query from squirrel :

select * from teiid_user_app_ent1 where PCLIENTID = '1656'

7:55:02,627 WARN  [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue85) TEIID30020 Processing exception 'TEIID30504 MYSSDEVORACLE_pkg: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_Teiid_test.TEIID_USER_APP_ENT1"(?)}' for request GJyJg6E6wSdd.9.  Exception type org.teiid.core.TeiidProcessingException thrown from oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112). Enable more detailed logging to see the entire stacktrace.

[rareddy]

Looks like oracle driver is throwing an error, set logging DEBUG on Teiid in the server and re-run the query and capture the whole exception stack and attach for further suggestions.

[shawkins]

Do you mean to call this as a procedure?  Or should it be modeled as a Teiid function?

[amitagrawal]

below is the erro after enabling more logs. initially, the error was "at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45] Caused by: java.sql.SQLException: ORA-06550: line 1, column 20:
PLS-00114: identifier 'PKG_Teiid_test.USER_APP_ENTITL' too long"

so I reduced the package name length but now I get below error.

Also, I am able to call the package ffucntion without any issues from TOAD PL/SQL block.

TEIID30020 Processing exception 'TEIID30504 MYSSDEVORACLE_pkg: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1.TEIID_USER_APP_ENT1"(?)}' for request DNS5VyXaLI3G.6.  Exception type org.teiid.core.TeiidProcessingException thrown from oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112).: org.teiid.core.TeiidProcessingException: TEIID30504 MYSSDEVORACLE_pkg: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1.TEIID_USER_APP_ENT1"(?)}
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:534) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:321) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:280) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:101) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:384) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:250) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]
at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]
Caused by: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1.TEIID_USER_APP_ENT1"(?)}
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:457) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:302) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
... 24 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 14:
PLS-00201: identifier 'MSS4.PKG_T1.TEIID_USER_APP_ENT1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:749)
at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:742)
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
... 27 more

19:25:52,026 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue12) Removing tuplesource for the request DNS5VyXaLI3G.6
19:25:52,026 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue12) Removing TupleBuffer:
19:25:52,026 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue12) before rollback:org.teiid.dqp.internal.process.TransactionServerImpl@1a313e8(DNS5VyXaLI3G REQUEST ID:TransactionImple < ac, BasicAction: 0:ffff0a88a23b:49c8d3c0:521be3dc:b status: ActionStatus.RUNNING >)
19:25:52,041 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue12) after rollback : null
19:25:52,041 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue12) Sending error to client
19:25:52,041 DEBUG [org.teiid.TRANSPORT] (Worker0_QueryProcessorQueue12) send message: MessageHolder: key=380 contents=ResultsMessage rowCount=0 finalRow=-1
19:25:52,057 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue12) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@1a313e8(DNS5VyXaLI3G REQUEST ID:TransactionImple < ac, BasicAction: 0:ffff0a88a23b:49c8d3c0:521be3dc:b status: ActionStatus.ABORTED >)
19:25:52,057 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue12) after suspend : null

[shawkins]

What sql would you use from SQuireL to make the package based call?

[amitagrawal]

Hi Steve,

I am using below SQL from Squirrel.  TEIID_USER_APP_ENT1 has Name In Source property as "MSS4"."PKG_T1.TEIID_USER_APP_ENT1".

select * from TEIID_USER_APP_ENT1 where PCLIENTID = '1656'

[shawkins]

Amit, I'm asking outside of Teiid what SQL can you use to successfully make the function call?

[amitagrawal]

Steve,

please see below PL/SQL block which runs fine without any issue.

set serveroutput on size 1000000;
DECLARE

userAppEntitlements   sys_refcursor;
client_id   number(9);
client_name varchar2(500);
user_id varchar2(500);
first_name varchar2(500);
last_name varchar2(500);
email varchar2(500);
app_name varchar2(500);
user_type varchar2(500);

BEGIN

userAppEntitlements:= MSS4.PKG_T1.teiid_user_app_ent1( '99999' );
loop
FETCH userAppEntitlements INTO client_id,client_name,user_id,first_name,last_name,email,app_name,user_type;
DBMS_OUTPUT.PUT_LINE('client_id:' || client_id );
EXIT WHEN userAppEntitlements%NOTFOUND;
end loop;
CLOSE userAppEntitlements ;
end;

[shawkins]

If modeled as a procedure, the default JDBC handling by Teiid will use a CallableStatement with JDBC escaped procedure invocation syntax - ? = call ...  This may be introducing a visibility issue like http://stackoverflow.com/questions/970875/oracle-identifier-myschema-mytable-must-be-declared

Are you able from toad to issue "{ call MSS4.PKG_T1.teiid_user_app_ent1( '99999' )}" successully?

[amitagrawal]

based on my earlier PL/SQL block, can you provide code snipped on how to incorporate the function call using "{ call MSS4.PKG_T1.teiid_user_app_ent1( '99999' )}" in TOAD. as I mentioned earlier, there is no error in calling stand alone fucntion like "MSS4"."TEIID_USER_APP_ENT" from Teiid but returns error when it is part of the package.

[shawkins]

Amit, I'm not sure what the syntax would be as I don't have access to TOAD.  I know from SQuirreL you can't successfully make just the stored function call using the escape syntax as you need to bind an output parameter.  All I'm trying to confirm is that you get a similar error (presuming that your using the same connection details).

In any case since this works when outside of a package it really does seem like a visibility issue and not something to do with Teiid.

[amitagrawal]

Steve,

finally, the function worked fine. I guess the issue was related to xmi file not gettng refreshed in VDB. I remvoed xmi completely, added it back and then it worked fine. However, I have a different issue now where my function's parameter is table object.  pleae see the below specification of the fucntion.

function TEIID_USER_APP_ENT1 (

    pClientId           IN VARCHAR2,   

    pClientId_new  In short_string_col

    )

return sys_refcursor ;

when I query the below SQL from Teiid, I get below error.

select * from TEIID_USER_APP_ENT1 where PCLIENTID = '1656' and PCLIENTID_NEW in ( '1656' )

TEIID30020 Processing exception 'TEIID30492 Expressions of type OBJECT, CLOB, BLOB, or XML cannot be used in comparison: PCLIENTID_NEW IN ('1656').' for request nHRe6XibJC/g.5.  Exception type org.teiid.api.exception.query.QueryValidatorException thrown from org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:335).: org.teiid.api.exception.query.QueryValidatorException: TEIID30492 Expressions of type OBJECT, CLOB, BLOB, or XML cannot be used in comparison: PCLIENTID_NEW IN ('1656').

at org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:335) [teiid-engine-8.2.0.Final.jar:8.2.0.Final

how can we query the function which accepts table of objects as a parameter.

[shawkins]

> how can we query the function which accepts table of objects as a parameter.

When you call a procedure using a SELECT that is somewhat non-standard syntax - https://docs.jboss.org/author/display/TEIID/DML+Commands#DMLCommands-ProceduralRelationalCommand

One of the side effects of overloading the meaning of comparison to be a variable assignment is that non-comparable parameter assignments don't work.

You should instead use the string type for the parameter in your imported Teiid procedure metadata or use a standard Teiid procedure call:

CALL TEIID_USER_APP_ENT1(PCLIENTID=>'1656' ,PCLIENTID_NEW=>'1656')

[amitagrawal]

when I impoted metadata throgh Teiid, I also got an extra parameter "ParamWithNoName1" imported in my designer. I changed PCLIENTID_NEW paramter's type from object to String and removed extra parameter "ParamWithNoName1" from designer. finally, when I am querying below SQL and it still throws error.

select * from TEIID_USER_APP_ENT1 where PCLIENTID = '1656' and PCLIENTID_NEW in ( '1656', '9999' )

TEIID30020 Processing exception 'TEIID30504 MYSSDEVORACLE_table_type: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}' for request tsWGdVt5Ez2A.9.  Exception type org.teiid.core.TeiidProcessingException thrown from oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112).: org.teiid.core.TeiidProcessingException: TEIID30504 MYSSDEVORACLE_table_type: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:534) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:321) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:280) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:101) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:384) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:250) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]
at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]
Caused by: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:457) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:302) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
... 24 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 14:
PLS-00306: wrong number or types of arguments in call to 'TEIID_USER_APP_ENT1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:749)
at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:742)
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
... 27 more

15:52:40,058 DEBUG [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue32) Removing tuplesource for the request tsWGdVt5Ez2A.9
15:52:40,058 DEBUG [org.teiid.BUFFER_MGR] (Worker3_QueryProcessorQueue32) Removing TupleBuffer:
15:52:40,058 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue32) before rollback:org.teiid.dqp.internal.process.TransactionServerImpl@2ec7f1(tsWGdVt5Ez2A REQUEST ID:TransactionImple < ac, BasicAction: 0:ffff0a88a23b:-33ab01c0:521d0161:1d status: ActionStatus.RUNNING >)
15:52:40,074 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue32) after rollback : null
15:52:40,074 DEBUG [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue32) Sending error to client
15:52:40,074 DEBUG [org.teiid.TRANSPORT] (Worker3_QueryProcessorQueue32) send message: MessageHolder: key=1812 contents=ResultsMessage rowCount=0 finalRow=-1
15:52:40,074 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue32) before suspend:org.teiid.dqp.internal.process.TransactionServerImpl@2ec7f1(tsWGdVt5Ez2A REQUEST ID:TransactionImple < ac, BasicAction: 0:ffff0a88a23b:-33ab01c0:521d0161:1d status: ActionStatus.ABORTED >)
15:52:40,074 DEBUG [org.teiid.TXN_LOG] (Worker3_QueryProcessorQueue32) after suspend : null

[shawkins]

It's possible that Oracle doesn't like that your PCLIENTID_NEW parameter is being bound as a string/varchar.  Do you know how would you bind a value of your custom type using JDBC?

Another possibility is to leave the column typed as object, use the standard call syntax, and see if it oracle would let it be bound as a JAVA_OBJECT type.

Otherwise there is a more involved option to set the native-query extension metadata to use a literal value in the call instead.