Table drop & create
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Table drop & create

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Table drop & create

    I have a simple SQL script that drops a table and then creates is new. When I run it I get the following error:

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production


    Table dropped.


    Table created.

    CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object


    I queried ALL_OBJECTS table and this table did not exist in any other schema. What could be the problem?


    Here's the script in case you want to see it:

    WHENEVER SQLERROR CONTINUE;

    DROP TABLE TMS_MASTER.SFI_TMS_GBC_ITP;

    WHENEVER SQLERROR EXIT SQL.SQLCODE;

    CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
    AS
    SELECT sol.load_id load_id
    , to_char(trunc(a.creation_date),'MM/DD/YYYY') inv_create_date
    , i.INV_NBR
    , sol.ORD_ID
    , loc1.NAME ship_from
    , loc2.NAME ship_to
    , r.source_warehouse SRC_WHS
    , r.source_location SRC_LOC
    , r.destination_warehouse DEST_WHS
    , r.destination_location DEST_LOC
    , round((sum(sol.ord_line_weight)/l.weight) * ic.amount,2) AMOUNT
    FROM ord@pnetwork o
    , load@pnetwork l
    , LOCATION@pnetwork loc1
    , LOCATION@pnetwork loc2
    , fp_invoice@pnetwork i
    , fp_invoice_cost@pnetwork ic
    , fp_shipment_ord_ln@pnetwork sol
    , sis_master.cyc_date_future d
    , ap_invoices_all@popm a
    , sfi_xfer_routes@popm r
    WHERE o.group_name = 'ITP'
    AND o.source_id = loc1.ID
    AND o.dest_id = loc2.ID
    AND o.source_id = '01004886020' -- GREEN BAY CREAMERY PLANT LOCATION
    AND o.id = sol.ord_id
    AND sol.line_freight_key = '502100' -- ITP FREIGHT - BULK CHEESE
    AND r.load_id = SUBSTR(sol.load_id,1,11)
    AND l.bill = sol.LOAD_ID
    AND ic.cost_qualifier = 8 -- 8 = TOTAL COST
    AND i.INVOICE_ID = ic.INVOICE_ID
    AND i.inv_bol = substr(a.description,23,12)
    AND a.description like '%TMS%'
    AND sol.load_id = i.INV_BOL
    AND a.invoice_num = i.INV_NBR
    AND trunc(a.creation_date) >= d.prev_prd_begin_greg -- this part grabs records where the creation date falls
    AND trunc(a.creation_date) <= d.prev_prd_end_greg -- between the begin and end dates of the previous period,
    AND trunc(d.cal_day_greg) = trunc(sysdate) -- based on the current date
    GROUP BY sol.load_id, to_char(trunc(a.creation_date),'MM/DD/YYYY'), i.INV_NBR, sol.ORD_ID
    , l.weight, ic.amount, loc1.NAME, loc2.NAME, r.source_warehouse, r.source_location
    , r.destination_warehouse, r.destination_location;

    /

    exit;

  2. #2
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    You are creating the table twice here in your script.
    Code:
    WHENEVER SQLERROR CONTINUE;
    
    DROP TABLE TMS_MASTER.SFI_TMS_GBC_ITP;
    
    WHENEVER SQLERROR EXIT SQL.SQLCODE;
    
    CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
    AS
    SELECT sol.load_id load_id 
    , to_char(trunc(a.creation_date),'MM/DD/YYYY') inv_create_date
    , i.INV_NBR
    , sol.ORD_ID
    , loc1.NAME ship_from 
    , loc2.NAME ship_to
    , r.source_warehouse SRC_WHS
    , r.source_location SRC_LOC
    , r.destination_warehouse DEST_WHS
    , r.destination_location DEST_LOC
    , round((sum(sol.ord_line_weight)/l.weight) * ic.amount,2) AMOUNT 
    FROM ord@pnetwork o 
    , load@pnetwork l 
    , LOCATION@pnetwork loc1 
    , LOCATION@pnetwork loc2 
    , fp_invoice@pnetwork i 
    , fp_invoice_cost@pnetwork ic 
    , fp_shipment_ord_ln@pnetwork sol
    , sis_master.cyc_date_future d 
    , ap_invoices_all@popm a
    , sfi_xfer_routes@popm r
    WHERE o.group_name = 'ITP' 
    AND o.source_id = loc1.ID 
    AND o.dest_id = loc2.ID 
    AND o.source_id = '01004886020' -- GREEN BAY CREAMERY PLANT LOCATION
    AND o.id = sol.ord_id 
    AND sol.line_freight_key = '502100' -- ITP FREIGHT - BULK CHEESE
    AND r.load_id = SUBSTR(sol.load_id,1,11)
    AND l.bill = sol.LOAD_ID 
    AND ic.cost_qualifier = 8 -- 8 = TOTAL COST
    AND i.INVOICE_ID = ic.INVOICE_ID 
    AND i.inv_bol = substr(a.description,23,12)
    AND a.description like '%TMS%'
    AND sol.load_id = i.INV_BOL 
    AND a.invoice_num = i.INV_NBR
    AND trunc(a.creation_date) >= d.prev_prd_begin_greg -- this part grabs records where the creation date falls 
    AND trunc(a.creation_date) <= d.prev_prd_end_greg -- between the begin and end dates of the previous period, 
    AND trunc(d.cal_day_greg) = trunc(sysdate) -- based on the current date
    GROUP BY sol.load_id, to_char(trunc(a.creation_date),'MM/DD/YYYY'), i.INV_NBR, sol.ORD_ID
    , l.weight, ic.amount, loc1.NAME, loc2.NAME, r.source_warehouse, r.source_location
    , r.destination_warehouse, r.destination_location;
    
    / -------   take this out as this runs the create stmt again
    
    exit;

  3. #3
    Join Date
    Aug 2003
    Posts
    100
    You are correct! Thanks

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    I think you can also have the driving_site hint, since I can see lots of dblink
    Cheers!
    OraKid.

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