on 10-14-2009 5:46 PM
Hi All
Can you please help me with the syntax for calling SP in MII ( and mode...) ..etc.
I have tried lot of ways from different thread ..but no luck.
the SP code i have created and executed is ..
create or replace procedure test_proc
(arg_part IN VARCHAR2,
arg_text OUT VARCHAR2,
arg_qty1 IN NUMBER,
arg_qty2 IN OUT NUMBER)
IS
BEGIN
arg_text := UPPER(arg_part);
arg_qty2 := arg_qty1 * arg_qty2;
END;
And I've tested it as follows:
DECLARE
v_part VARCHAR2(50);
v_text VARCHAR2(50);
v_qty1 NUMBER;
v_qty2 NUMBER;
BEGIN
v_part := 'This is a test.';
v_qty1 := 5;
v_qty2 := 8;
test_proc(v_part, v_text, v_qty1, v_qty2);
DBMS_OUTPUT.PUT_LINE(v_text || ' ' || v_qty2);
END;
Thanks in advance.
Hari
Hari,
Go through the below threads these will guide you :
Also Go throug below help link:
http://help.sap.com/saphelp_xmii115/helpdata/en/Connectors/IDBCConnector.htm
-Suresh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Hari,
There are few ways to send back SP data out
Here is the one which is quite simple
1) Declare a Reference Cursor in some Package for re-usability and also suitable for holding any table data
create or replace PACKAGE GLOBALPKG AS
TYPE CUR IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END GLOBALPKG;
2) As you said you would like to input 4 values and output 8 values, Lets assume your SP looks like this then here is the solution
create or replace procedure Test_Proc
(
inval1 in integer,
inval2 in integer,
inval3 in integer,
inval4 in integer,
outcursor out GLOBALPKG.CUR -- Out Put cursor variable
)
as
outval1 integer;
outval2 integer;
outval3 integer;
outval4 integer;
outval5 integer;
outval6 integer;
outval7 integer;
outval8 integer;
begin
--User Business Logic which will provide values to all 8 variables
-- send all 8 values out through cursor
Open outcursor for
select outval1,outval2,outval3,outval4,outval5,outval6,outval7,outval8 from dual;
end;
Please note declaration of output cursor variable and how it is thrown out
3) Calling SP in MII
*
CALL Test_Proc(:OutputVar)
*
No semicolon at the end.
Hope this helps!!
Regards,
Adarsh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Adarsh -
Thanks for your reply.
Is in that calling syntax should be
CALL Test_Proc(inp1,inpp2,inp3,inp4,?)
for above procdure which you written.
I have created one sp with your syntax and called successfully from MII with abouve calling syntax from MII.
I have also couple questions;
All the posts in sdn are so confusing ..enough to blow some body's head
any way..my question are..
Whats the deal with calling with belwo syntax
proc('http:param.1')
and
proc( [Param.1],[Param.2],:x)....
Thanks
Hari
Hi Hari,
Sorry I missed out to show the input variables in the syntax in the 3rd step
Heres' the right one
CALL Test_Proc('[Param.1]','[Param.2]','[Param.3]','[Param.4]',:OutVar)
The mode should be FixedQueryWithOutput
In MS SQL SP you can declare output variables in query template and use them to hold the out put values from SP without any cursor.
But for Oracle SP you cannot declare openly the variables in Query template and use them for holding SP output values but you can use a cursor to throw a output from Oracle SP and use it directly in query template without need to declare it.
I have tried many different techniques to declare variables in Query template to hold output values instead of cursor but never been successful so far.
I hope I have cleared your confusion.
Regards,
Adarsh
Thanks Adarsh.
It did work for me for both of the below syntaxes
CALL Test_Proc('[Param.1]','[Param.2]','[Param.3]','[Param.4]',:OutVar)
CALL Test_Proc('[Param.1]','[Param.2]','[Param.3]','[Param.4]',?)
And Thanks for your insight explanation of diff between calling MS Sql SP and Orace SP.
Thanks
Hari
Edited by: Hari on Oct 15, 2009 9:40 AM
And Every body,please careful while posting code...and put code in proper markup tags..since here for example..
if write
[Param.1]
with out tags..it would be transoforemd as http://param.1 .
I figured out syntax.
Declare
TYPE cursortype is ref cursor;
mycursor cursortype;
v1 NUMBER;
v2 NUMBER;
v3 NUMBER;
v4 NUMBER(10);
v5 NUMBER(10);
v6 NUMBER;
BEGIN
pkg1.getmydata('1234','17','10','50',mycursor);
fetch mycursor
into
v1,
v2 ,
v3 ,
v4 ,
v5 ,
v6 ;
dbms_output.put_line(v1,v2,v3,v4,v5,v6);
close mycursor;
END;
/
Any recommedation like how to write SP in oracle which can be called in MII easily.
I have 4 input parameters and 8 output parameters.
Thanks
Hari
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
7 | |
3 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.