INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Executing Stored Proc.

Executing Stored Proc.

(OP)
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

RE: Executing Stored Proc.

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close