1 2 3 Previous Next 38 Replies Latest reply on Sep 11, 2013 7:46 AM by Steven Hawkins

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

    Amit Agrawal Newbie

      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.

        • 1. Re: calling an oracle funtion part of the package from teiid returns error.
          Ramesh Reddy Master

          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.

          • 2. Re: calling an oracle funtion part of the package from teiid returns error.
            Steven Hawkins Master

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

            • 3. Re: calling an oracle funtion part of the package from teiid returns error.
              Amit Agrawal Newbie

              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

              • 4. Re: calling an oracle funtion part of the package from teiid returns error.
                Steven Hawkins Master

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

                • 5. Re: calling an oracle funtion part of the package from teiid returns error.
                  Amit Agrawal Newbie

                  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'

                  • 6. Re: calling an oracle funtion part of the package from teiid returns error.
                    Steven Hawkins Master

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

                    • 7. Re: calling an oracle funtion part of the package from teiid returns error.
                      Amit Agrawal Newbie

                      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;

                      • 8. Re: calling an oracle funtion part of the package from teiid returns error.
                        Steven Hawkins Master

                        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?

                        • 9. Re: calling an oracle funtion part of the package from teiid returns error.
                          Amit Agrawal Newbie

                          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.

                          • 10. Re: calling an oracle funtion part of the package from teiid returns error.
                            Steven Hawkins Master

                            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.

                            • 11. Re: calling an oracle funtion part of the package from teiid returns error.
                              Amit Agrawal Newbie

                              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.

                              • 12. Re: calling an oracle funtion part of the package from teiid returns error.
                                Steven Hawkins Master

                                > 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')

                                • 13. Re: calling an oracle funtion part of the package from teiid returns error.
                                  Amit Agrawal Newbie

                                  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

                                  • 14. Re: calling an oracle funtion part of the package from teiid returns error.
                                    Steven Hawkins Master

                                    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.

                                    1 2 3 Previous Next