-
1. Re: How to call oracle stored procdure from teiid which returns ref cursor
rareddy Aug 26, 2013 4:51 PM (in response to teiid123)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
shawkins Aug 26, 2013 6:53 PM (in response to rareddy)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
teiid123 Aug 27, 2013 9:06 AM (in response to shawkins)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
shawkins Aug 27, 2013 9:18 AM (in response to teiid123)> 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
teiid123 Aug 27, 2013 10:40 AM (in response to 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.
-
6. Re: How to call oracle stored procdure from teiid which returns ref cursor
shawkins Aug 27, 2013 10:55 AM (in response to teiid123)> 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
teiid123 Aug 27, 2013 12:13 PM (in response to shawkins)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
shawkins Aug 27, 2013 12:29 PM (in response to teiid123)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.