cancel
Showing results for 
Search instead for 
Did you mean: 

Executing oracle SP in MII

HariCS23
Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hari,

Go through the below threads these will guide you :

[Thread2|]

[Thread|]

[Thread3|;

Also Go throug below help link:

http://help.sap.com/saphelp_xmii115/helpdata/en/Connectors/IDBCConnector.htm

-Suresh

Answers (2)

Answers (2)

sidnooradarsh
Contributor
0 Kudos

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

HariCS23
Contributor
0 Kudos

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

sidnooradarsh
Contributor
0 Kudos

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

HariCS23
Contributor
0 Kudos

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

HariCS23
Contributor
0 Kudos

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 .

HariCS23
Contributor
0 Kudos

And..any body know ..what is syntax to call that sp from oracle toad or sql * plus.

Thanks

Hari

HariCS23
Contributor
0 Kudos

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;

/

former_member201407
Participant
0 Kudos

Awesome Hari.

So we hopefully will get the new SP ready for our next action for Lots to release and Rec, Start in all reports

Regards

Som

HariCS23
Contributor
0 Kudos

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

HariCS23
Contributor
0 Kudos

And i am using ojdbc14.jar driver and my back end data base is oracle 10.2.0.4 .