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:
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_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:
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:
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.