How to execute stored procedure with one input and many output parameters

[urosdigital]

Hello,

If i have the following stored procedure:

  PROCEDURE DisplaySim(p_msisdn   IN VARCHAR2,
                           p_imsi     OUT VARCHAR2,
                       	   p_iccid    OUT VARCHAR2,
                       	   p_status OUT VARCHAR2);

I can execute it in sql developer like this:

set serveroutput on;
DECLARE
	v_msisdn           	VARCHAR2(30);
	v_imsi           	VARCHAR2(30);
	v_iccid          VARCHAR2(30);
	v_status  VARCHAR2(30);
BEGIN
	v_msisdn := '1234567890';
	
	main_user.user_who_can_execute_procedure.DisplayLastSim( 
        p_msisdn            => v_msisdn,
		p_imsi               => v_imsi,           
		p_iccid           => v_iccid,    
		p_status   => v_status
	);
	DBMS_OUTPUT.PUT_LINE('Displaying data for MSISDN');
	DBMS_OUTPUT.PUT_LINE('MSISDN: ' || v_msisdn);
	DBMS_OUTPUT.PUT_LINE('IMSI: ' || v_imsi);
        DBMS_OUTPUT.PUT_LINE('ICCID: ' || v_iccid);
	DBMS_OUTPUT.PUT_LINE('status: ' || v_status);
        DBMS_OUTPUT.PUT_LINE('');
END;

I have checked docs for stored procedures -> https://python-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html#pl-sql-stored-procedures and there is a code sample as shown below:

out_val = cursor.var(int)
cursor.callproc('myproc', [123, out_val])
print(out_val.getvalue())        # will print 246

Based on my procedure i tried the following code:

            out_val = cursor.var(str)
            cursor.callproc('main_user.user_who_can_execute_procedure.DisplaySim', ['1234567890', out_val])
            print(out_val.getvalue())

It gives me the following error:

code: 6550
full code: ORA-06550
error message: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'DISPLAYSIM'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This is my firs time dealing with stored procedures, so far i have been dealing with stored function and this is the first time i have one input param and many output params. How should i execute this stored procedure?
Can someone show me the way when using callproc() and also the way of using Anonymous block.

BR,
Uros

[urosdigital]

OK solved in the meantime with:

            input_msisdn = '1234567890'
            imsi = cursor.var(str)
            iccid = cursor.var(str)
            status = cursor.var(str)
            cursor.callproc('main_user.user_who_can_execute_procedure.DisplaySim', [input_msisdn, imsi, iccid, status])
            out_imsi = imsi.getvalue()
            out_iccid = iccid.getvalue()
            out_status= status.getvalue()
            print(f'Simcard: {out_iccid} with imsi: {out_imsi} has {out_status} age status for msisdn: {input_msisdn}')

I would appreciate if someone can show the way of executing this procedure in case i have to call it many times for different input msisdns.:

['1234567890', '9876543210', '9988776655']

BR,
Uros

[anthony-tuininga]

Creating variables is the correct way of handling output parameters. You can reuse the variables as many times as you like. Each time you call the procedure new values will be populated. You can call multiple times as follows:

imsi = cursor.var(str)
iccid = cursor.var(str)
status = cursor.var(str)
for msisdn in ['1234567890', '9876543210', '9988776655']:
    cursor.callproc('main_user.user_who_can_execute_procedure.DisplaySim', [msisdn, imsi, iccid, status])
    out_imsi = imsi.getvalue()
    out_iccid = iccid.getvalue()
    out_status= status.getvalue()
    print(f'Simcard: {out_iccid} with imsi: {out_imsi} has {out_status} age status for msisdn: {msisdn}')

If you know you are going to be calling many of them you can also use executemany() instead, as in:

data = [['1234567890'], ['9876543210'], ['9988776655']]
imsi = cursor.var(str, arraysize=len(data))
iccid = cursor.var(str, arraysize=len(data))
status = cursor.var(str, arraysize=len(data))
cursor.setinputsizes(None, imsi, iccid, status)
cursor.executemany("begin main_user.user_who_can_execute_procedure.DisplaySim(:1, :2, :3, :4); end;", data)
for i, row in enumerate(data):
    msisdn = row[0]
    out_imsi = imsi.getvalue(i)
    out_iccid = iccid.getvalue(i)
    out_status= status.getvalue(i)
    print(f'Simcard: {out_iccid} with imsi: {out_imsi} has {out_status} age status for msisdn: {msisdn}')

Using executemany() will reduce the number of round trips to 1, which can significantly improve performance if you need to perform many executions.

[urosdigital]

Hello @anthony-tuininga

Thanks a million! :)

BR,
Uros