Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 16, 2021 - 12:02 pm UTC

Last updated: February 22, 2021 - 4:54 pm UTC

Version: Oracle 11gR2 XE

Viewed 1000+ times

You Asked

Hi, i have de following error on this code:
--Exercici 1
 Create type Adreca_T As Object(
 carrer varchar2(80),
 numero number(3),
 poblacio varchar2(80),
 codiPostal number(5)
 );
 Create type E_Empleat_T As Object(
 Nif varchar2(9),
 Nom varchar2(50),
 adreça Adreca_T,
 dataNaixement date,
 sou number(5),
 cap REF E_Empleat_T
 );
 create table TaulaEmpleats of E_Empleat_T;
 insert into TaulaEmpleats values ('38234567A','Josep Verdú Bargalló', 
 Adreca_T('Estanislau Figueres',25,'Tarragona',43002),
 to_date('12/12/1973','dd/mm/yyyy'),4500,null);
 insert into TaulaEmpleats values ('39311345X','Pere Garcia Llorens', 
 Adreca_T('Blanca dAnjou',45,'Tarragona',43002),
 to_date('05/07/1980','dd/mm/yyyy'),1000,(select REF(E) from TaulaEmpleats E where nif = '38234567A'));
--Exercici 2
CREATE TYPE telefons_T is VARRAY(5) of varchar2(10);
Create or replace type E_Empleat2_T As Object(
 Nif varchar2(9),
 Nom varchar2(50),
 adreça Adreca_T,
 dataNaixement date,
 sou number(5),
 cap REF E_Empleat2_T,
    Telefon Telefons_t,
    member procedure afegirTelefon(Telefon in varchar2),
    member procedure modificarTelefon (telefonold in varchar2, telefonnew in varchar2)
 );
drop table TaulaEmpleats;
create table TaulaEmpleats of E_Empleat2_T;
insert into TaulaEmpleats values ('38234567A','Josep Verdú Bargalló', 
Adreca_T('Estanislau Figueres',25,'Tarragona',43002),
to_date('12/12/1973','dd/mm/yyyy'),4500,null,null);

insert into TaulaEmpleats values ('39311345X','Pere Garcia Llorens', 
Adreca_T('Blanca dAnjou',45,'Tarragona',43002),
to_date('05/07/1980','dd/mm/yyyy'),1000,
(select REF(E) from TaulaEmpleats E where nif = '38234567A'),null);
--Exercici 3
Create or Replace Type body E_Empleat2_T as 

    member PROCEDURE afegirTelefon (telefon VARCHAR2) as
    taula_af telefons_T;
    numero number(5);
    begin
        select telefon into taula_af from TaulaEmpleats where nif =SELF.Nif;
        numero:=taula_af.Count;
        taula_af.EXTEND();
        taula_af(numero+1):=Telefon;
        update TaulaEmpleats set telefon=taula_af where nif =SELF.Nif;
        end afegirTelefon; 
        
    member PROCEDURE modificarTelefon (telefonold VARCHAR2, telefonnew VARCHAR2) as
    taula_af telefons_T;
    i number(5);
    fi boolean;
    begin
        select telefon into taula_af from TaulaEmpleats where nif =SELF.Nif;
        i:=1;
        fi:=false;
        while (i<=taula_af.COUNT and not fi) loop
            if (taula_af(i) = telefonold) then
                taula_af(i):=telefonnew;
                fi:=true;
            end if;
            i:=i+1;
        end loop;
        Update TaulaEmpleats set telefon=taula_af where nif=SELF.Nif;
        end modificarTelefon;
end;
declare 
x E_Empleat2_T;
begin 
    select value(E) into x from TaulaEmpleats E where E.nif = '39311345X';
    x.afegirTelefon('659312111');
    update TaulaEmpleats E set E= x where E.nif='39311345X';
End;

The problem, was in "Exercici 3"
The banner

Error que empieza en la línea: 1 del comando :
declare
x E_Empleat2_T;
begin
select value(E) into x from TaulaEmpleats E where E.nif = '39311345X';
x.afegirTelefon('659312111');
update TaulaEmpleats E set E= x where E.nif='39311345X';
End;
Ther error:
Informe de error -
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SYSTEM.E_EMPLEAT2_T", line 8
ORA-06512: at line 5
06531. 00000 - "Reference to uninitialized collection"
*Cause: An element or member function of a nested table or varray
was referenced (where an initialized collection is needed)
without the collection having been initialized.
*Action: Initialize the collection with an appropriate constructor
or whole-object assignment.
Any help will be appreciated.

and Connor said...

To use a collection you must initialise it in some way.

Either with a query or directly. Examples below

SQL> create or replace
  2  type my_list is
  3    table of number;
  4  /

Type created.

SQL>
SQL> declare
  2    n my_list;
  3  begin
  4    n.extend;
  5    n(1) := 'x';
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 4


SQL>
SQL> declare
  2    n my_list := my_list();   -- initialise
  3  begin
  4    n.extend;
  5    n(1) := 10;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
  2    n my_list;
  3  begin
  4    select empno
  5    bulk collect into n
  6    from scott.emp;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clarifications

Gerard Capilla Baeza, February 18, 2021 - 7:51 am UTC

I don't use a varray collection in my exercice and when i tried your solution I found this another error:
Type Body E_EMPLEAT2_T compilado

LINE/COL ERROR
--------- -------------------------------------------------------------
7/9 PL/SQL: SQL Statement ignored
7/16 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got SYSTEM.TELEFONS_T
19/9 PL/SQL: SQL Statement ignored
19/16 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got SYSTEM.TELEFONS_T
Errores: comprobar log de compilador
This is the codde with your changes:
create or replace Type body E_Empleat2_T as 

    member PROCEDURE afegirTelefon (telefon VARCHAR2) as
    taula_af telefons_T := telefons_T();
    numero number(5);
    begin
        select telefon bulk collect into taula_af from TaulaEmpleats where nif =SELF.Nif;
        numero:=taula_af.Count;
        taula_af.EXTEND;
        taula_af(numero+1):=Telefon;
        update TaulaEmpleats set telefon=taula_af where nif =SELF.Nif;
        end afegirTelefon; 

    member PROCEDURE modificarTelefon (telefonold VARCHAR2, telefonnew VARCHAR2) as
    taula_af telefons_T;
    i number(5);
    fi boolean;
    begin
        select telefon bulk collect into taula_af from TaulaEmpleats where nif =SELF.Nif;
        i:=1;
        fi:=false;
        while (i<=taula_af.COUNT and not fi) loop
            if (taula_af(i) = telefonold) then
                taula_af(i):=telefonnew;
                fi:=true;
            end if;
            i:=i+1;
        end loop;
        Update TaulaEmpleats set telefon=taula_af where nif=SELF.Nif;
        end modificarTelefon;
end;


Chris Saxon
February 22, 2021 - 4:54 pm UTC

I don't use a varray collection in my exercice

This looks like a varray to me:

CREATE TYPE telefons_T is VARRAY(5) of varchar2(10);


As the error says, you have a type mismatch.

TaulaEmpleats.telefon => varray

When you bulk collect this into something, you're getting an array of arrays. Whereas the target type is just:

taula_af telefons_T

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library