| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How can we call Stored Procedure in OracleDB with RefCursor |
« View previous topic :: View next topic » |
| Author |
Message
|
| ravi_ishere |
 Posted: Fri Feb 22, 2008 2:53 am Post subject: How can we call Stored Procedure in OracleDB with RefCursor |
 |
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
Hi All,
Anyone know how to call the a stored procedure in Oracle returning Ref Cursor as a set of values
create or replace PACKAGE BODY CONY_ERR_ACTN_TEST_PACK AS
Procedure:-
FUNCTION f_get_actions(
i_test_sl IN VARCHAR2,
i_test_type IN VARCHAR2,
i_test_desc IN VARCHAR2,
o_test_num OUT NUMBER,
o_testwait OUT VARCHAR2,
o_testcount OUT VARCHAR2,
o_testqmgr OUT VARCHAR2,
o_queue OUT VARCHAR2,
o_qmgr OUT VARCHAR2,
o_Test_comment OUT VARCHAR2,
o_err_cd OUT VARCHAR2,
o_err_msg OUT VARCHAR2) RETURN p_recordset AS temp_p_recordset TEST_PACK.p_recordset ;
END f_get_error_actions;
END TEST_PACK;
i am passing the 3 values and getting the values and Result set
i have tested the procedure it is working fine in SQL PLUS.
I have tried the below steps :
CREATE PROCEDURE TEST (IN var CHARACTER,IN var1 CHARACTER, IN var2 CHARACTER,OUT var3 INT,OUT var4 CHARACTER,OUT var5 CHARACTER,OUT var6 CHARACTER,OUT var7 CHARACTER, OUT var8 CHARACTER,OUT var9 CHARACTER,OUT var10 CHARACTER,OUT errormsg CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "TEST.TEST_PACK.f_get_actions";
CALL TEST (var,var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,var11,Environment.Varaibles.ResultSet[])
But i am getting the below exception:
CHARACTER Text = [DataDirect][ODBC Oracle driver][Oracle]ORA-06550: line 1, column 17: PLS-00382: expression is of wrong type ORA-06550: line 1, column 9: PL/SQL: Statement ignored
i have checkd the DB also it is fine.
Can anyone tell what exactly the issue.
Thanks,
R |
|
| Back to top |
|
 |
| Vitor |
| Posted: Fri Feb 22, 2008 3:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Can you return a result set into ESQL like that? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ravi_ishere |
| Posted: Fri Feb 22, 2008 3:13 am Post subject: |
|
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
I have tried as per the WBI Info center docs for v6
and sorry one correction:
Not like the below:
Environment.Varaibles.ResultSet[])
I ahve done like this:
Environment.ResultSet[]) |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 22, 2008 3:25 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ravi_ishere wrote: |
I have tried as per the WBI Info center docs for v6
|
Again I learn.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ravi_ishere |
| Posted: Fri Feb 22, 2008 3:43 am Post subject: |
|
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
i have changed the procedure like :
create or replace PACKAGE BODY CONY_ERR_ACTN_TEST_PACK AS
FUNCTION f_get_actions(
i_test_sl IN VARCHAR2,
i_test_type IN VARCHAR2,
i_test_desc IN VARCHAR2,
o_err_cd OUT VARCHAR2,
o_err_msg OUT VARCHAR2) RETURN p_recordset AS temp_p_recordset TEST_PACK.p_recordset ;
END f_get_error_actions;
END TEST_PACK;
CREATE PROCEDURE TEST (IN var CHARACTER,IN var1 CHARACTER, IN var2 CHARACTER,OUT var10 CHARACTER,OUT errormsg CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "TEST.TEST_PACK.f_get_actions";
CALL TEST (var,var1,var2,var10,var11,Environment.ResultSet[])
Tried calling the procedure but getting the same exception |
|
| Back to top |
|
|
| AJStar |
| Posted: Fri Feb 22, 2008 3:52 am Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
Have you defined "p_recordset" as a cursor in the stored proc? i.e., of type sys_refcursor ?
_________________
Regards
AJ |
|
| Back to top |
|
|
| AJStar |
| Posted: Fri Feb 22, 2008 3:58 am Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
The signature of your procedure definition and procedure call seems to be different.
Try this...
SET Environment.Variables.ResultSet[] = TEST (var,var1,var2,var10,var11);
_________________
Regards
AJ |
|
| Back to top |
|
|
| ravi_ishere |
| Posted: Fri Feb 22, 2008 4:17 am Post subject: |
|
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
[Have you defined "p_recordset" as a cursor in the stored proc? i.e., of type sys_refcursor ?]
Yes p_recordset to ref cursor
Here is the code:
create or replace TEST_PACK AS
TYPE p_recordset is REF CURSOR;
FUNCTION f_get_actions(
_test_sl IN VARCHAR2,
i_test_type IN VARCHAR2,
i_test_desc IN VARCHAR2,
o_err_cd OUT VARCHAR2,
o_err_msg OUT VARCHAR2) return p_recordset;
END TEST_PACK;;[/quote] |
|
| Back to top |
|
|
| AJStar |
| Posted: Fri Feb 22, 2008 4:34 am Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
I'm not clear with your stored proc definition... maybe bcos I'm not that well-versed in PL/SQL 
But I can give you an example of how it could be done.
Stored Proc definition in Oracle
| Code: |
create or replace procedure f_get_actions(test_sl IN VARCHAR2, i_test_type IN VARCHAR2, i_test_desc IN VARCHAR2, o_err_cd OUT VARCHAR2, o_err_msg OUT VARCHAR2, p_recordset out sys_refcursor)
is
**** your SP action ****
end;
|
In ESQL... the definition;
| Code: |
CREATE PROCEDURE TEST (IN var CHARACTER,IN var1 CHARACTER, IN var2 CHARACTER, OUT var10 CHARACTER, OUT errormsg CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "<schema>.f_get_actions";
|
And the call...
| Code: |
CALL TEST(var, var1, var2, var10, var11, Environment.Variables.Result.ResultSet[]) IN Database.{<schema>};
|
_________________
Regards
AJ |
|
| Back to top |
|
|
| ravi_ishere |
| Posted: Sat Feb 23, 2008 9:41 pm Post subject: |
|
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
| Yes, i have did the process what you mentioned but still un successful for calling the stored procedure. |
|
| Back to top |
|
|
| AJStar |
| Posted: Sun Feb 24, 2008 8:20 pm Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
If you have defined everything as per my previous post, I don't think there should be any problem.
Have you checked if your DSN has the "Procedure Returns Results" parameter set to true?
_________________
Regards
AJ |
|
| Back to top |
|
|
| ravi_ishere |
| Posted: Mon Feb 25, 2008 2:20 am Post subject: |
|
|
Apprentice
Joined: 20 Jan 2008
Posts: 29
|
No i do not done.
Will it give any issue? |
|
| Back to top |
|
|
| AJStar |
| Posted: Mon Feb 25, 2008 4:28 am Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
Yes, certainly.
So pls check that too.
_________________
Regards
AJ |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
