-
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
-
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|