How to select from the object
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to select from the object

  1. #1
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    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

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width