troubleshooting Question

How to adapt Import SQL from ora9i to work with a oracle 11g database.

Avatar of Vincent Costanza
Vincent CostanzaFlag for United States of America asked on
Oracle DatabaseProgramming
16 Comments1 Solution432 ViewsLast Modified:
The following sql script was used to import data from a simple csv file into oracle 9i database, in order to update scrap pricing. We have upgraded to oracle 11g, All the table names and spaces are the same name and place in the database but the routine does not work  with 11 no matter how I change it. Below is the original sql that works with the 9i data base..
 I would like to 1st have it work properly, 2nd have it only replace rows that exist in the CSV first column, and then if possible have it check the form of the CSV, meaning is there a valid commodity code in the first column and second are there 6 columns of prices on that row of the CSV.(SQL Below)>

--This script will change the values in Tier Pricing in Scrapware SV (by --Vincent Costanza)


CREATE OR REPLACE DIRECTORY xtern_Tierdata_dir AS 'Z:\EXCEL\PriceList\CSV\TierP';


REM ------Create External table to import Tier_Pricing.csv...

create table Tier_Pricing_xtern
       ( comm_id       NUMBER(10,0),
       Dealer       NUMBER(15,5),
         CommVdr      NUMBER(15,5),
         T1             NUMBER(15,5),
         T2             NUMBER(15,5),
         T3             NUMBER(15,5),
       T4             NUMBER(15,5)            
      )
       organization external
       (
            TYPE ORACLE_LOADER
            default directory xtern_Tierdata_dir
               access parameters
         (
            records delimited by newline
            badfile xtern_Tierdata_dir:'empxt%a_%p.bad'
              logfile xtern_Tierdata_dir:'empxt%a_%p.log'
           fields terminated by ','
            missing field values are null
            (
            comm_id, Dealer, CommVdr, T1, T2, T3, T4
            )
         )
         location ('Tier_Pricing.csv')  
     )
      PARALLEL
     REJECT LIMIT UNLIMITED;

REM -----Load External Table with CSV data...

select * from Tier_Pricing_xtern ;

REM Creat internal Temp Table...

CREATE TABLE Tier_P_Tmp AS SELECT * FROM TIER_PRICE;




ALTER TABLE Tier_P_Tmp
      ADD (      DE             NUMBER(15,5),
               Com            NUMBER(15,5),
               T1             NUMBER(15,5),
               T2             NUMBER(15,5),
               T3             NUMBER(15,5),
             T4             NUMBER(15,5));


REM copy data from external table to temp int table...

BEGIN
    FOR c IN (SELECT * FROM Tier_Pricing_xtern)
    LOOP
        UPDATE Tier_P_Tmp
           SET       DE = c.Dealer,
            Com = c.CommVdr,
            T1 = c.T1,
            T2 = c.T2,
            T3 = c.T3,
             T4 = c.T4
         WHERE comod_id = c.comm_id;
       
    END LOOP;
END;
/

Commit;



REM  Drop External Table...

Drop table Tier_Pricing_xtern;

REM  change date in temp table....seperate... method

UPDATE Tier_P_Tmp
   SET s_dt = TRUNC(SYSDATE), e_dt = ADD_MONTHS(TRUNC(SYSDATE),2);

commit;


rem Move and Add into Tier_Price_DTL

 MERGE INTO Tier_Price_Dtl t1
   USING (SELECT TPRC_ID, 'T1'      AS TPDT_CD, t1      AS price FROM Tier_P_Tmp UNION ALL
          SELECT TPRC_ID, 'T2'      AS TPDT_CD, t2      AS price FROM Tier_P_Tmp UNION ALL
          SELECT TPRC_ID, 'T3'      AS TPDT_CD, t3      AS price FROM Tier_P_Tmp UNION ALL
          SELECT TPRC_ID, 'T4'      AS TPDT_CD, t4      AS price FROM Tier_P_Tmp UNION ALL
          SELECT TPRC_ID, 'DE'  AS TPDT_CD, DE        AS price FROM Tier_P_Tmp UNION ALL
          SELECT TPRC_ID, 'COM' AS TPDT_CD, com AS price FROM Tier_P_TMP) x
      ON (    t1.TPRC_ID = x.TPRC_ID
          AND t1.TPDT_CD = x.TPDT_CD)
    WHEN MATCHED THEN
          UPDATE set t1.price = x.price
    WHEN NOT MATCHED THEN
          INSERT (TPRC_ID, TPDT_CD, price)
          VALUES (x.TPRC_ID, x.TPDT_CD, x.price);

commit;


REM --Update dates in Tier_Price    the aniquatited history tracking table...

BEGIN
    FOR c IN (SELECT * FROM Tier_P_Tmp)
    LOOP
        UPDATE Tier_Price
           SET  S_DT = c.S_DT,
              E_DT = C.E_DT
         WHERE TPRC_ID = C.TPRC_ID;

    END LOOP;
END;
/

commit;


REM -----Drop Tier_P_Tmp and clear....

Drop table Tier_P_Tmp;



exit
Tier-Pricing.csv
Tier-Price-Import.sql
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros