How to pass a parameter of type 'Nested Table' to a stored procedure in Oracle

1

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:
    
asked by anonymous 04.12.2015 / 15:30

0 answers