Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2010
    Posts
    13

    Unanswered: Sql issue please look

    --------------------------------------------------------
    -- File created - Wednesday-March-24-2010
    --------------------------------------------------------
    --------------------------------------------------------
    -- DDL for Type CLIENT
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "CLIENT" UNDER person
    (
    addressLine1 VARCHAR2(200),
    addressLine2 VARCHAR2(200),
    aTown VARCHAR2(200),
    contactTel1 VARCHAR2(200),
    contactTel2 VARCHAR2(200),
    maxPrice NUMBER,
    desiredArea VARCHAR2(200),
    attends appointments_list
    )


    /

    --------------------------------------------------------
    -- DDL for Type PROPERTY
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "PROPERTY" AS OBJECT
    (
    property_id NUMBER,
    dor DATE,
    type VARCHAR2(200),
    bedrooms NUMBER,
    receptionRooms NUMBER,
    bathrooms NUMBER,
    garage NUMBER,
    garden NUMBER,
    regionArea NUMBER,
    pAddressLine1 VARCHAR2(200),
    pAddressLine2 VARCHAR2(200),
    pTown VARCHAR2(200),
    price NUMBER,
    appointments appointments_list
    )


    /

    --------------------------------------------------------
    -- DDL for Type SALESPERSON
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "SALESPERSON" UNDER person
    (
    jobTitle VARCHAR2(200),
    makes appointments_list
    )


    /

    --------------------------------------------------------
    -- DDL for Type APPOINTMENT
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "APPOINTMENT" AS OBJECT
    (
    appointment_id NUMBER,
    date_time DATE,
    type VARCHAR2(200),
    levelOfIntrest VARCHAR2(200),
    offerMade NUMBER,
    salesMan REF salesperson,
    applicant REF client,
    aProperty REF property
    )


    /

    --------------------------------------------------------
    -- DDL for Type APPOINTMENTSREFTABLE
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "APPOINTMENTSREFTABLE" AS TABLE OF REF appointment;


    /

    --------------------------------------------------------
    -- DDL for Type PERSON
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "PERSON" AS OBJECT
    ( id NUMBER,
    surname VARCHAR2(100),
    forename VARCHAR2(100),
    dob DATE,
    MAP MEMBER FUNCTION getId RETURN NUMBER
    ) NOT FINAL;
    /
    CREATE OR REPLACE TYPE BODY "PERSON" AS
    MAP MEMBER FUNCTION getId RETURN NUMBER IS
    BEGIN
    RETURN id;
    END;
    END;


    /

    --------------------------------------------------------
    -- DDL for Type APPOINTMENTS_LIST
    --------------------------------------------------------

    CREATE OR REPLACE TYPE "APPOINTMENTS_LIST" as table of ref appointment;


    /


    --------------------------------------------------------
    -- DDL for Table APPOINTMENT_TAB
    --------------------------------------------------------

    CREATE TABLE "APPOINTMENT_TAB" OF "APPOINTMENT"
    ;
    --------------------------------------------------------
    -- DDL for Table PERSON_TAB
    --------------------------------------------------------

    CREATE TABLE "PERSON_TAB" OF "PERSON"

    NESTED TABLE TREAT(SYS_NC_ROWINFO$ AS "CLIENT")."ATTENDS" STORE AS "SYSNTQiy4WPqCQ4CVeghgOntvVA=="
    RETURN AS VALUE
    NESTED TABLE TREAT(SYS_NC_ROWINFO$ AS "SALESPERSON")."MAKES" STORE AS "SYSNTLqsrjbDgS8OjqiM85DSCzQ=="
    RETURN AS VALUE;
    --------------------------------------------------------
    -- DDL for Table PROPERTY_TAB
    --------------------------------------------------------

    CREATE TABLE "PROPERTY_TAB" OF "PROPERTY"

    NESTED TABLE "APPOINTMENTS" STORE AS "APPOINTMENTS_NEST_TAB"
    RETURN AS VALUE;

    ---------------------------------------------------
    -- DATA FOR TABLE PROPERTY_TAB
    -- FILTER = none used
    ---------------------------------------------------
    REM INSERTING into PROPERTY_TAB

    ---------------------------------------------------
    -- END DATA FOR TABLE PROPERTY_TAB
    ---------------------------------------------------

    ---------------------------------------------------
    -- DATA FOR TABLE APPOINTMENT_TAB
    -- FILTER = none used
    ---------------------------------------------------
    REM INSERTING into APPOINTMENT_TAB
    Insert into APPOINTMENT_TAB (APPOINTMENT_ID,DATE_TIME,TYPE,LEVELOFINTREST,OFFE RMADE,SALESMAN,APPLICANT,APROPERTY) values (1,to_timestamp('10-MAY-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'Business','HIGH',4000,null,null,null);
    Insert into APPOINTMENT_TAB (APPOINTMENT_ID,DATE_TIME,TYPE,LEVELOFINTREST,OFFE RMADE,SALESMAN,APPLICANT,APROPERTY) values (2,to_timestamp('10-MAY-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'Business','HIGH',4000,null,null,null);
    Insert into APPOINTMENT_TAB (APPOINTMENT_ID,DATE_TIME,TYPE,LEVELOFINTREST,OFFE RMADE,SALESMAN,APPLICANT,APROPERTY) values (3,to_timestamp('10-MAY-10 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'Business','HIGH',4000,null,null,null);

    ---------------------------------------------------
    -- END DATA FOR TABLE APPOINTMENT_TAB
    ---------------------------------------------------

    ---------------------------------------------------
    -- DATA FOR TABLE PERSON_TAB
    -- FILTER = none used
    ---------------------------------------------------
    REM INSERTING into PERSON_TAB
    Insert into PERSON_TAB (ID,SURNAME,FORENAME,DOB) values (32,'Sam','C.M',to_timestamp('10-MAY-70 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));

    ---------------------------------------------------
    -- END DATA FOR TABLE PERSON_TAB
    ---------------------------------------------------

  2. #2
    Join Date
    Mar 2010
    Posts
    13
    Sorry, if anyone could please look at that and see if it is ok. I seem to be getting errors.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I seem to be getting errors.
    ERRORS? What Errors?
    I don't see any errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2010
    Posts
    13
    When I run this I get errors

  5. #5
    Join Date
    Jan 2010
    Posts
    18
    Quote Originally Posted by mrempes87 View Post
    When I run this I get errors
    Show us the errors.

  6. #6
    Join Date
    Mar 2010
    Posts
    13

    Pics showing errors etc

    Hi,

    here are screenshots.

    The first just shows how I have the connection working (different usernames provide the same result for errors)


    The other screenshot shows the errors I am getting.


    These errors happen when I run the code that you can see in the top of this thread.

    Any guidance as to what is wrong with my code or what I need to change in it.

    Just anything. Please.
    Attached Thumbnails Attached Thumbnails Connection Working.png   Error Messages.png  

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I guess I need new glasses, because all I see is blurry text.

    Too bad CUT & PASTE is broken for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2010
    Posts
    13
    Hang on, sorting now.

    Sorry

  9. #9
    Join Date
    Mar 2010
    Posts
    13
    Error: PL/SQL: Compilation unit analysis terminated
    Error(10,11): PLS-00201: identifier 'APPOINTMENTS_LIST' must be declared
    Error(16,15): PLS-00201: identifier 'APPOINTMENTS_LIST' must be declared
    Error(4,9): PLS-00201: identifier 'APPOINTMENTS_LIST' must be declared


    Those are the messages I get when I run the code.

  10. #10
    Join Date
    Feb 2005
    Posts
    57
    Wouldn't it be better to create Type APPOINTMENTS_LIST before you try to use it?

  11. #11
    Join Date
    Mar 2010
    Posts
    13

    Now it is working?

    Now all the sudden its working and my tables and types are showing up properly?

    I dont understand why.


    Can somebody else please run that code.


    Iv been tinkering with it using oracle sql developer, oracle 10g, and oracle 10g express on win xp sp3 32 bit


    thanks

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Before you use a user defined type, you have to create it first before it is used in the create.

    I just noticed that outrider beat me to it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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