Skip to Content
0
Former Member
Jul 15, 2015 at 02:42 PM

Issue in Stored procedure

149 Views


Hi,

I have created a stored procedure, which was compiled successfully but it is throwing an error message while executing in Toad 11g as given below

ORA-06550: line 2, column 1:

PLS-00306: wrong number or types of arguments in call to 'MIS'

ORA-06550: line 2, column 1:

PL/SQL: Statement ignored

Could some one help me to amend this query.

CREATE OR REPLACE PACKAGE LMHBATCH.MIS_Package

AS

Type Test_type IS REF CURSOR;

CURSOR;

END MIS_package;

/

CREATE OR REPLACE PROCEDURE LMHBATCH.MIS

(Test_Cursor IN OUT MIS_PACKAGE.TEST_TYPE,

supplier IN OUT Redemption.rdm_c_creation_user%type)

as

BEGIN

OPEN Test_Cursor FOR

SELECT distinct rdm_c_creation_user supplier

FROM LMHBATCH.redemption r

where rdm_c_creation_user NOT IN ('BP','ASK')

and TRIM(rdm_c_creation_user) IN

((SELECT DISTINCT (rfr_v_supplier_id)

FROM LMHBATCH.rtr_funct_role)

)

MINUS

SELECT * FROM

(SELECT rdm_c_creation_user supplier

FROM LMHBATCH.redemption

WHERE

rdm_d_creation_date_time >= TRUNC (SYSDATE-1)

AND rdm_d_creation_date_time < TRUNC (SYSDATE)

AND TRIM(rdm_c_creation_user) IN

((SELECT DISTINCT (rfr_v_supplier_id)

FROM LMHBATCH.rtr_funct_role)

)

UNION

SELECT rdm_c_creation_user supplier

FROM LMHBATCH.redemption

WHERE

rdm_d_creation_date_time >= TRUNC (SYSDATE-1)

AND rdm_d_creation_date_time < TRUNC (SYSDATE)

AND TRIM(rdm_c_creation_user) = 'ARGOSREC'

);

loop

fetch Test_Cursor into supplier;

EXIT WHEN Test_Cursor%notfound;

dbms_output.put_line(supplier);

end loop;

close Test_Cursor;

END MIS;

/

I execute the stored proc like below

begin

MIS;

end;

/