Problem solve Get help with specific problems with your technologies, process and projects.

Loading CSV file to an Oracle table

How do I load a 30 fields, character and numeric, CSV file to an Oracle table, and how do I predefine the table so that my loader works? Could you provide some examples?

You can create an external table based on the CSV file and view the data directly in your Oracle database. However,...

you should know what the datatype of these 30 fields are and the maximum lengths of these fields in the CSV file and define the table accordingly.

Secondly a database directory needs to be defined that is map to an operating system path where the CSV file will be resident. In my example I have the path setup as /home/fahmia/PLSQL/examples and have it mapped to A_DEFINED_DIRECTORY.

SELECT * FROM ALL_DIRECTORIES will provide you with that information. Please note: grant read, write on this directory must be executed to the schema where the predefined_table is going to be created.

create table predefined_table (field1  varchar2(provide lenght) (or number)), ...
          field30 varchar2(provide lenght) (or number)))
organization external
    type oracle_loader
    default directory A_DEFINED_DIRECTORY
    access parameters
       records delimited by newline
        badfile /home/fahmia/PLSQL/examples/test.bad'
        discardfile '/home/fahmia/PLSQL/examples/test.dis'
        logfile '/home/fahmia/PLSQL/examples/test.log'
        fields terminated by ","
        (  field1  varchar2(provide lenght) (or number)), ...
    field30 varchar2(provide lenght) (or number))       
     location ('/home/fahmia/PLSQL/examples/test.csv')
reject limit unlimited

Dig Deeper on Using Oracle PL-SQL