Error PLS-00306

[angelana22]

Using CR Version 9, Oracle, stored procedure as datasource, MS OLE DB Provider for Oracle as connectivity.

My stored procedure is using:

TYPE cursor_type IS REF CURSOR RETURN temp_table%ROWTYPE;
With 2 parameters company_id as number and user_id as varchar2. Both parameters were declared as IN.

Calling the procedure in report using only company_id as parameter works but when I added user_id I got an error.

I am using the parameters as part of the select statement in WHERE clause.

Is there anyone here that knows why I am encountering the "PLS-00306: wrong number or types of arguments in call to procedure" error?

Thanks!

 

[Turkbear]

Hi,
To be sure what is happening, we need to see the SP..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[angelana22]

This is the SP:

Spec:
REATE OR REPLACE PACKAGE LL.Test
IS
TYPE cursor_type1 IS REF CURSOR RETURN avwf_daily_time_temp%ROWTYPE;
Procedure DailyTime(pOutput IN OUT cursor_type1,CO in NUMBER,WORKDT IN DATE, USR in VARCHAR2);
END Test;

Body:
CREATE OR REPLACE PACKAGE BODY LL.Test
IS
Procedure DailyTime(pOutput IN OUT cursor_type1,CO in NUMBER,WORKDT IN DATE, USR in VARCHAR2)
IS
BEGIN
OPEN pOutput FOR
SELECT
A.EMP_NO, A.FUL_NM, A.STA_CD, A.GRP_CD,A.DPT_CD,
DC.R_NAME, A.CO_NO,DD.LD5 WRK_DPT_CD,
DD.WORK_DT WRK_DT, DD.HOURS HRS_NO,
DD.PAY_CODE PAY_CD, DD.LD1 SFT_CD,
DD.LD2 WO,DD.LD3 TASK, DD.LD6 WRK_ACC_CD,
DD.LD4 PRJ_NO, DD.COMMENTS NOT_TX,
TSD.OTHER_BOOLEAN2,
CASE TSD.OTHER_BOOLEAN2
WHEN 'T' THEN 'OT_COMP'
ELSE (CASE G.ATTEND_CLASS
WHEN 'TS_REG_HRS' THEN 'STRAIGHT_TIME'
ELSE 'OT_PAID'
END)
END COMP, F.LONG_DESCRIPTION PAY_NM,
G.ATTEND_CLASS PAY_CAT_CD , H.USR_ID
FROM
LV_LAW.WF_EMPLOYEE_VW A

INNER JOIN
LAWSON.DEPTCODE DC
ON TRIM(A.DPT_CD) = TRIM(DC.DEPARTMENT)
AND A.CO_NO = DC.COMPANY

INNER JOIN
LV_LAW.WF_PAYROLL_VW B
ON A.EMP_ID = B.EMP_ID AND
B.VER_ID IN (SELECT MAX_VER_ID FROM
LV_LAW.WF_MAXPERIOD_VW)

INNER JOIN
WFTE.TIME_SHEET_OUTPUT DD
ON B.VER_ID = DD.EMPLOYEE_PERIOD_VERSION

INNER JOIN
WFTE.TIME_SHEET_DETAIL TSD
ON TSD.EMPLOYEE_PERIOD_VERSION =
DD.EMPLOYEE_PERIOD_VERSION AND
DD.WORK_DT = TSD.WORK_DT AND
DD.PAY_CODE = TSD.PAY_CODE

INNER JOIN
WFTE.PAY_CODE F
ON DD.PAY_CODE = F.PAY_CODE

LEFT OUTER JOIN
LAWSON.ATCLASS G
ON A.CO_NO = G.COMPANY AND
DD.PAY_CODE = G.ATTEND_CODE AND
G.ATTEND_CLASS LIKE 'TS_%'

INNER JOIN
LV_LAW.LW_REPORT_SECURITY_VW H
ON A.CO_NO = H.CO_NO AND
A.EMP_NO = H.EMP_NO

INNER JOIN
LV_LAW.HR_DEPT_AUX I
ON A.CO_NO=I.COMPANY AND
A.DPT_CD = I.LEVEL_ID AND
I.WFTE_GO_LIVE_STATUS='P'

WHERE
A.CO_NO= CO AND
DD.WORK_DT = WORKDT AND
H.USR_ID = USR AND
F.LONG_DESCRIPTION <> '-' AND
DD.PAY_CODE NOT IN ('AF','AS','AX','BC','CC','CF', 'CS','CX','DF','DX','PC','PX');
END;
END Test;

--
I am not sure if its worth mentioning but, I noticed that the work_dt parameter when called in Crystal report is datetime type and not Date. I thought that that was the one that is giving me an error so I removed it leaving CO and USR as parameters. Same error. I removed USR and CO paramater worked.

Thanks!

/

 

[Turkbear]

Hi,
you have these:

pOutput IN OUT cursor_type1,
CO in NUMBER,
WORKDT IN DATE,
USR in VARCHAR2

so,
4 values must be passed to the proc ..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[angelana22]

I was reading online how to use SP in CR and it says that I should have an IN OUT cursor for the report to work which I have proven combining that parameter with another parameter CO. It worked.

And I already have one report in production thats using SP that has IN OUT cursor param. So I am not sure if thats the problem...

Thanks!

 

[angelana22]

Still working on this report... is there anyone here who has an idea why my report is giving me an error when I add more stored procedure parameters?

I wonder why I am getting an error when the SP declation below was combined with more than one parameter.

Procedure DailyTime(pOutput IN OUT cursor_type1);

Example:
The following works:

1. Procedure DailyTime(pOutput IN OUT cursor_type1,CO in NUMBER);

2. Procedure DailyTime(pOutput IN OUT cursor_type1,WORKDT IN DATE);

3. Procedure DailyTime(pOutput IN OUT cursor_type1,USR in VARCHAR2);

Any additional param added after any the second param fails.

Thanks!