I'm working with Oracle for just over 2 months and need to pass a dataset to be used in the query parameter inside the procedure. I searched a lot on the net and found no solution to solve my problem, I always get the same error message - incorrect number of argument types in the call.
Following are the codes and the return of the execution of the proccedure:
Table Creation Code;
CREATE TABLE MARCO.NOTA_FISCAL ( NUMERO NUMBER(10,0) NOT NULL, DAT_EMISSAO DATE NOT NULL )TABLESPACE "DADOS";
Package Code:
CREATE OR REPLACE PACKAGE MARCO.PKG_CONTABIL AS TYPE T_CURSOR IS REF CURSOR; TYPE T_NUMBER_TABLE IS TABLE OF NUMBER(10,0); PROCEDURE SP_NOTA_FISCAL_BY_NUMERO_SEL (VNOTAS IN T_NUMBER_TABLE, VCURSOR OUT T_CURSOR); END PKG_CONTABIL; \ CREATE OR REPLACE PACKAGE BODY MARCO.PKG_CONTABIL AS PROCEDURE SP_NOTA_FISCAL_BY_NUMERO_SEL (VNOTAS IN T_NUMBER_TABLE, VCURSOR OUT T_CURSOR) IS BEGIN OPEN VCURSOR FOR SELECT A.NUMERO, A.DAT_EMISSAO FROM MARCO.NOTA_FISCAL A WHERE A.NUMERO IN (SELECT column_value FROM table(VNOTAS)); END SP_NOTA_FISCAL_BY_NUMERO_SEL; END PKG_CONTABIL;
Query code:
DECLARE TYPE T_NUMBER_TABLE IS TABLE OF NUMBER(10,0); TYPE T_RESULT IS REF CURSOR; notas T_NUMBER_TABLE := T_NUMBER_TABLE(32222,232322); res T_RESULT; c_numero NUMBER(10,0); c_data DATE; BEGIN MARCO.PKG_CONTABIL.SP_NOTA_FISCAL_BY_NUMERO_SEL(notas, res); LOOP FETCH res INTO c_numero, c_data; EXIT WHEN res%NOTFOUND; dbms_output.put_line(c_numero); END LOOP; END;
Query Return:
Relatório de erros - ORA-06550: linha 9, coluna 3: PLS-00306: número incorreto de tipos de argumentos na chamada para 'SP_NOTA_FISCAL_BY_NUMERO_SEL' ORA-06550: linha 9, coluna 3: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: