Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Type with Member (Oracle, 19.2, Linux)
| Oracle Type with Member [message #690489] |
Fri, 26 June 2026 13:52  |
Duane
Messages: 593
Registered: December 2002
|
Senior Member |
|
|
Hello,
Just trying to see if this can be done a different way.
Resolution: I'm wanting the values within the "Name" column to be in separate columns.
Not sure if that can be done or not with my current SQL statement. That's why I'm asking.
Table/Table Data
create table fallen_stars
(id varchar2(9),
first_name varchar2(60),
middle_name varchar2(60),
last_name varchar2(60));
Insert into FALLEN_STARS (ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME) values ('123','Elvis','Aaron','Presley');
Insert into FALLEN_STARS (ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME) values ('456','James',null,'Dean');
Insert into FALLEN_STARS (ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME) values ('789','Marilyn ','Jeane','Monroe');
Type
create or replace type FallenStarsType as object(ID varchar2(9),
Name varchar2(400),
FirstName varchar2(60),
MiddleName varchar2(60),
LastName varchar2(60));
create or replace type FallenStarsTable as table of FallenStarsType;
Oracle Type Spec/Body
create or replace type Stars as object (ID varchar2(9),
NameType varchar2(30),
member function StarID return varchar2,
member function Name return FallenStarsTable pipelined);
create or replace type body Stars as
member function StarID return varchar2 is
StarIDNumber fallen_stars.id%type;
begin
select id
into StarIDNumber
from fallen_stars
where id = self.ID;
return StarIDNumber;
exception
when no_data_found
then
return '';
end StarID;
member function Name return FallenStarsTable pipelined is
Name varchar2(400);
FirstName fallen_stars.first_name%type;
LastName fallen_stars.last_name%type;
MiddleName fallen_stars.middle_name%type;
cursor DataElements is
select id,
trim(first_name) first_name,
trim(last_name) last_name,
trim(middle_name) middle_name,
case
when upper(self.NameType) = 'FIRST'
then
trim(first_name)
when upper(self.NameType) = 'LAST'
then
trim(last_name)
when upper(self.NameType) = 'FIRSTLAST'
then
trim(first_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRST'
then
trim(last_name)||', '||trim(first_name)
when upper(self.NameType) = 'FIRSTMIDDLELAST'
then
trim(first_name)||' '||trim(middle_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRSTMIDDLE'
then
trim(last_name)||', '||trim(first_name)||' '||trim(middle_name)
else
null
end name
from fallen_stars
where id = self.ID;
begin
for i in DataElements
loop
pipe row (FallenStarsType(i.id,
i.name,
i.first_name,
i.middle_name,
i.last_name));
end loop;
return;
exception
when no_data_found
then
raise;
end Name;
end;
Query
select Stars(id, null).StarID() ID,
Stars(id, 'LASTFIRST').Name() Name
from fallen_stars
order by ID
This is after I click within the "Name" column.
[img]/forum/fa/14876/0/[/img]
Query 2
This query provides the data elements into separate columns but I'm unsure how to integrate this with the fallen_stars table.
select *
from Stars(123, 'LASTFIRST').Name() Name
|
|
|
|
| Re: Oracle Type with Member [message #690491 is a reply to message #690489] |
Wed, 01 July 2026 07:37  |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You need Stars member function Name to return object type, not table type:
create or replace type StarsSY as object (
ID varchar2(9),
NameType varchar2(30),
member function StarID return varchar2,
member function Name return FallenStarsType);
/
create or replace type body StarsSY as
member function StarID return varchar2 is
StarIDNumber fallen_stars.id%type;
begin
select id
into StarIDNumber
from fallen_stars
where id = self.ID;
return StarIDNumber;
exception
when no_data_found
then
return '';
end StarID;
member function Name return FallenStarsType is
v_res FallenStarsType;
begin
select FallenStarsType(
id,
trim(first_name),
trim(last_name),
trim(middle_name),
case
when upper(self.NameType) = 'FIRST' then trim(first_name)
when upper(self.NameType) = 'LAST' then trim(last_name)
when upper(self.NameType) = 'FIRSTLAST' then trim(first_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRST' then trim(last_name)||', '||trim(first_name)
when upper(self.NameType) = 'FIRSTMIDDLELAST' then trim(first_name)||' '||trim(middle_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRSTMIDDLE' then trim(last_name)||', '||trim(first_name)||' '||trim(middle_name)
end
)
into v_res
from fallen_stars
where id = self.ID;
return v_res;
exception
when no_data_found
then
raise;
end Name;
end;
/
select * from table(select cast(collect(StarsSY(ID, 'LASTFIRST').Name()) as FALLENSTARSTABLE) from fallen_stars);
ID NAME FIRSTNAME MIDDLENAME LASTNAME
----- -------------------- -------------------- -------------------- --------------------
123 Elvis Presley Aaron Presley, Elvis
456 James Dean Dean, James
789 Marilyn Monroe Jeane Monroe, Marilyn
SQL>
select *
from FALLENSTARSTABLE(StarsSY(123, 'LASTFIRST').Name()) Name
select *
from Stars(123, 'LASTFIRST').Name() Name
SY.
|
|
|
|
| Re: Oracle Type with Member [message #690492 is a reply to message #690489] |
Wed, 01 July 2026 09:37 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Actually, you can get rid of member function StarID and add ID as member function Name parameter:
create or replace type StarsX as object (NameType varchar2(30),
member function Name(p_id varchar2) return FallenStarsTable pipelined);
/
create or replace type body StarsX as
member function Name(p_id varchar2) return FallenStarsTable pipelined is
Name varchar2(400);
FirstName fallen_stars.first_name%type;
LastName fallen_stars.last_name%type;
MiddleName fallen_stars.middle_name%type;
cursor DataElements is
select id,
trim(first_name) first_name,
trim(last_name) last_name,
trim(middle_name) middle_name,
case
when upper(self.NameType) = 'FIRST'
then
trim(first_name)
when upper(self.NameType) = 'LAST'
then
trim(last_name)
when upper(self.NameType) = 'FIRSTLAST'
then
trim(first_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRST'
then
trim(last_name)||', '||trim(first_name)
when upper(self.NameType) = 'FIRSTMIDDLELAST'
then
trim(first_name)||' '||trim(middle_name)||' '||trim(last_name)
when upper(self.NameType) = 'LASTFIRSTMIDDLE'
then
trim(last_name)||', '||trim(first_name)||' '||trim(middle_name)
else
null
end name
from fallen_stars
where id like p_ID;
begin
for i in DataElements
loop
pipe row (FallenStarsType(i.id,
i.name,
i.first_name,
i.middle_name,
i.last_name));
end loop;
return;
exception
when no_data_found
then
raise;
end Name;
end;
/
select * from StarsX('LASTFIRST').Name('%');
ID NAME FIRSTNAME MIDDLENAME LASTNAME
----- -------------------- -------------------- -------------------- --------------------
123 Presley, Elvis Elvis Aaron Presley
456 Dean, James James Dean
789 Monroe, Marilyn Marilyn Jeane Monroe
SQL>
select * from StarsX('LASTFIRST').Name('123');
ID NAME FIRSTNAME MIDDLENAME LASTNAME
----- -------------------- -------------------- -------------------- --------------------
123 Presley, Elvis Elvis Aaron Presley
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jul 19 14:48:46 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
