I'm trying to write an Function in an Oracle database. When i'm in the function editor and attempt to run it, DbVis inserts an additional parameter.
@call [dbvis - v0] = SP_GET_ANNUAL_SALES_HISTORY( [dbvis - v1], 'DAL', '00105315', '2013' );
@echo returnValue = [dbvis - v0];
@echo p1 = [dbvis - v0];
Then I get this error:
... Physical database connection acquired for: JdaTest
10:36:40 [@CALL - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SP_GET_ANNUAL_SALES_HISTORY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
10:36:40 [@ECHO - 0 row(s), 0.000 secs] returnValue = null
10:36:40 [@ECHO - 0 row(s), 0.000 secs] p1 = null
... 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [2 successful, 0 warnings, 1 errors]
This is my function. It's my first foray into stored procedures. At this point, I'm just trying to getting it to run and delivery some results. hte Return type is a Type I also created. It's 'CREATE OR REPLACE TYPE "SAPMGR"."ANNUAL_SALES_HISTORY" is Varray(12) of number'
CREATE OR REPLACE FUNCTION "SAPMGR"."SP_GET_ANNUAL_SALES_HISTORY" (loc_in IN varchar2,item_in IN varchar2,year_in IN varchar2)
RETURN annual_sales_history
AS
yearStart Date;
yearEnd Date;
start_date sales_history.start_date%TYPE;
qty sales_history.quantity%TYPE;
ash annual_sales_history;
cursor c1 is
select start_date,QTY
from sales_history
where item = item_in
and loc = loc_in
and start_date between yearStart and yearEnd
order by item, loc, start_date;
BEGIN
Loop
fetch c1 into start_date, qty;
exit when c1%notfound;
ash(extract(month from start_date)) := qty;
DBMS_OUTPUT.PUT_LINE( 'ash' || ' ' || ash(0) || ' ' || ash(1) || ' ' || ash(2) || ' ' || ash(3) || ' ' || ash(4) || ' ' || ash(5)|| ' ' || ash(6) || ' ' || ash(7));
End loop
commit;
close c1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
RETURN ash;
END;
What is [dbvis - v1] and how do I get rid of it? Or, please show me where I'm leaving something out maybe
Thanks.
