Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle error PLS-00905 invalid object

Re: Oracle error PLS-00905 invalid object

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 27 May 2004 07:52:39 -0700
Message-ID: <1085669566.490112@yasure>


Alex Paris wrote:

> I don't understand because I continue to receive this errors where is
> it wrong?
> Thanks.
>
> PS: I use Oracle 9i.
>
>
> CREATE OR REPLACE TYPE TP_DBG_ART_SPEC AS OBJECT (
> ID RO_ATTR_ART_SPEC.ID%TYPE,
> COD_SPEC RO_ATTR_ART_SPEC.COD_SPEC%TYPE,
> DESCR RO_TB_ATTREZ_SPEC.DESCRIZIONE%TYPE,
> ID_FIELD RO_ATTR_ART_SPEC.ID_FIELD%TYPE,
> SPEC_DESCR RO_ATTR_FIELD.DESCR%TYPE,
> VALORE RO_STO_ART_SPEC.VALORE%TYPE,
> F_TIPO RO_STO_ART_SPEC.F_TIPO%TYPE,
> SEQ_PRESENTAZ RO_STO_ART_SPEC.SEQ_PRESENTAZ%TYPE);
> /
>
> CREATE OR REPLACE TYPE TP_TBL_ART_SPEC AS TABLE OF TP_DBG_ART_SPEC;
> /
>
> CREATE OR REPLACE PACKAGE DBGES AS
> TYPE RC IS REF CURSOR;
>
> PROCEDURE RO_P_GET_ART_MACCH_SPEC( p_date IN NUMBER,
> p_dbgart IN CHAR,
> p_f_bicol IN CHAR,
> p_f_conf IN CHAR,
> p_mcosc IN CHAR,
> p_cod_macch IN CHAR,
> p_Rset IN OUT RC);
> END DBGES;
> /
>
> --
> --
>
> CREATE OR REPLACE PACKAGE BODY DBGES AS
> -- ------------------------------------------------------
> -- DEFINIZIONE PUBLICA DELLE VARIABILI
> -- ------------------------------------------------------
> --VARIABILI
>
>
> -- =========================================================================
> -- =========================================================================
> PROCEDURE RO_P_GET_ART_MACCH_SPEC( p_date IN NUMBER,
> p_dbgart IN CHAR,
> p_f_bicol IN CHAR,
> p_f_conf IN CHAR,
> p_mcosc IN CHAR,
> p_cod_macch IN CHAR,
> p_Rset IN OUT RC) AS
>
> v_Rset TP_TBL_ART_SPEC := TP_TBL_ART_SPEC() ;
>
> v_id RO_ATTR_ART_SPEC.ID%TYPE;
> v_cod_spec RO_ATTR_ART_SPEC.COD_SPEC%TYPE;
> v_descrizione RO_TB_ATTREZ_SPEC.DESCRIZIONE%TYPE;
> v_id_field RO_ATTR_ART_SPEC.ID_FIELD%TYPE;
> v_descr RO_ATTR_FIELD.DESCR%TYPE;
>
> v_cnt NUMBER;
> v_valore RO_STO_ART_SPEC.VALORE%TYPE;
> v_f_tipo RO_STO_ART_SPEC.F_TIPO%TYPE;
> v_seq_presentaz RO_STO_ART_SPEC.SEQ_PRESENTAZ%TYPE;
>
> --CURSORI
> CURSOR c_attr_art IS
> SELECT A.ID, A.COD_SPEC, B.DESCRIZIONE, A.ID_FIELD, C.DESCR
> FROM RO_ATTR_ART_SPEC A, RO_TB_ATTREZ_SPEC B, RO_ATTR_FIELD C
> WHERE C.ID = A.ID_FIELD AND C.SOCIETA = A.SOCIETA
> AND B.COD_SPEC = A.COD_SPEC AND B.SOCIETA = A.SOCIETA
> AND A.DBGART = p_DbgArt AND A.F_CONF = P_f_conf
> AND A.F_BICOL = p_f_bicol AND A.COD_MACCH = p_Cod_Macch
> AND A.SOCIETA = p_mcosc ORDER BY A.COD_SPEC, A.ID_FIELD;
>
> CURSOR c_valore (p_id NUMBER) IS
> SELECT A.VALORE, A.F_TIPO, A.SEQ_PRESENTAZ
> FROM RO_STO_ART_SPEC A, RO_STO_T B
> WHERE B.ID = A.ID_TESTATA AND A.ID_ART_SPEC = P_id
> AND A.F_COSA <> 'D' AND B.DATA_DEC <= p_date
> ORDER BY B.ID DESC;
>
>
>
> BEGIN
> OPEN c_attr_art;
> v_cnt:= 0;
> LOOP
> FETCH c_attr_art INTO v_id, v_cod_spec, v_descrizione, v_id_field,
> v_descr;
> EXIT WHEN c_attr_art%NOTFOUND;
>
> -- Recupero il valore corretto per il giorno assegnato
> v_valore := ' ';
> v_f_tipo := 1;
> v_seq_presentaz := 99;
> OPEN c_valore ( v_id );
> FETCH c_valore INTO v_valore, v_f_tipo, v_seq_presentaz;
> CLOSE c_valore;
>
> -- Inserisco il nuovo record
> v_cnt := v_cnt + 1;
> v_rset(v_cnt) := TP_DBG_ART_SPEC(v_ID, v_COD_SPEC, v_descrizione,
> v_ID_FIELD, v_DESCR,
> v_valore, v_f_tipo, v_seq_presentaz );
>
> END LOOP;
> CLOSE c_ATTR_art;
> IF v_cnt > 0 THEN
> OPEN p_rset for select * from the ( select cast (v_rset as
> TP_TBL_ART_SPEC) from dual);
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> END;
> END DBGES;
> /

Don't have time to figure out why it isn't working right now ... but I do wonder about the following:

  1. Why a rollback in the exception clause but no COMMIT?
  2. Why a cursor loop rather than bulk collection?
-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu May 27 2004 - 09:52:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US