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

Issue in Stored procedure

21 Views

Hi,

I am getting an error message while trying to execute the below stored proc, could someone help me to amend this query.

[Error] PLS-00403 (38: 26): PLS-00403: expression 'V_COUNT' cannot be used as an INTO-target of a SELECT/FETCH statement

[Error] PLS-00306 (40: 24): PLS-00306: wrong number or types of arguments in call to '||'

CREATE OR REPLACE PACKAGE LMHBATCH.MIS_Package

AS Type Test_type IS REF CURSOR;

END MIS_package;

/

CREATE OR REPLACE PROCEDURE LMHBATCH.MIS1

(Test_Cursor IN OUT MIS_PACKAGE.TEST_TYPE,

v_c_user Redemptions.rdm_c_creation_user%type,

v_count Redemption%ROWTYPE

)

as

BEGIN

OPEN Test_Cursor FOR

select cnt,supplier from

(SELECT COUNT (1) cnt,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)

)

GROUP BY rdm_c_creation_user

UNION

SELECT COUNT (1) cnt, 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'

GROUP BY rdm_c_creation_user

UNION

SELECT COUNT (1) cnt,decode(rdm_c_creation_user,'B_RDMNTRCK','RDMNTRCK_SSL','RDMNTRCK_SSL') supplier

FROM LMHBATCH.redemption

WHERE

trunc(rdm_d_creation_date_time) = TRUNC (SYSDATE)

and TRIM(rdm_c_creation_user) ='B_RDMNTRCK'

group by decode(rdm_c_creation_user,'B_RDMNTRCK','RDMNTRCK_SSL','RDMNTRCK_SSL')

order by 2);

loop

fetch Test_Cursor into v_count, v_c_user;

EXIT WHEN Test_Cursor%notfound;

dbms_output.put_line(v_count||v_c_user);

end loop;

close Test_Cursor;

END MIS1;

/

Regards,

Sam