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

Asked

Viewed 497 times

1

I have been working with Oracle for a little more than 2 months and I need to pass a data set to be used in the query parameter within the process. I searched a lot on the net and did not find any solution to solve my problem, I always get the same error message - incorrect number of argument types in the call.

Follow the codes and the return of the execution:

Code for Table Creation;

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;

Code of consultation:

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;

Return of the Consultation:

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:
  • its procedure SP_NOTA_FISCAL_BY_NUMERO_SEL not just waiting for a parameter VNOTAS IN T_NUMBER_TABLE ? the other is not return VCURSOR OUT ?

  • Yes. An input parameter and an output parameter (cursor).

  • Tried to declare table as package type ?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.