How to select from the object

[Chandra]

how can i select the rows from the object here is my object

create or replace type concerned as object(
msisdn varchar2(10),
profile number(5));


when i try to select from the sql like

SQL> select t.msisdn from concerned t;
select t.msisdn from concerned t
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here

any idea.

Thanks

[TimHall]

Think of this as a user defined datatype. You can't do a:

SELECT * FROM VARCHAR2

so why would you expect to do this from your datatype. You must first create an instance of the object and store it in a table. At this point you can select it using the "." notation. Try this:

CREATE OR REPLACE TYPE concerned AS OBJECT(
msisdn VARCHAR2(10),
profile NUMBER(5));
/

CREATE TABLE concerned_tab (
id NUMBER(10),
concerned_col concerned);

INSERT INTO concerned_tab (id, concerned_col)
VALUES (1, concerned('TEST',5));
COMMIT;

SELECT a.concerned_col.msisdn,
a.concerned_col.profile
FROM concerned_tab a;

Cheers