Home » SQL & PL/SQL » SQL & PL/SQL » Query when we have a method in table definition
Query when we have a method in table definition [message #352657] Wed, 08 October 2008 19:44 Go to next message
AMIR64
Messages: 6
Registered: October 2008
Junior Member
Hi all;

I really need the following queries. Im a new user of Oracle and im confused with this sort of queries.
the followed is first the definition of TYPEs and TABLEs and then the requested queries:

CREATE or replace TYPE WaterType AS object
(
name VARCHAR(20)
)
NOT INSTANTIABLE
NOT FINAL;


CREATE or replace TYPE OceanType UNDER WaterType
(
area FLOAT
);


CREATE or replace TYPE RiverType UNDER WaterType
(
length INT, /* in km */
meets REF(WaterType) SCOPE Rivers
runs_through REF(StateType) MULTISET SCOPE States
)
INSTANCE METHOD leads_to_ocean() RETURNS OceanType;


CREATE or replace TYPE StateType AS object
(
name VARCHAR(20),
abbrv CHAR(3),
capital VARCHAR(50)
);


CREATE TABLE Oceans OF OceanType;
CREATE TABLE Rivers OF RiverType;
CREATE TABLE States OF StateType;


1)all rivers which are longer than 100km and shorter than 130km, and which lead into the Pacific ocean.
2)the abbreviations of the states through which the river "Murray" flows.

Thanks in advance
AMIR
icon14.gif  Re: Query when we have a method in table definition [message #352677 is a reply to message #352657] Wed, 08 October 2008 23:24 Go to previous messageGo to next message
NoraizOraDev
Messages: 11
Registered: September 2008
Junior Member
The syntax to create type is
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>,
...,
<column_name> <data_type>);
/

For example
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(Cool);
/

To verify
SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME = 'phone_t'

Have a nice Day!
Re: Query when we have a method in table definition [message #352679 is a reply to message #352677] Wed, 08 October 2008 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both,

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
OP, always post your Oracle version (4 decimals).

Regards
Michel
Re: Query when we have a method in table definition [message #352680 is a reply to message #352679] Wed, 08 October 2008 23:45 Go to previous messageGo to next message
AMIR64
Messages: 6
Registered: October 2008
Junior Member
But I only need to know how I can Query that two questions at the buttom
Re: Query when we have a method in table definition [message #352685 is a reply to message #352680] Thu, 09 October 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AMIR64 wrote on Thu, 09 October 2008 06:45
But I only need to know how I can Query that two questions at the buttom

Put what you tried (and post it formated), we don't do homework.

Regards
Michel

Re: Query when we have a method in table definition [message #352726 is a reply to message #352685] Thu, 09 October 2008 03:50 Go to previous messageGo to next message
AMIR64
Messages: 6
Registered: October 2008
Junior Member
--first one
select r.name.name
from Rivers r
where r.length.length >100 and r.length.length <130 and r.leads_to_ocean().name = 'pacific';

--second one
select s.abbrv.abbrv
from States s, Rivers r
where s.name In (select DEREF(u).name
from UNNEST(r.runs_through) u
where r.name = 'Murray')
Re: Query when we have a method in table definition [message #352733 is a reply to message #352726] Thu, 09 October 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 09 October 2008 06:41
Both,

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
OP, always post your Oracle version (4 decimals).

Regards
Michel

Re: Query when we have a method in table definition [message #352736 is a reply to message #352733] Thu, 09 October 2008 04:35 Go to previous messageGo to next message
AMIR64
Messages: 6
Registered: October 2008
Junior Member
what do u mean by the version? I use Oracle iSQL*Plus (10g)

[Updated on: Thu, 09 October 2008 04:36]

Report message to a moderator

Re: Query when we have a method in table definition [message #352741 is a reply to message #352736] Thu, 09 October 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

1 row selected.

And this is NOT the only and most important point.

Regards
Michel
Re: Query when we have a method in table definition [message #352744 is a reply to message #352741] Thu, 09 October 2008 05:11 Go to previous message
AMIR64
Messages: 6
Registered: October 2008
Junior Member
the version is:

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
Previous Topic: De-Duping the Table Data
Next Topic: ORA-00936: missing expression
Goto Forum:
  


Current Time: Wed Dec 07 08:52:03 CST 2016

Total time taken to generate the page: 0.06305 seconds