Subscribers and my mom will probably remember that I’ve briefly talked about this feature before, but it was really just a tease. I wanted to go into a bit more detail today.

In version 21.3, SQLcl got a wicked cool new feature. And yes, it sounds pandering of me to say that, but every now and then I see new features come out that turn out to be JUST as handy as we imagined them when we set out to build them.

This is one of those feature I’ll probably toss out in every Tips & Tricks talk I do going forward.

You have: a delimited text file or simply a CSV.

This is my personal data dump from the social media app known as Untappd.

Putting that data into a table.

The Old-School Way

This still works, of course.

But, it’s a wizard, has multiple steps, and I don’t have SQLDev started, and I’m already at my prompt, ready to go, NOW.

The New-School Way

The LOAD command has a new parameter you can toss onto a job, ‘NEW’.

This looks promising!

So what does this command do? Well, we scan your data from the CSV, we look at the column headers to come up with new column names, and then we look at the data itself – how wide are the strings, is that a DATE format we recognize, etc.

Then we show you that DDL, execute it, and then load the data from the CSV into the new table.

Which looks a LITTLE something like this –

The output keeps going…this is just the first bit showing me the load options I have going.

The most interesting thing of note here is this:

scan_rows 1000

That’s a LOAD command option telling SQLcl to look at the first 1,000 rows of my CSV to ‘measure’ the column widths and dates to build the DDL/INSERTs around.

If you have wider data past the first 50 or 100 rows, you’ll get a lot of REJECTED INSERTs.

SET LOAD SCAN_ROWS 1000

Note the higher you set this, the more resources you’ll burn reading the data and doing the number crunching.

So, I don’t need to do anything really, I can just toss my CSV at SQLcl, and let it put it into a table for me.

Here’s a quick animation…

Create and Load the table, collect stats, INFO+, and a little reporting query of my new data to play with.

Or maybe I just want the propose table DDL…

SHOW DDL, what’s that?

So go through the 1000 rows, and figure out the DDL, but just SHOW it to me, don’t execute it.
load newtable file.csv SHOW_DDL 
If you add the ‘NEW’ onto the command, it will actually execute the scenario.
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.

Write A Comment

RSS
Follow by Email
LinkedIn
Share