Solved

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

Posted on 2014-11-11
16
318 Views
Last Modified: 2014-11-24
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
0
Comment
Question by:vpciii
16 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40436889
but the routine does not work  with 11 no matter how I change it
So, where exactly is the problem?! Do you hit any errors when you "run the script"?!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40437365
That syntax is all valid in Oracle11.  My guess is that the problem is on the first line:
CREATE OR REPLACE DIRECTORY xtern_Tierdata_dir AS 'Z:\EXCEL\PriceList\CSV\TierP';

Is this path (Z:\EXCEL\PriceList\CSV\TierP) valid on your new server?  If not, that is the problem.  Change that path to something that is valid on your Oracle11 server.

If that path is valid, does this file: "Tier_Pricing.csv" exist in that directory?  If not, copy it there.  Then, there is one other thing to check.

Which Oracle user are you trying to run these commands with?  Does that user account have the privileges needed in Oracle11 to create a directory?  I would suggest that a program that will be used multiple times should not include a "create or replace directory ..." step.  Rather, that task should be done just once by a DBA, and the program can simply use it each time (assuming that the Oracle user who will run these commands has been granted read and write privileges on this logical directory.)
0
 

Author Comment

by:vpciii
ID: 40438282
ALEX and MARGer,
YES the path is valid.. I have manually filled in the table twice, takes about an hour, and for some reason when I run the routine it wipes the table and I have to re-enter manually....

BELOW is the log from the last time I tried it:

Z:\EXCEL\PriceList\CSV\TierP>sqlplus scrapware/recycle@veronica @Z:\EXCEL\PriceList\CSV\TierP\Tier_Price_Import.sql  

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 3 23:06:09 2014

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Directory created.


Table created.

select * from Tier_Pricing_xtern
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file E:\SHILOH\IMPORTS\TierP\empxt000_2064_6956.log



Table created.


Table altered.

BEGIN
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file E:\SHILOH\IMPORTS\TierP\empxt000_2064_6956.log
ORA-06512: at line 2



Commit complete.


Table dropped.


121 rows updated.


Commit complete.


726 rows merged.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


Table dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438305
>>CREATE OR REPLACE DIRECTORY xtern_Tierdata_dir AS 'Z:\EXCEL\PriceList\CSV\TierP';


>>error opening file E:\SHILOH\IMPORTS\TierP\empxt000_2064_6956.log

Seems there is an issue with the directory being created and what the external table is using.

One has:'Z:\EXCEL\PriceList\CSV\TierP'

The output shows: E:\SHILOH\IMPORTS\TierP
0
 

Author Comment

by:vpciii
ID: 40438750
YES that error is confusing because I don't see where its setting up that location for the log file.

is that some kind of variable or setting in the database?

" E:\SHILOH\IMPORTS\TierP\empxt000_2064_6956.log "
Does not exist on this server, but did on the old one, I guess I could create it, thoughts???

I used to use a query to find all the set directories like the "Dump Dir" for instance...
select * from all_objects where object_type ='DIRECTORY';

is there a way to find what is pointing to this one and change it?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438768
It is being set here:
 logfile xtern_Tierdata_dir:'empxt%a_%p.log'

and here if not specified:  default directory xtern_Tierdata_dir

So, something isn't right.  

>>select * from all_objects where object_type ='DIRECTORY';

That is one way but there is also a DBA_DIRECTORIES view.

Tripple check the value for xtern_Tierdata_dir
0
 

Author Comment

by:vpciii
ID: 40438774
See query results, below, last line, there is a setting but how do I confirm its current setting?
How do I change it to what I want it to be?


SQL> select * from all_objects where object_type ='DIRECTORY';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
                                    12689                DIRECTORY
30-MAR-10 09-JUN-13 2013-06-09:05:12:54 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            DATA_PUMP_DIR
                                    12764                DIRECTORY
30-MAR-10 09-JUN-13 2013-06-09:05:12:54 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            XMLDIR
                                    57134                DIRECTORY
30-MAR-10 30-MAR-10 2010-03-30:10:29:37 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            DATA_FILE_DIR
                                    73306                DIRECTORY
09-JUN-13 09-JUN-13 2013-06-09:05:10:50 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            LOG_FILE_DIR
                                    73309                DIRECTORY
09-JUN-13 09-JUN-13 2013-06-09:05:10:50 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            MEDIA_DIR
                                    73310                DIRECTORY
