Results 1 to 2 of 2

Thread: syntax help2

  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: syntax help2

    I have created a table with a nested table and a ref. But am having trouble inserting into the table.
    can you help?



    CREATE TYPE Position_type AS object(
    Xpos int(4),
    Ypos int(4)
    );


    CREATE TYPE color_type as OBJECT (
    color VARCHAR2(10)
    );


    drop type text_box_type;

    CREATE TYPE text_box_type as OBJECT (
    text varChar2 (20),
    font varChar2 (10),
    text_size number (4),
    color color_type
    );

    /
    show errors;


    CREATE TYPE position_varray_type as varray(6) of position_type;

    /
    show errors;




    CREATE TYPE property_type as OBJECT (
    label VARCHAR2(10),
    value VARCHAR2(20)
    );

    /
    show errors;

    **********************************

    CREATE TYPE properties_type;
    /
    show errors;


    create type properties_ntabl_type AS TABLE OF properties_type;
    /
    show errors;

    CREATE OR REPLACE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,
    project REF project_type
    MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /









    CREATE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,
    project REF project_type
    )
    NOT FINAL;
    /

    CREATE TYPE properties_type as object(
    version NUMBER(6,2),
    isConcept VARCHAR2(10),
    resourceNo int
    );
    /
    show errors;



    CREATE TABLE shape of shape_type(primary key(id),
    foreign key (project) references project)
    nested table properties store as properties_nt(
    (primary key (nested_table_id, isConcept))
    organization index compress)
    return as locator;
    ************************************************** ****************





    CREATE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,

    project REF project_type,
    MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /
    show errors;



    CREATE or replace TYPE rectangle_type under shape_type (
    width number (4),
    height number(4),
    fillcolor color_type,
    OVERRIDING MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /
    show errors;



    CREATE OR REPLACE TYPE BODY shape_type AS
    MEMBER FUNCTION getShapeType RETURN VARCHAR2 IS
    result varchar2(20);
    BEGIN result := 'shape ' ;
    return result;
    END;
    END;
    /



    CREATE OR REPLACE TYPE BODY rectangle_type AS
    OVERRIDING MEMBER FUNCTION getShapeType RETURN VARCHAR2 IS
    result varchar2(20);
    begin
    result := 'rectangle ' ;
    return result;
    end;
    END;

    /
    show errors;



    I want to insert into this table using

    insert into shape select(rectangle_type
    (1,
    text_box_type
    ( 'Shape 1', 'Arial', 18, color_type( 'Red')
    ),
    position_type( 23,35 ),
    color_type('Blue'),
    properties_ntabl_type
    ( properties_type
    ( 2, 'Room', 23)
    ),
    ref (g),
    20,
    10,
    color_type( 'red')
    )
    )
    FROM project g
    WHERE g.project_id = 'pm123';


    /
    show errors;

    but then I get this error message.

    insert into shape select(rectangle_type
    *

    ERROR at line 1:
    ORA-04063: table "S4011335.SHAPE" has errors

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: syntax help2

    Originally posted by minnie1
    I have created a table with a nested table and a ref. But am having trouble inserting into the table.
    can you help?



    CREATE TYPE Position_type AS object(
    Xpos int(4),
    Ypos int(4)
    );


    CREATE TYPE color_type as OBJECT (
    color VARCHAR2(10)
    );


    drop type text_box_type;

    CREATE TYPE text_box_type as OBJECT (
    text varChar2 (20),
    font varChar2 (10),
    text_size number (4),
    color color_type
    );

    /
    show errors;


    CREATE TYPE position_varray_type as varray(6) of position_type;

    /
    show errors;




    CREATE TYPE property_type as OBJECT (
    label VARCHAR2(10),
    value VARCHAR2(20)
    );

    /
    show errors;

    **********************************

    CREATE TYPE properties_type;
    /
    show errors;


    create type properties_ntabl_type AS TABLE OF properties_type;
    /
    show errors;

    CREATE OR REPLACE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,
    project REF project_type
    MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /









    CREATE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,
    project REF project_type
    )
    NOT FINAL;
    /

    CREATE TYPE properties_type as object(
    version NUMBER(6,2),
    isConcept VARCHAR2(10),
    resourceNo int
    );
    /
    show errors;



    CREATE TABLE shape of shape_type(primary key(id),
    foreign key (project) references project)
    nested table properties store as properties_nt(
    (primary key (nested_table_id, isConcept))
    organization index compress)
    return as locator;
    ************************************************** ****************





    CREATE TYPE shape_type as OBJECT (
    id VARCHAR2(10),
    text_box text_box_type,
    position position_type,
    color color_type,
    properties properties_ntabl_type,

    project REF project_type,
    MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /
    show errors;



    CREATE or replace TYPE rectangle_type under shape_type (
    width number (4),
    height number(4),
    fillcolor color_type,
    OVERRIDING MEMBER FUNCTION getShapeType return VARCHAR2) NOT FINAL;
    /
    show errors;



    CREATE OR REPLACE TYPE BODY shape_type AS
    MEMBER FUNCTION getShapeType RETURN VARCHAR2 IS
    result varchar2(20);
    BEGIN result := 'shape ' ;
    return result;
    END;
    END;
    /



    CREATE OR REPLACE TYPE BODY rectangle_type AS
    OVERRIDING MEMBER FUNCTION getShapeType RETURN VARCHAR2 IS
    result varchar2(20);
    begin
    result := 'rectangle ' ;
    return result;
    end;
    END;

    /
    show errors;



    I want to insert into this table using

    insert into shape select(rectangle_type
    (1,
    text_box_type
    ( 'Shape 1', 'Arial', 18, color_type( 'Red')
    ),
    position_type( 23,35 ),
    color_type('Blue'),
    properties_ntabl_type
    ( properties_type
    ( 2, 'Room', 23)
    ),
    ref (g),
    20,
    10,
    color_type( 'red')
    )
    )
    FROM project g
    WHERE g.project_id = 'pm123';


    /
    show errors;

    but then I get this error message.

    insert into shape select(rectangle_type
    *

    ERROR at line 1:
    ORA-04063: table "S4011335.SHAPE" has errors
    ...assume this belong to Oracle section...

Posting Permissions

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