Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35

    Red face Unanswered: Newbie doing data convertion

    Hi to all experts there.

    I have problem regarding the data convertion. Now i need to do data convertion. Cause by client change the old system to new system.
    For the new system, they use Oracle Database.
    Now , i have all the records (from old system) , and i also have new table structure (new system).
    My problem are :
    1) what is the tool need i prepare to do the data convertion ? Is it
    i) ODBC dirver ? Which version ?
    ii) SQL plus in which version ? What is the command ?

    2) Anything tool that i miss ?

    3) If i have all the tool (mentioned above ),how to do it ?


    Can you all experts there teach me step by step .
    Starting from
    i) How to collect the data from old system , and store it in what file ?

    ii) After successfully collect the data , then how to convert it into new database (Oracle Database)

    Thanks in advance.

    regards,
    natalie koo

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Newbie is NOT doing data convertion
    Newbie is hoping somebody else will do Newbie's job.
    The actual solution might depend upon:
    1) Old OS name & version
    2) Old database name & version
    3) Oracle version
    all of which were not included.
    My suggestion is that the client should find somebody who knows more about Oracle than only how to spell it.
    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.

  3. #3
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35
    Hi, most expertest anacedent,
    I very appreciate your reply message. Thank you for your valuable advise.
    1) "Newbie is NOT doing data convertion"
    2) "Newbie is hoping somebody else will do Newbie's job."

    If you not willing to help somebody , so please don't waste your golden time to entertain a "newbie'.

    Since you are a expert, but ended out , you still can't solve the posted problem. So are you use for ?

    Let me advise you back, this is not a good way to teach a newbie , even newbie don't know anyting.

    best regrads,
    natalie

  4. #4
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24
    Hi, newbie....
    actually i'm agree with the anacedent....
    Newbie is NOT doing data convertion
    Newbie is hoping somebody else will do Newbie's job.
    u r hoping that someone will teach u everything...step by step....
    this is not a forum to teach u step by step...
    u can find all the solution of ur problem in www.oracle.com
    no offense...
    ----------------------------------------------------------
    the same answer like anacedent...
    The actual solution might depend upon:
    1) Old OS name & version
    2) Old database name & version
    3) Oracle version
    which u should know what kind of oracle ur client use....
    if ur client using oracle 10g Beta version.....
    there's a driver(OracleXE) 4 ODBC which u just setup it in ODBC conf

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please check out the following link. oracle has a number of tools and whitepapers on migration. However, I would agree with the other people that your company should hire a consultant part time to do the conversion.

    http://www.oracle.com/technology/tec...ion/index.html
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Natalie,
    At least, tell us what the old database, post some table structure, your questions are too much generic to answer them.

    Saludos,
    DKG.

  7. #7
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35
    Hi, to all experts there,
    I had try to do the data conversion (even i'm a newbie),

    The scenario is :
    1) my client using oracle 10g
    2) my table structure
    eg. db_master
    db_dbcd varchar2(10)
    db_name varchar2(50)
    db_addr varchar2(200)
    db_tel varchar2(20)
    db_cretrm varchar2 (10)
    db_crelimit number(12,4)
    db_remark varchar2(300)
    credate date
    moddate date

    Now, i have old data (from old database) all are in text file. Then i do the following step :
    1) using microsoft excel to open it and save as dbf file. (DBF 4 dBASE IV)
    file name called : dbtormaster.dbf

    2) setup the ODBC conf
    To create a new database source , from tab system DNS, i select 'Microsoft dBase driver (*.dbf)', i not sure is it i select the correct driver or not.
    Then in ODBC database setup i fiill in
    Data Source Name: dbtor_master
    Decription : dbf
    Version : dBase IV

    3) I login to SQLPlus
    USER = DBF
    PASSWORD = DBF
    HOST STRING =ODBCBF

    COPY FROM DBF/DBF@ODBCBF TO client1/PASS@sale CREATE dbtor_master USING SELECT * dbtor_master;

    4) I login to SQLPlus again
    USER = client1
    PASSWORD = pass
    HOST STRING =sale

    CREATE TABLE dbtor_master1 AS SELECT * FROM db_master;

    insert into dbtor_master1 (db_dbcd,db_name,db_addr,db_tel,db_cretrm,db_creli mit,db_remark,credate,moddate)
    select rtrim(CODE),RTRIM(name),RTRIM(add),tel,cretrm,crel imit,remark,sysdate,syadate) from dbtor_master


    INSERT INTO db_master
    SELECT * FROM dbtor_master1


    Is it correct ? Pls. all expert there advice me, if i did wrong.
    Thanks in advance.
    regards,
    natalie

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why so complicated. If your old data is in a flat file, simply use sqlldr and load it directly into your new tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Natalie, what beilstwh is trying to say is that if you save the data on excel as a "Comma Separated Values", it's really easy to import it to Oracle using SQL*Loader.

    First, save the data in data.csv

    Then, create a file import.ctl:
    Code:
    LOAD DATA APPEND INTO TABLE dbtor_master1
    (
     db_dbcd                           terminated by ','
    ,db_name                    	terminated by ','
    ,db_addr                      terminated by ','
    ,db_tel                     terminated by ','
    ,db_cretrm                 	terminated by ','
    ,db_crelimit             terminated by ','
    ,db_remark                  terminated by ','
    ,credate                  terminated by ','
    ,moddate                  terminated by ','
    )
    You'll have to create the table in Oracle (in case you don't have it yet).

    After that, run this command:
    Code:
    sqlldr userid=<user>/<passwd> control=import.ctl data=data.csv log=import.log
    That will take care of importing all data in the file into the table.

    Good luck!

  10. #10
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35
    Hi,
    Thanks DKG n Beilstwh, i will try to do it.
    Btw , when i login to sqlplus (i had try use ODBC ,before receive your message to advice me using SQL Loader) i had encounter the error message:

    1) my SQLPLUS version:
    Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
    PL/SQL Release 8.1.5.0.0 - Production
    CORE Version 8.1.5.0.0 - Production
    TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production

    2) when I login to SQLPlus
    USER = DBF
    PASSWORD = DBF
    HOST STRING =ODBCBF

    Then the error message show as below :
    ERROR:
    ORA-03121: no interface driver connected - function not performed.

    Is it i setup a wrong ODBC conf ? or it cause by other things ?

    Below is my setup ODBC conf step :
    setup the ODBC conf
    To create a new database source , from tab system DNS, i select 'Microsoft dBase driver (*.dbf)', i not sure is it i select the correct driver or not.
    Then in ODBC database setup i fiill in
    Data Source Name: dbtor_master
    Decription : dbf
    Version : dBase IV

    Thanks again to u all experts there ,whereby try to understand my question and give a valueable advice. Thanks a lof.

    regards,
    natalie

  11. #11
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35
    sorry i mistyping.
    HOST STRING =ODBCBF change to HOST STRING =ODBCBF

    regards,
    natalie

Posting Permissions

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