Click here to Skip to main content
15,891,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I'm working with Oracle just over two months, and i must pass a set of data to be used as a query parameter in the procedure. I searched a lot on the net, but no solution has been found to solve my problem.


Source Code used.


Table Creation:
SQL
CREATE TABLE MARCO.NOTA_FISCAL (
  NUMERO NUMBER(10,0) NOT NULL, 
  DAT_EMISSAO DATE NOT NULL  
)TABLESPACE "DADOS";

Package Code>
SQL
CREATE OR REPLACE PACKAGE MARCO.PKG_CONTABIL AS
  TYPE T_NUMBER_TABLE IS TABLE OF NUMBER(10,0);

  PROCEDURE SP_NOTA_FISCAL_BY_NUMERO_SEL
    (VNOTAS IN T_NUMBER_TABLE,
    VCURSOR OUT SYS_REFCURSOR);

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 SYS_REFCURSOR) 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:
SQL
DECLARE
  notas MARCO.PKG_CONTABIL.T_NUMBER_TABLE := MARCO.PKG_CONTABIL.T_NUMBER_TABLE(32222,232322);
  res SYS_REFCURSOR;
  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;

Error Message:
C#
Relatório de erros -
ORA-21700: o objeto não existe ou está marcado para deleção
ORA-06512: em "MARCO.PKG_CONTABIL", line 6
ORA-06512: em line 9
21700. 00000 -  "object does not exist or is marked for delete"
*Cause:    User attempted to perform an inappropriate operation to
           an object that is non-existent or marked for delete.
           Operations such as pinning, deleting and updating cannot be
           applied to an object that is non-existent or marked for delete.
*Action:   User needs to re-initialize the reference to reference an
           existent object or the user needs to unmark the object.
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900