09-JUN-13 09-JUN-13 2013-06-09:05:10:50 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            SS_OE_XMLDIR
                                    74449                DIRECTORY
09-JUN-13 09-JUN-13 2013-06-09:05:11:09 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            SUBDIR
                                    74490                DIRECTORY
09-JUN-13 09-JUN-13 2013-06-09:05:11:15 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            DUMP_FILES1
                                   127904                DIRECTORY
02-NOV-14 02-NOV-14 2014-11-02:22:54:20 VALID   N N N          9



OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            XTERN_TIERDATA_DIR
                                   128114                DIRECTORY
03-NOV-14 03-NOV-14 2014-11-03:23:09:33 VALID   N N N          9



10 rows selected.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438777
>>a setting but how do I confirm its current setting?

Looks like we cross posted.  Query DBA_DIRECTORIES.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:vpciii
ID: 40438824
Would it be safe to run this?:
CREATE DIRECTORY XTERN_TIERDATA_DIR AS 'Z:\EXCEL\PriceList\CSV\TierP';
Would it have to be run each time, or just once?
Or should I use and create a new Directory?
0
 

Author Comment

by:vpciii
ID: 40438833
SQL> select * from all_objects where object_type ='DBA_DIRECTORIES';

no rows selected
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438837
>>Would it have to be run each time, or just once?

Just once.

>>select * from all_objects where object_type ='DBA_DIRECTORIES';

Not all_objects:
select * from DBA_DIRECTORIES;

If you don't have DBA access:
select * from ALL_DIRECTORIES;
0
 

Author Comment

by:vpciii
ID: 40438865
From the results below It seems to be set correctly... not sure why the log file returned what it did on 11/03/14.
I only have the production server, so I am afraid to run it again becausec last time it deleted all the data in the entire internal table.

SQL> select * from DBA_DIRECTORIES;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------

SYS                            XTERN_TIERDATA_DIR
Z:\EXCEL\PriceList\CSV\TierP

SYS                            DUMP_FILES1
S:\SW-Dump

SYS                            SUBDIR
F:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------

SYS                            SS_OE_XMLDIR
F:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\

SYS                            LOG_FILE_DIR
F:\oracle\product\11.2.0\dbhome_1\demo\schema\log\

SYS                            DATA_FILE_DIR
F:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------

SYS                            XMLDIR
c:\ade\aime_dadvfh0169\oracle/rdbms/xml

SYS                            MEDIA_DIR
F:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\

SYS                            DATA_PUMP_DIR
F:\oracle/admin/veronica/dpdump/


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------

SYS                            ORACLE_OCM_CONFIG_DIR
F:\oracle\product\11.2.0\dbhome_1/ccr/state


10 rows selected.
0
 

Author Comment

by:vpciii
ID: 40438884
JUST another thought..   Z:\EXCEL\PriceList\CSV\TierP is a mapped location, is that a problem for oracle..
The old server I used a local drive and shared it out..
I was trying to be more secure, using a file server for the CSV to be written buy non admin users...
I am the only one that runs the import SQL
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40438895
I would not run this in production until you have everything worked out on a test system.

The reason the data is 'gone' is likely because of one of the drops.  Not sure what table the data is 'missing' from but still NEVER a good idea to play in production.

I would also look at rewriting this depending on how much data is involved.  Read up on GLOBAL TEMPORARY TABLES.  They should be able to replace Tier_P_Tmp.

The directory XTERN_TIERDATA_DIR looks correct.  Not sure why the log shoes the E drive.

What is the date/time stamp on the log file?  Are you looking at a the correct log for the correct run?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40438902
>>Z:\EXCEL\PriceList\CSV\TierP is a mapped location, is that a problem for oracle..

Can easily be the problem.

Oracle runs as the SYSTEM Windows account (Check who runs the Oracle database service).  The SYSTEM account needs to have: Read, Write and Read/Execute.  Possibly Modify but I cannot remember for sure.

Remember:  Who you are logged into the machine as doesn't matter.  It is who Oracle is running as that matters.

For what its worth:  This is 'fixed' in 12c since you finally create a specific user for Oracle to own/run everything.
0
 

Author Closing Comment

by:vpciii
ID: 40463832
I am not finished building the lab for to test this, when I try it I will repost if this doesnt help..
Thanks,
Vincent
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now