1 2 Previous Next 16 Replies Latest reply: Aug 7, 2012 9:27 AM by Hulk RSS

    Importing Data from CSV File

    Hulk
      Dear Friends,

      I have already created the directory DIR1.

      I am getting the below warning message

      1 CREATE OR REPLACE PROCEDURE P2
      2 is
      3 BEGIN
      4 create table ext_table_csv1 (
      5 i Number,
      6 n Varchar2(20),
      7 m Varchar2(20)
      8 )
      9 organization external (
      10 type oracle_loader
      11 default directory DIR1
      12 access parameters (
      13 records delimited by newline
      14 fields terminated by ","
      15 missing field values are null
      16 )
      17 location ('file.csv')
      18 )
      19 reject limit unlimited;
      20* END P2;
      QL> /

      Warning: Procedure created with compilation errors.

      QL> show err
      rrors for PROCEDURE P2:

      INE/COL ERROR
      ------- -----------------------------------------------------------------
      /1 PLS-00103: Encountered the symbol "CREATE" when expecting one of
      the following:
      begin case declare exit for goto if loop mod null pragma
      raise return select update while with <an identifier>
      <a double-quoted delimited-identifier> <a bind variable> <<
      close current delete fetch lock insert open rollback
      savepoint set sql execute commit forall merge pipe

      Please help me.
        • 1. Re: Importing Data from CSV File
          Venkadesh Raja
          for creating table inside the pl sql block.you have to use Dynamic Sql

          http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

          try this untested
          CREATE OR REPLACE PROCEDURE P2
           is
           BEGIN
           execute immediate 'create table ext_table_csv1 (
           i Number,
           n Varchar2(20),
           m Varchar2(20)
           )
          organization external (
           type oracle_loader
           default directory DIR1
           access parameters (
           records delimited by newline
           fields terminated by ","
           missing field values are null
           )
           location ('file.csv')
           )
           reject limit unlimited';
           END P2;
          • 2. Re: Importing Data from CSV File
            ShankarViji
            Hi NGK,

            You cannot create a Table inside a anonymous PL\SQL block. Unless, you use Dynamic SQL.

            You can Import data from CSV File to Oracle table using:

            1.SQL * Loader.
            2. External Table Concepts.

            Please view the link below.

            External Tables

            And, {message:id=9360002}

            Thanks,
            Shankar

            Edited by: Shankar Viji on Aug 2, 2012 11:27 PM
            • 3. Re: Importing Data from CSV File
              Hulk
              I have used dynamic sql and created
              create or replace
              PROCEDURE TEST_PROC
              IS

              BEGIN
              EXECUTE IMMEDIATE 'create table ext_table_csv (i Number, n Varchar2(20), M Varchar2(20))
              ORGANIZATION EXTERNAL
              (type oracle_loader
              default directory ADMIN1
              access parameters (
              records delimited by newline
              fields terminated by ","
              missing field values are null
              )
              location ("file.csv")
              )
              reject limit unlimited';


              END TEST_PROC;
              The code got complied but throwing error while running

              Connecting to the database NIKHIL.
              ORA-00905: missing keyword
              ORA-06512: at "NIKHIL.TEST_PROC", line 5
              ORA-06512: at line 2
              Process exited.
              Disconnecting from the database NIKHIL.
              • 4. Re: Importing Data from CSV File
                BluShadow
                Why are you trying to create a table (external table or otherwise) inside a procedure? That is not how to design databases.

                Database objects shouldn't be created at run-time, they should be part of the database design, created once and then used within the application.

                What's the problem you're trying to solve that is making you want to create it at run-time? If it's because the file name changes then you can change the file name of an external table using the ALTER TABLE command, changing the LOCATION attribute, through an execute immediate statement.

                You error is probably because you have put a blank line between the IS and the BEGIN and you're creating it through SQL*Plus or something like that... but rather than worry about that, let's sort out the real issue you're trying to solve.
                • 5. Re: Importing Data from CSV File
                  Hulk
                  My actual problem is that, I need to create a procedure for Importing Data from CSV file.
                  My front End is Dotnet ,where from the code this procedure is called when we press Import button.
                  Please can you explain me with an example how can I do that.
                  • 6. Re: Importing Data from CSV File
                    BluShadow
                    NGK246 wrote:
                    My actual problem is that, I need to create a procedure for Importing Data from CSV file.
                    My front End is Dotnet ,where from the code this procedure is called when we press Import button.
                    Please can you explain me with an example how can I do that.
                    So why is that a problem.

                    You have the external table defined on the database pointing to your CSV file (You do realise that the database can only read files on the database server, don't you? It can't read files from your local client machine).

                    The when your .net application calls a procedure on the database, that procedure just queries the external table to insert the data, using a simple INSERT ... SELECT ... statement.
                    • 7. Re: Importing Data from CSV File
                      Hulk
                      If at all I want to fetch data from another CSV file (say 'test20101214.txt') to the External table defined then I just need to
                      alter the location of the CSV pointing to the table as shown below...
                      execute immediate 'alter table ext_test location(''test'||to_char(sysdate-1,'YYYYMMDD')||'.txt'')';
                      And then use INSERT .. SELECT for pumping data into my actual table residing on my database.

                      Please correct me if I am wrong.
                      • 8. Re: Importing Data from CSV File
                        BluShadow
                        NGK246 wrote:
                        If at all I want to fetch data from another CSV file (say 'test20101214.txt') to the External table defined then I just need to
                        alter the location of the CSV pointing to the table as shown below...
                        execute immediate 'alter table ext_test location(''test'||to_char(sysdate-1,'YYYYMMDD')||'.txt'')';
                        And then use INSERT .. SELECT for pumping data into my actual table residing on my database.

                        Please correct me if I am wrong.
                        Yep, that's pretty much it.
                        • 9. Re: Importing Data from CSV File
                          Hulk
                          I am now trying to create the EXTERNAL TABLE.

                          step-1:I have created the directory in my database server and gave alias name as ADMIN1.

                          step-2:Granted read and write access to that directory.

                          step-3
                          create table ext_table_csv (i Number, n Varchar2(20), M Varchar2(20))
                          ORGANIZATION EXTERNAL
                          (type oracle_loader
                          default directory ADMIN1
                          access parameters (
                          records delimited by newline
                          fields terminated by ","
                          missing field values are null
                          )
                          location ('file.csv')
                          )
                          reject limit unlimited;

                          step-4: desc ext_table_csv;
                          Name Null Type
                          ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          I NUMBER
                          N VARCHAR2(20)
                          M VARCHAR2(20)

                          3 rows selected

                          step-5:
                          select * from ext_table_csv;

                          Error report:
                          SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                          ORA-29400: data cartridge error
                          KUP-04063: unable to open log file EXT_TABLE_CSV_2072_10248.log
                          OS error The system cannot find the file specified.
                          ORA-06512: at "SYS.ORACLE_LOADER", line 19
                          29913. 00000 - "error in executing %s callout"
                          *Cause:    The execution of the specified callout caused an error.
                          *Action:   Examine the error messages take appropriate action.

                          Please let me know what could be the problem.
                          • 10. Re: Importing Data from CSV File
                            BluShadow
                            NGK246 wrote:
                            Error report:
                            SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                            ORA-29400: data cartridge error
                            KUP-04063: unable to open log file EXT_TABLE_CSV_2072_10248.log
                            OS error The system cannot find the file specified.
                            ORA-06512: at "SYS.ORACLE_LOADER", line 19
                            29913. 00000 - "error in executing %s callout"
                            *Cause:    The execution of the specified callout caused an error.
                            *Action:   Examine the error messages take appropriate action.

                            Please let me know what could be the problem.
                            It's an operating system error, which indicates that, on your server, the user that is running the oracle processes (typically the one Oracle was installed as - and this will differ on Windows and Unix) doesn't have access to the directory that your directory object is pointing at, or only has read permission on that directory and therefore can't create the necessary log file.
                            • 11. Re: Importing Data from CSV File
                              Hulk
                              I am convienced with the explaination.

                              Action Done by me.
                              I have connected as admin SYSTEM/SYS
                              adn gave read and write access the user who is trying to create.

                              Please correct me if I am wrong .

                              But the user is again getting the same Error.

                              Please let me know what action I need to take on this.
                              • 12. Re: Importing Data from CSV File
                                BluShadow
                                NGK246 wrote:
                                I am convienced with the explaination.

                                Action Done by me.
                                I have connected as admin SYSTEM/SYS
                                adn gave read and write access the user who is trying to create.

                                Please correct me if I am wrong .

                                But the user is again getting the same Error.

                                Please let me know what action I need to take on this.
                                Yes, what you've done sounds fine.
                                But has the Oracle user ( not the schema user) on the physical server, got operating file system permissions to read and write to that physical directory?
                                • 13. Re: Importing Data from CSV File
                                  Hulk
                                  Yes... the Oracle User on the physical server got operating file system permissions to read and write to that physical directory.
                                  I can confirm that because he had place the file in that location and he is able to make changes and save.
                                  • 14. Re: Importing Data from CSV File
                                    Hulk
                                    I am giving you the sequence of steps I have implemented for better reference.

                                    I am using NIKHIL schema.

                                    I have connected as admin SYSTEM/SYS and gave grant permission to create any directory to NIKHIL.

                                    Connected back to NIKHIL.

                                    create or replace directory EXTDIR AS 'F:\DIR';
                                    directory created.

                                    I have connected as admin SYSTEM/SYS and gave read and write access the user who is trying to create.
                                    grant read, write on directory EXTDIR to NIKHIL;

                                    Connected back to NIKHIL.

                                    select OWNER from dba_directories where directory_name='EXTDIR';

                                    OWNER
                                    ------------
                                    SYS


                                    why is it showing SYS when I have created from NIKHIL.

                                    Will this be the reason for creating that problem since external table is created in NIKHIL.
                                    1 2 Previous Next