8 Replies Latest reply on Aug 27, 2013 12:29 PM by Steven Hawkins

    How to call oracle stored procdure from teiid which returns ref cursor

    lawrence jesu Newbie

      Hi,

           How to call oracle sroed procedure which has one input peram and returns ref cursor results. here is my proc.

       

      procedure      user_app_entitlements_proc (

          pClientId           IN VARCHAR2  ,

          userAppEntitlements OUT sys_refcursor  

          ) as

       

       

       

      I have created source as in Teiid desiner as it is in the attachment. I appreciate your help.

       

      Thanks

      Lawrence

        • 1. Re: How to call oracle stored procdure from teiid which returns ref cursor
          Ramesh Reddy Master

          In Designer, right click ->designer -> preview (or click the running man tool bar button) while you select the procedure.

           

          If you are using JDBC client then you can use "Callable statement" {? = call user_app_entitlements_proc(?) }

           

          If you are using SquirreL client or Data Tools Explorer in the Eclipse call "execute user_app_entitlements_proc('xxxx')"

          • 2. Re: How to call oracle stored procdure from teiid which returns ref cursor
            Steven Hawkins Master

            The callable statement shown above won't quite work as Teiid treats return parameters and result sets differently.  So while the source procedure returns a ref cursor you would model it in Teiid as having a result set.  Thus the escaped callable syntax isn't really needed and you can just use "call user_app_entitlements_proc(?)" - https://docs.jboss.org/author/display/TEIID/DML+Commands

            • 3. Re: How to call oracle stored procdure from teiid which returns ref cursor
              lawrence jesu Newbie

              Hi,

                I don't understand clearly. I have requirement to call Oracle Stored procedure using Teiid design wizard in Eclipse.

              let me explain my steps what i did.

                      1. Clicked "Create Source Model for JDBC Data Source"

                      2. Connected Database

                      3. Checked "Procedure check box"

                      4. My Oracle DB listed bunch of stored procedure . I selected one of proce "user_app_ent_prce()" which has one string param and ref cur results.

                      5. Clicked finish button and now my Teiid explorer changed as in the attachment.

                      6. Now how to test my procedure is working? where to add this command execu or call proce  etccc?

                      7. If it is table i just go to Squirel client and will test select * from mytable name.

                      8. In the same  way ,i am trying for procedure but i get error.

               

              Please tell me step by step to call  oracle procedure from Teiid model explorer( both design step and coding and testing step in squirel).

              Oracle table is easy for me. Even oracle function works if i follow the above steps. but procedure doesn't works.

               

                     I might do something wrong, please clearly give me with example from starting to end.

               

               

              Thanks

              Lawrence

              • 4. Re: How to call oracle stored procdure from teiid which returns ref cursor
                Steven Hawkins Master

                > Now how to test my procedure is working? where to add this command execu or call proce  etccc?

                 

                From SQuirreL or the Designer preview/execution view you can issue "CALL user_app_entitlements_proc('xxxx')"

                 

                > In the same  way ,i am trying for procedure but i get error.

                 

                Can you elaborate?  What Teiid sql did you use?  What was your exception?

                • 5. Re: How to call oracle stored procdure from teiid which returns ref cursor
                  lawrence jesu Newbie

                  aused by: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): { ?= call "MSS4"."USER_APP_ENTITLEMENTS_PROC"(?)}

                  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$1.call(DataTierTupleSource.java:177) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]

                  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:174) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]

                  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_45]

                  at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_45]

                  at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]

                  ... 6 more

                  Caused by: java.sql.SQLException: ORA-06550: line 1, column 14:

                  PLS-00306: wrong number or types of arguments in call to 'USER_APP_ENTITLEMENTS_PROC'

                  ORA-06550: line 1, column 7:

                  PL/SQL: Statement ignored

                   

                   

                   

                   

                  look my procedure has 2 peram. the second peram is OUT peram. i call this from squirrel as you said. but it throws exception.

                  • 6. Re: How to call oracle stored procdure from teiid which returns ref cursor
                    Steven Hawkins Master

                    > look my procedure has 2 peram. the second peram is OUT peram.

                     

                    Yes, and with that last exception things are a little clearer.  Currently the oracle logic in Teiid assumes that result sets are provided by a return parameter and not thus use the sql "? = call ...".  Can you change your stored function (or add one) to return a cursor rather than using an out parameter?

                    • 7. Re: How to call oracle stored procdure from teiid which returns ref cursor
                      lawrence jesu Newbie

                      create or replace

                      PROCEDURE my_test_proc (

                          p_job           VARCHAR2,

                          p_emp_refcur    IN OUT SYS_REFCURSOR

                      )

                      IS

                      BEGIN

                          OPEN p_emp_refcur FOR SELECT mss_client.client_id FROM mss_client where client_id>100000;

                      END;

                       

                       

                      I changed my procedure IN OUT SYS_REFCURSOR. Still i get the same error. wrong number of arguments.

                      • 8. Re: How to call oracle stored procdure from teiid which returns ref cursor
                        Steven Hawkins Master

                        I'm saying that we currently understand how to handle a returned ref cursor - http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551171813078805685

                         

                        create or replace

                        FUNCTION my_test_proc (

                            p_job           VARCHAR2

                        )

                        RETURN types.cursortype

                        AS

                            p_emp_refcur    types.cursorType;

                        BEGIN

                            OPEN p_emp_refcur FOR SELECT mss_client.client_id FROM mss_client where client_id>100000;

                            RETURN p_emp_refcur;

                        END;

                         

                        It would take an enhancement to understand how to handle an out parameter ref cursor as the result set.