7 Replies Latest reply: Jan 11, 2013 2:48 AM by odie_63 RSS

    Inserting modified XML format data in DB

    937360
      Hi,
      I have to read and insert data in database received in following file format :

      000001~ROH~04012013~000002~000003
      D<SrlNo>1</SrlNo><COL1>R1_C1_DATA</COL1><COL2>YYY</COL2><COL3>100</COL3>
      D<SrlNo>2</SrlNo><COL1>R2_C1_DATA</COL1><COL2>NY</COL2><COL3>16736</COL3>
      D<SrlNo>3</SrlNo><COL1>R3_C1_DATA</COL1><COL2>Y</COL2><COL3>67</COL3>


      1st line is header, rest 3 lines are detail records.

      Please HELP !

      Regards,
      Rohit Chaudhari
      rohit1304@live.com
        • 1. Re: Inserting modified XML format data in DB
          odie_63
          Hi,

          Database version, table structure?

          Since the file is not XML, we can't use conventional methods.
          An external table might be the solution in this case.
          • 2. Re: Inserting modified XML format data in DB
            937360
            Hi,
            Thanks for replying.

            Database : Oracle10g, tabl;e structue is same as input detail record (all fields varchar).
            After taking this file in external table how to go ahead with delimiting the data in desired columns ?
            • 3. Re: Inserting modified XML format data in DB
              odie_63
              After taking this file in external table how to go ahead with delimiting the data in desired columns ?
              It's all explained in the documentation : http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_params.htm
              CREATE TABLE load_xt (
                srlno varchar2(30)
              , col1  varchar2(30)
              , col2  varchar2(30)
              , col3  varchar2(30)
              ) 
              ORGANIZATION EXTERNAL (
                TYPE ORACLE_LOADER 
                DEFAULT DIRECTORY test_dir
                ACCESS PARAMETERS (
                  RECORDS DELIMITED BY NEWLINE
                  SKIP 1
                  FIELDS (
                    dummy char(1)
                  , srlno char(30) ENCLOSED BY "<SrlNo>" AND "</SrlNo>"
                  , col1  char(30) ENCLOSED BY "<COL1>"  AND "</COL1>"
                  , col2  char(30) ENCLOSED BY "<COL2>"  AND "</COL2>"
                  , col3  char(30) ENCLOSED BY "<COL3>"  AND "</COL3>"
                  ))
               LOCATION ('test.dat')
              )
              REJECT LIMIT UNLIMITED;
              SQL> select * from load_xt;
               
              SRLNO           COL1           COL2         COL3
              --------------- -------------- ------------ -----------
              1               R1_C1_DATA     YYY          100
              2               R2_C1_DATA     NY           16736
              3               R3_C1_DATA     Y            67
               
              • 4. Re: Inserting modified XML format data in DB
                937360
                Hi,
                ABove query fired successfully. But while executing the select query, I am getting this error :

                ------------
                ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                ORA-29400: data cartridge error
                KUP-04063: unable to open log file LOAD_XT_13893942.log
                OS error No such file or directory
                ORA-06512: at "SYS.ORACLE_LOADER", line 19

                View program sources of error stack.
                -------------

                Can you please help...
                • 5. Re: Inserting modified XML format data in DB
                  odie_63
                  Did you change the default directory to one of your own?
                  • 6. Re: Inserting modified XML format data in DB
                    937360
                    Yes, I replaced the default directory name with mine.
                    • 7. Re: Inserting modified XML format data in DB
                      odie_63
                      OK.

                      How about the file location?
                      Does the database have access rights to the directory/file?

                      I've tested the example on version 10.2.0.5.
                      What's your exact db version? (SELECT * FROM $version)