Results 1 to 9 of 9

Thread: Temp table

  1. #1
    Join Date
    Sep 2006
    Posts
    9

    Question Unanswered: Temp table

    Can anyone explain why I'm getting this error :

    SQL> CREATE GLOBAL TEMPORARY TABLE CUSTORD_TEMP
    2 ON COMMIT PRESERVE ROWS
    3 (CUSTNO NUMBER(4) PRIMARY KEY,
    4 FIRST_NAME VARCHAR(20),
    5 LAST_NAME VARCHAR(30),
    6 TOTAL_AMT NUMBER(6,2));
    (CUSTNO NUMBER(4) PRIMARY KEY,
    *
    ERROR at line 3:
    ORA-00922: missing or invalid option



    thanks in advance

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL> create global temporary table custord_temp
      2  (  custno          number(4) primary key,
      3     first_name      varchar(20),
      4     last_name       varchar(30),
      5     total_amt       number(6,2)
      6  )
      7  on commit preserve rows
      8  /
    
    Table created.
    
    SQL>

  3. #3
    Join Date
    Sep 2006
    Posts
    9

    Question

    Thanks a lot...it worked
    I have another error

    SQL> CREATE GLOBAL TEMPORARY TABLE CUSTORDTOT_TEMP
    2 (CUSTORD_NUM NUMBER(4) PRIMARY KEY,
    3 CUSTNO NUMBER(4) FOREIGN KEY,
    4 TOTAL_TAX NUMBER(4.2),
    5 TOTAL_SHIP NUMBER(3,2))
    6 CONSTRAINT custno_fk FOREIGN key
    7 ON COMMIT DELETE ROWS;
    CUSTNO NUMBER(4) FOREIGN KEY,
    *
    ERROR at line 3:
    ORA-00907: missing right parenthesis

    I can't see why I get this error....right parenthesis?

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    What is "CUSTNO NUMBER(4) FOREIGN KEY"?

    Did you mean

    Code:
    CREATE GLOBAL TEMPORARY TABLE CUSTORDTOT_TEMP
    ( CUSTORD_NUM  NUMBER(4) PRIMARY KEY
    , CUSTNO       REFERENCES customers
    , TOTAL_TAX    NUMBER(4,2)
    , TOTAL_SHIP   NUMBER(3,2) )
    ON COMMIT DELETE ROWS;
    or

    Code:
    CREATE GLOBAL TEMPORARY TABLE CUSTORDTOT_TEMP
    ( CUSTORD_NUM  NUMBER(4) PRIMARY KEY
    , CUSTNO       CONSTRAINT custordtot_cust_fk REFERENCES customers
    , TOTAL_TAX    NUMBER(4,2)
    , TOTAL_SHIP   NUMBER(3,2) )
    ON COMMIT DELETE ROWS;
    or perhaps

    Code:
    CREATE GLOBAL TEMPORARY TABLE CUSTORDTOT_TEMP
    ( CUSTORD_NUM  NUMBER(4) PRIMARY KEY
    , CUSTNO       INTEGER
    , TOTAL_TAX    NUMBER(4,2)
    , TOTAL_SHIP   NUMBER(3,2)
    , CONSTRAINT custordtot_cust_fk FOREIGN KEY (custno) REFERENCES customers )
    ON COMMIT DELETE ROWS;
    Unfortunately FK consttraints are not supported for global temp tables.

    ORA-14455: attempt to create referential integrity constraint on temporary table

    Cause: An attempt was made to create a referential integrity constraint on a temporary table. This is not supported.
    Action: Use triggers.

  5. #5
    Join Date
    Sep 2006
    Posts
    9
    you guys are the best...thanks

  6. #6
    Join Date
    Sep 2006
    Posts
    9

    Question ???

    Create Table Precious_metal_price
    (metal_id Number(4) References Metals,
    Price Number(9,3),
    Price_date_time Date,
    Time_between Timestamp Interval Day(2) To Second (2) ))
    Tablespace Users01
    Storage(initial 10m Next 5 Pctfree 0 Pctincrease 10 Maxextents 52k);

    missing right parenthesis

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that you're firing blind shots here. Doing so, you're very likely to fail to create a table. So, how about reading the manual? Especially CREATE TABLE statement?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why should he be bothered to RTFM when folks here will spoon feed him answers?
    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.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Vybez
    Time_between Timestamp Interval Day(2) To Second (2) ))
    So is time_between a timestamp or an interval?

Posting Permissions

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