CODEWITHSUNDEEP
sqloracle-databaseplsqluser-defined-types
S.Marx
S.Marx

Reputation: 1022

How to create a collection and insert values in a table with the collection?

Hey I am creating a database to store some information about a sport club, where I have trainers and athletes, but when I am trying to insert trainers(entrenadors) to the table that errors appears:

enter image description here

The code that I have is this one:

  1. Creation of the person type (t_persona) who inherit the types of coach and athletes. We must create a function that returns all the data

CREATE OR REPLACE type t_persona as object( nom VARCHAR2(20), cognom VARCHAR2(20), dataNaix DATE, MEMBER PROCEDURE mostrar ) NOT FINAL; / CREATE OR REPLACE type body t_persona AS MEMBER PROCEDURE mostrar IS BEGIN dbms_output.put_line('Nom: ' || nom || 'Cognom: ' || cognom || 'DataNaix: ' || dataNaix); END; END; /

2.Creation of the curs type (t_curs).

CREATE OR REPLACE type t_curs as object( nom VARCHAR2(50), hores NUMBER(10), lloc VARCHAR2(50), dia DATE );

3.Creation of the collection of courses (t_cursos) of type t_curs.

CREATE OR REPLACE TYPE t_cursos AS varray(20) of t_curs;

4.Creation of the coach type (t_entrenandor), must be inherited from type t_persona. This must have a function that returns all the data reusing the function of t_persona. 

CREATE OR REPLACE type t_entrenador under t_persona(
   telefon VARCHAR2(9),
   cursos t_cursos,
OVERRIDING MEMBER PROCEDURE mostrar);
/

CREATE OR REPLACE type body t_entrenador as
OVERRIDING MEMBER PROCEDURE  mostrar is
 BEGIN
   (self as t_persona).mostrar;
    dbms_output.put_line('Telefon: ' || telefon || 'Cursos : ' || cursos);
  END;
END;

5.Create table entrenadors that with contain entrenadors.

CREATE TABLE entrenadors of t_entrenador
  1. Insert into table entrenadors two entrena

INSERT INTO entrenadors VALUES('Daniel', 'Rovira', to_date('12/01/2014','DD/MM/YYYY'), '65555222', t_cursos('spinning', 1, 'España', to_date('12/01/2014','DD/MM/YYYY')));

In that number 6 is where the error occurs, can someone help me finding what is wrong? Thank you!

Upvotes: 1

Views: 684

Answers (1)

APC
APC

Reputation: 146239

t_entrenador.cursos has datatype of t_cursos which is a table of t_curs. To instantiate the table you need to pass instances of t_curs. 

INSERT INTO entrenadors 
VALUES('Daniel', 'Rovira', to_date('12/01/2014','DD/MM/YYYY'), '65555222',
        t_cursos(t_curs('spinning', 1, 'España', to_date('12/01/2014','DD/MM/YYYY'))
   )
  );

Upvotes: 2

Related Questions