Executing Stored Proc.

[IanWaterman]

I am trying to test a stored Proc used in a report.

The procedure has a date input parameter and a cursor result set as output

create or replace PROCEDURE "OLY_NOSTRO_PROJECTION"
(pStartDate IN DATE,
pResultSet OUT TYPES.cursorType)
as

Procedure runs perfectly OK in report and Crystal passes the following SQL to Oracle

{CALL "BFC_CRYSTAL"."OLY_NOSTRO_PROJECTION"({ts '2016-08-31 09:56:48'})}

This is not recognised in SQL developer

I have converted to

both CALL and EXEC

call OLY_NOSTRO_PROJECTION ('31-AUG-16');

exec OLY_NOSTRO_PROJECTION (to_date('2016/09/01', 'YYYY/MM/DD'));

But it fails to execute with errors
Error starting at line : 1 in command -
call OLY_NOSTRO_PROJECTION ('31-AUG-16')
Error report -
SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OLY_NOSTRO_PROJECTION'
06553. 00000 - "PLS-%s: %s"
*Cause:
*Action:
Error starting at line : 3 in command -
exec OLY_NOSTRO_PROJECTION (to_date('2016/09/01', 'YYYY/MM/DD'))
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OLY_NOSTRO_PROJECTION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

I have written SPs before but without Cursors and never had this problem. What am I doing wrong/missing.

Thank you

Ian

 

[IanWaterman]

Please disregard this I have worked out I need to run via PL/SQL which is a pain

Procedure detailed below
Can I eliminate use of Cursor by simply removing output parameter
ie

create or replace PROCEDURE "OLY_NOSTRO_PROJECTION"
(pStartDate IN DATE)

and replacing

DBMS_OUTPUT.PUT_LINE(plsql);
OPEN pResultSet for plsql;

with

Exec plsql

Ian

create or replace PROCEDURE "OLY_NOSTRO_PROJECTION"
(pStartDate IN DATE,
pResultSet OUT TYPES.cursorType)
as
BEGIN
DECLARE DAY1 VARCHAR2(10);
thisdate DATE;
type v_DAY is varray(7) of DATE;
array v_DAY := v_DAY();
j int;
plday integer;
plsql varchar2(1024) ;


begin
for i in 1..7 loop
array.extend();
j := i-1;
If i=1 then thisdate := pStartDate;
ELSE thisdate:=array(i-1)+1;
END IF;

IF TRIM(TO_CHAR(thisdate,'DAY'))='SATURDAY' OR TRIM(TO_CHAR(thisdate,'DAY'))='SUNDAY' THEN
thisdate := NEXT_DAY(thisdate,'MONDAY');
END IF;
array(i):= thisdate;
DAY1 := TO_CHAR(thisdate,'DAY');
DBMS_OUTPUT.PUT_LINE(DAY1);
END LOOP;

plsql := ' SELECT SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(1),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field1 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(2),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field2 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(3),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field3 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(4),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field4 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(5),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field5 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(6),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field6 ,
SUM(CASE WHEN TO_NUMBER(MVDTVA)<=' || TO_NUMBER(TO_CHAR(array(7),'YYYYMMDD'))|| ' THEN MVMONT ELSE 0 END) as Field7 ,
SUBSTR(cur.TBCOMP,1,3) as Currency
FROM UAT.FDBMVT mvt
LEFT JOIN UAT.FDBTAB cur on mvt.MVMON = cur.TBCODE and cur.TBID = ''040''
WHERE MVGRE=40
GROUP BY SUBSTR(cur.TBCOMP,1,3)
ORDER BY SUBSTR(cur.TBCOMP,1,3)';


DBMS_OUTPUT.PUT_LINE(plsql);

OPEN pResultSet for plsql;

END;
END