DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problem to get ref of table of objects with nested table

  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Problem to get ref of table of objects with nested table

    Hi all, i've a table of objects with a nested table. If i query for a ref of object inside that table i receive a null ref....why???

    This is my situation:

    Code:
    CREATE OR REPLACE TYPE tCategoria AS OBJECT(
      nome VARCHAR2(100),
      nPremi INTEGER,
      minHandicap INTEGER,
      maxHandicap INTEGER,
      age INTEGER
    );
    /
     
    CREATE OR REPLACE TYPE categorie_tab IS TABLE OF tCategoria;
    /
     
    CREATE OR REPLACE TYPE tGara AS OBJECT(
      nome VARCHAR2(100),
      DATA DATE,
      par INTEGER,
      reserved NUMBER(1),
      sponsor VARCHAR2(100),
      categorie categorie_tab,
      MEMBER FUNCTION classifica RETURN XMLTYPE
    );
    /
     
    CREATE TABLE Gare OF tGara(
      nome NOT NULL,
      DATA NOT NULL,
      par NOT NULL,
      sponsor NOT NULL,
      reserved NOT NULL)
    NESTED TABLE categorie STORE AS gara_categorie
    /
     
    SELECT * FROM gare g WHERE g.nome = 'Coppa del Presidente';
    /
    SELECT REF(g) FROM gare g WHERE g.nome = 'Coppa del Presidente';
    The first query result is correct:
    http://imageshack.us/f/441/resgh.png/

    But with the second one i get a null ref:
    http://imageshack.us/f/145/resquery.png/

    Really, i don't understand why! :(

    Thanks in advance and sorry for my english :P

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Post some sample data.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Sorry for late reply.
    I try to explain better (in short) the problem:
    i want to create a database to store the informations about golf clubs and their members.
    Each club organizes races for which information is passed through an xml file.

    So my schema is:

    Code:
    -------------------------------------------------------------------------
    -- CATEGORY  Each player takes part in a race in one or more categories
    -------------------------------------------------------------------------
    CREATE OR REPLACE TYPE tCategoria AS OBJECT(
      nome VARCHAR2(100),
      nPremi INTEGER,
      minHandicap INTEGER,
      maxHandicap INTEGER,
      age INTEGER
    );
    /
    
    CREATE OR REPLACE TYPE categorie_tab IS TABLE OF tCategoria;
    /
    
    -----------------------------
    -- RACE
    -----------------------------
    CREATE OR REPLACE TYPE tGara AS OBJECT(
      nome VARCHAR2(100),
      data DATE,
      par INTEGER,
      reserved NUMBER(1),
      sponsor VARCHAR2(100),
      categorie categorie_tab,
      MEMBER FUNCTION classifica RETURN XMLTYPE
    );
    /
    
    CREATE TABLE Gare OF tGara(
      nome NOT NULL,
      data NOT NULL,
      par NOT NULL,
      sponsor NOT NULL,
      reserved NOT NULL)
    NESTED TABLE categorie STORE AS gara_categorie
    /
    
    CREATE OR REPLACE TYPE gare_tab AS TABLE OF REF tGara;
    /
    
    CREATE OR REPLACE TYPE telefoni_tab AS TABLE OF CHAR(10);
    /
    
    ------------------------------
    -- GOLF CLUB
    ------------------------------
    CREATE OR REPLACE TYPE tCircolo AS OBJECT(
        nome VARCHAR2(100),
        citta VARCHAR2(100),
        ospita gare_tab,
        telefoni telefoni_tab,
        MEMBER FUNCTION numeroIscritti RETURN INTEGER,
        MEMBER PROCEDURE creaGara(inputXML XMLTYPE, par INTEGER)
    );
    /
    
    CREATE TABLE Circoli OF tCircolo(
      nome NOT NULL,
      citta NOT NULL
    )
    NESTED TABLE ospita STORE AS circolo_ospita,
    NESTED TABLE telefoni STORE AS circolo_telefoni;
    /
    
    ------------------------------------------
    -- MEMBER / PLAYER
    ------------------------------------------
    CREATE OR REPLACE TYPE tGiocatore AS OBJECT(
      tessera CHAR(10),
      nome VARCHAR2(30),
      cognome VARCHAR2(50),
      sesso CHAR,
      eta INTEGER,
      handicap NUMBER(3,1),
      iscritto REF tCircolo
    );
    /
    
    CREATE TABLE Giocatori OF tGiocatore(
      tessera NOT NULL PRIMARY KEY,
      nome NOT NULL,
      cognome NOT NULL,
      sesso NOT NULL CONSTRAINT sesso_check CHECK(upper(sesso) IN ('M','F')),
      eta NOT NULL CONSTRAINT eta_check CHECK(eta > 0),
      handicap NOT NULL CONSTRAINT handicap_check CHECK(handicap BETWEEN 0 AND 36),
      iscritto SCOPE IS Circoli
    );
    /
    
    ---------------------------------
    -- PARTECIPATING IN THE RACE
    ---------------------------------
    CREATE TABLE Partecipa(
      giocatore REF tGiocatore SCOPE IS Giocatori,
      gara REF tGara NOT NULL,
      categorie categorie_tab,
      risultati XMLTYPE)
    NESTED TABLE categorie STORE AS partecipa_categorie;
    /
    
    CREATE TABLE xml_temp(data XMLTYPE);
    /

    As i said, to create a race i need to parse an xml file and store the information about it:
    Code:
    ----------------------------------------------------------------------------------------------------
    -- Procedure "creaGara" (create race) and FUNCTION "numeroIscritti " (get number of club's members)
    -----------------------------------------------------------------------------------------------------
    CREATE OR REPLACE TYPE BODY tCircolo AS
      
      MEMBER PROCEDURE creaGara(inputXML XMLTYPE, par INTEGER) AS
        nome varchar2(100);
        data varchar2(10);
        reserved NUMBER(1);
        sponsor varchar2(100);
        
        gara tGara;
        
        xml_data XMLTYPE;
      
      BEGIN
        if inputXML.isschemavalid('GolfCompetition.xsd') = 1 then
          dbms_output.put_line('XML IS VALID');
          
          insert into xml_temp values(inputXML);
          
          nome := XMLTYPE.extract(inputXML,'/GolfCompetition/Name/text()').getStringVal();
          data := XMLTYPE.extract(inputXML,'/GolfCompetition/Date/text()').getStringVal();
          
          xml_data := XMLTYPE.extract(inputXML,'/GolfCompetition/Reserved/text()');
          
          if xml_data is null or xml_data.getStringVal() = 'no' or xml_data.getStringVal() = '0' then
            reserved := 0;
          else
            reserved := 1;
          end if;
          
          sponsor := XMLTYPE.extract(inputXML,'/GolfCompetition/Sponsor/text()').getStringVal();
          
          --dbms_output.put_line(nome || ' ' || to_date(data,'YYYY-MM-DD') || ' ' || par || ' ' || sponsor || ' ' || reserved);
          
          gara := new tGara(nome,to_date(data,'YYYY-MM-DD'),par,reserved,sponsor, new categorie_tab());
          
          --inserisco gara
          insert into gare values(gara);
          
          --inserimento categorie
          insert into table(select g.categorie from gare g where value(g) = gara)
          select gar."Name", gar."NumPrize", gar."From", gar."To", gar."Age"
          from xml_temp,
               xmltable('//Category'
               passing xml_temp.data
                    columns
                    "Name" varchar2(50) PATH '/Category',
                    "NumPrize" integer PATH '/Category/@NumPrize',
                    "From" integer PATH '/Category/@From',
                    "To" integer PATH '/Category/@To',
                    "Age" integer PATH '/Category/@Age') gar;
          
          --inserisco il riferimento alla gara all'interno della nested table del circolo
          insert into table(select c.ospita from circoli c where value(c) = self)
          select ref(g) from gare g where value(g) = gara;
          
          --eliminazione xml temporaneo
          delete from xml_temp;
          
        else
          dbms_output.put_line('XML IS NOT VALID!');
        end if; 
      END creaGara;
      
      MEMBER FUNCTION numeroIscritti RETURN INTEGER IS
      BEGIN
        RETURN 0;
      END numeroIscritti;
    END;
    /

    Some inserts:
    Code:
    -----------------------
    -- CREATE GOLF CLUBS
    -----------------------
    INSERT INTO Circoli VALUES(tCircolo('Barlassina A.S.D. Golf Country Club', 'Birago di Camnago (Monza)', new gare_tab(), new telefoni_tab('0362560214', '0362560225')));
    INSERT INTO Circoli VALUES(tCircolo('Lecco Golf Club', 'Annone Brianza (Lecco)', new gare_tab(), new telefoni_tab('0341579526')));
    INSERT INTO Circoli VALUES(tCircolo('Ambrosiano Golf Club', 'Bubbiano (Milano)', new gare_tab(), new telefoni_tab('0290840825','0290840826','0290840827')));
    INSERT INTO Circoli VALUES(tCircolo('Green Club Lainate', 'Lainate (Milano)', new gare_tab(), new telefoni_tab('029370864')));
    
    --------------------------
    -- CREATE A TEST RACE
    --------------------------
    set serveroutput on;
    declare
      circolo tCircolo;
    begin
      select value(c) into circolo from circoli c where c.nome = 'Barlassina A.S.D. Golf Country Club';
      circolo.creaGara(xmltype('
    
    	Coppa del Presidente
    	2009-12-25
    	Lavazza S.p.A
    	First
    	Second
    	Third
    	Lady
    	Over
    '),2);
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width