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

[teiid123]

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

[rareddy]

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

[shawkins]

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

[teiid123]

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

[shawkins]

> 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?

[teiid123]

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.

[shawkins]

> 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?

[teiid123]

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.

[shawkins]

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.