Update attribute of an element in a nested table

Question

I have created this type: create or replace type PRODTABLE as table of PROD_OBJ;

and I use that PRODTABLE in the follow PLSQL code:

    FUNCTION  INSERT_PRODUCTS (

      a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE,

      a_prodArray IN PRODTABLE

      ) 

     RETURN NUMBER IS

        v_error_code    NUMBER;
        v_error_message VARCHAR2(255);
        v_result        NUMBER:= 0;
        v_prod_id       PRODUTO.ID_PROD%TYPE;
        v_supplier      FORNECEDOR%ROWTYPE;
        v_prodInserted  PROD_OBJ;
        type nestedTable is table of PROD_OBJ;
        newList   nestedTable := nestedTable();

     BEGIN

     SELECT  FORNEC_OBJ(ID_FORNECEDOR,NOME_FORNECEDOR,MORADA,ARMAZEM,EMAIL,TLF,TLM,FAX) into v_supplier from fornecedor where id_fornecedor = a_supplier_id;

      FOR i IN a_prodArray.FIRST .. a_prodArray.LAST LOOP

          INSERT INTO PRODUTO (PRODUTO.ID_PROD,PRODUTO.NOME_PROD,PRODUTO.PREC_COMPRA_PROD,PRODUTO.IVA_PROD,PRODUTO.PREC_VENDA_PROD,PRODUTO.QTD_STOCK_PROD,PRODUTO.QTD_STOCK_MIN_PROD) 
          VALUES (S_PRODUTO.nextval,a_prodArray(i).NOME_PROD,a_prodArray(i).PREC_COMPRA_PROD,a_prodArray(i).IVA_PROD,NULL,NULL,NULL);

          SELECT ID_PROD into v_prod_id from PRODUTO where NOME_PROD = a_prodArray(i).NOME_PROD;

          INSERT INTO PROD_FORNECIDO VALUES (a_supplier_id, v_prod_id,a_prodArray(i).PREC_COMPRA_PROD);

          SELECT PROD_OBJ(ID_PROD,NOME_PROD,PREC_COMPRA_PROD,PREC_VENDA_PROD,QTD_STOCK_PROD,QTD_STOCK_MIN_PROD,IVA_PROD) into v_prodInserted from PRODUTO where ID_PROD= v_prod_id;
          newList.extend;
          newList(newList.last):= v_prodinserted;

        END LOOP;

        /*the next line generates Error(43,63): PLS-00642: local collection types not      allowed in SQL statements,
Error(43,63): PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR */
        INSERT INTO FORNECPRODS2 VALUES (a_supplier_id,v_supplier,newList);
        v_result:= 1; 
        RETURN v_result;
        COMMIT;
    (...)
    END;

I want to do something like a_prodArray(i):= v_prodInserted, but I can't because it's a nested table, so I need to retrieve another one to use in that insert OR update each element of nested table with the new inserted product. I have to do this because each prod_obj in PRODTABLE comes with an id=0, from JAVA. Maybe there's another way, like making the default value for primary key equals SEQUENCE.nextval, I don't know. Could someone please enlight me?

Thanks!

Answer 1

Hi newbAna,

you won't be able to modify a_prodArray since it is declared as an IN parameter (and therefore can not be used as an assignment target). IF you declare the parameter as an IN OUT, the assignment succeeds:

SQL> CREATE TYPE PROD_OBJ AS OBJECT
  2  (
  3     ID_PROD   NUMBER,
  4     NOME_PROD VARCHAR2(1)
  5  )
  6  ;
  7  /     
Type created

SQL> CREATE TYPE PRODTABLE as table of PROD_OBJ;
  2  /     
Type created

SQL> CREATE FUNCTION INSERT_PRODUCTS(a_prodArray IN OUT PRODTABLE)
  2     RETURN NUMBER IS
  3  BEGIN
  4     a_prodArray(1) := prod_obj(1, NULL);
  5     RETURN 0;
  6  END;
  7  /     
Function created

SQL> DECLARE
  2     l_prod_table prodtable := prodtable();
  3     dummy NUMBER;
  4  BEGIN
  5     l_prod_table.extend();
  6     dummy := INSERT_PRODUCTS(l_prod_table);
  7  END;
  8  /     
PL/SQL procedure successfully completed

Now for the PLS-00642 error, you have to use the EXACT same datatype as the definition of the table. In that case I suppose the type is the PRODTABLE SQL Type and you will have to declare newList as a PRODTABLE (and not a PLSQL type nestedTable).