Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use of REF in table

User_V7K1DDec 17 2022 — edited Dec 19 2022

Hi there,
I want to use ref to store two objects in a table.
Below is my schema:
create or replace type event_info as object (
event_info_id NUMBER,
event_name VARCHAR2(20),
event_type VARCHAR2(20),
event_duration VARCHAR2(20),
event_start_Date DATE,
event_end_Date DATE,
member procedure getEventName
);
/

create table event_info_table of event_info;

INSERT INTO event_info_table VALUES(event_info(1,'music performance', 'music', '5hrs','05-DEC-2021','05-DEC-2021'));

INSERT INTO event_info_table VALUES(event_info(2,'sport event', 'sport', '7hrs','09-DEC-2021','10-DEC-2021'));

INSERT INTO event_info_table VALUES(event_info(3,'music performance', 'music', '8hrs','13-DEC-2021','14-DEC-2021'));

INSERT INTO event_info_table VALUES(event_info(4,'theatre ', 'drama', '10hrs','11-DEC-2021','12-DEC-2021'));

INSERT INTO event_info_table VALUES(event_info(5,'promotion ', 'drama', '5hrs','21-DEC-2021','21-DEC-2021'));

INSERT INTO event_info_table VALUES(event_info(6,'music performance', 'music', '2hrs','25-DEC-2021','26-DEC-2021'));

create or replace type address as object (
address_id number,
street varchar2(4000),
city varchar2(4000),
state varchar2(4000),
country varchar2(4000),
postal_code varchar2(4000)
);
/

create table address_table of address;

create or replace type person_details as object (
personal_details_id number,
firstName varchar2(4000),
lastName varchar2(4000),
age NUMBER,
contactNumber NUMBER,
address_ address
);
/

create table person_details_table of person_details;

INSERT INTO person_details_table VALUES(person_details(1,'harsh', 'vardhan', 21 ,22002222,address(1,'stabu', 'riga','up','latvia','250101')));

INSERT INTO person_details_table VALUES(person_details(2,'basant', 'dhama', 22 ,22002022,address(2,'jurmala', 'jekabpils','up','latvia','250202')));

INSERT INTO person_details_table VALUES(person_details(3,'raj', 'raj', 23 ,22002122,address(3,'ropazu', 'jurmala','up','latvia','250101')));

INSERT INTO person_details_table VALUES(person_details(4,'krishna', 'kumar', 24 ,22002622,address(4,'kipsala', 'cesis','up','latvia','250105')));

INSERT INTO person_details_table VALUES(person_details(5,'mohit', 'malik', 25 ,22002822,address(5,'matisa', 'riga','up','latvia','1006')));

INSERT INTO person_details_table VALUES(person_details(6,'diana', 'devi', 26 ,22002922,address(6,'getrudas', 'riga','up','latvia','1006')));

create table event_organizer_table(
event_organizer_id NUMBER,
organizer_details_REF REF person_details,
event_info_REF REF event_info,
event_type VARCHAR2(20) );

INSERT INTO event_organizer_table SELECT 1,REF(T), REF(M) FROM person_details_table T, event_info_table M, 'sport' ;
How can I store data for a particular peron_details and event_info into event_organizer_table?
Please let me know.
This post has been answered by User_V7K1D on Dec 18 2022
Jump to Answer

Comments

cormaco

Please post your example XML as actual text.

User_2K4XP

Hi,
Below is the xml as actual text.

<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>
<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>

Thanks !

cormaco

Here is one way:
It is necessary to use XMLPARSE because your example is an XML fragment, not a complete XML file.

with example(xmlfile) as (
select xmlparse(content
'<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>
<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>') from dual)
select distinct x.* 
from example,xmltable(
    '/Party/TRDetail'
    passing xmlfile
    columns
        trindicator varchar2(3)  path 'TRIndicator',
        trname      varchar2(20) path 'TRName'
) x

TRINDICATOR TRNAME              
----------- --------------------
yes         XYZ                 
1 row selected.
1 person found this helpful
1 - 3

Post Details

Added on Dec 17 2022
#database, #general-database-discussions, #oracle, #sql
14 comments
297 views