I don’t have SQLcl yet, DOWNLOAD it!

Question from the ‘comments’ today:

I would like to have a parameter for dataload WITHOUT column names having to be in the 1st line.

Simply load the columns in the order they are in the txt/csv file.

Often delivered or generated txt files have no header information at all and you still know how to handle them (you know the target table AND the source file structures and that they must match.)

No joke, I really have such use cases and have (yet) to “construct” the first line (column names) by concat a “default” file and the data file. ugly…

a customer…

When I get these questions, I just love being able to say, “we can in fact to just that!”

Our TABLE

If we are going to load data, we’re going to need a table. Let’s build a quick-n-dirty copy of HR.EMPLOYEES.

CREATE TABLE emps_no_headers2
    AS
        SELECT
            employee_id,
            first_name,
            last_name,
            salary
        FROM
            employees
        WHERE
            1 = 2;

Our new table looks like this –

I truncated the table after I did the CTAS…accidentally w/o the WHERE clause, hence the STATS.

To load CSV, we’re going to need CSV. Let’s generate the CSV from our existing EMPLOYEES table.

CSV export, no column headers!

Now the default behavior when using our command to load delimited data is to treat the first line of the incoming data as the list of COLUMN headers for the table, and use that to map which items in the data being streamed goes to which column in the table being populated by SQLcl.

The command is called LOAD.

LOAD has two sets of options:

  • set loadformat – how the data being imported will be processed
  • set load – what and how the load operation will actually take place

We want to tell SQLcl to NOT expect column headers in our CSV, so we’re going to use ‘set loadformat’.

Your Load Options

You can use help set loadformat to get help…setting your load formats.

Hi friends in Europe! I know what you are thinking. You can change the delimiter(;) !

Want some help? Just ask for it…

Yes Virginia, there is an UNLOAD command too.

Loading the table

No mess, no fuss. Just works.

Let’s do a trick! Let’s take a CSV and get DDL for it.

Maybe you don’t have a table yet, you JUST have a CSV file. And THIS CSV file does have column headers. Let’s see what SQLcl 21.3 can do with that.

So I have a CSV. And I WANT a table from that. I can use the GUI of course. But we’re DEVS, no mouse, no mouse!

Image
load table tablename filename show_ddl

We’ll scan the data, look for max column widths, date formats, etc, and rename columns that aren’t legal/valid for an Oracle schema. That ‘show_ddl’ bit is new for 21.3, and says, just show me what you WOULD do, without actually doing it.

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

3 Comments

  1. Avatar

    Hey,

    just a remark – indirectly linked to the SQLcl too – would like to have the possibility to see private temporary tables in connection tree and to import data into those.

    As of now, I have to choose the option that creates the script with insert statements, which has to be tweaked with the correct/wanted PTT table name to insert data into the private temporary table.
    I need this many times – I get a .csv file, need to import data into a PTT and do some checks/DML against other tables. As this is the prime use scenario for private temporary tables, it would be nice if it is directly supported.

    Kind regards, Aleksander

  2. Avatar

    Hi Jeff,

    I was just trying to load a CSV file into a table with the new SQLcl, but I ran into a problem because there are a couple of “not null” columns in the table that are populated by either sequences or triggers and SQLcl does not seem to want to let me omit those columns from the CSV file. I can’t find a “SET LOAD” parameter to turn that behavior off.

    #ERROR Omitted columns must be nullable

    Thanks.

    • thatjeffsmith

      You need to have your table (if using IDENTITY) defined such that the column is populated on null…so SQLcl submits nothing for the column NULL, and the db sees that and fires the trigger to do the new value. Similar scenario here…BY DEFAULT ON NULL

Write A Comment

RSS
Follow by Email
LinkedIn
Share