You have some data, but not in your trusty Oracle Database yet.

How to get it there?

You have many options of course, but today I want to talk about a brand new one, Oracle SQL Developer Web (SDW).

Version 19.4 launched for all Oracle customers via our Oracle REST Data Services product. You can download and run this today for any of your on premise Oracle databases.

Quick shout-out to ORACLE-BASE: he ALREADY has a nice suite of videos and technical posts about getting started with SDW, so don’t miss those!

What I want to show you today is how to quickly take an existing CSV or Excel file and use it to create a NEW TABLE, and insert that data.

Getting started is as simple as a drag and drop

In the SQL Worksheet area of SDW, there’s a prominent panel on the bottom that practically BEGS you to give us some data to add to your database.

Once you start using this feature, you’ll see a log of imports listed here, but you scan still drag and drop files.

Do what it says with your mouse, or you can click the the little Cloud button in the toolbar next to the trashcan.

You’ll either get prompted for the file, or if you’ve dropped a file there, you’ll see a popup dialog with your data.

Does this look like the right data? If YES, click Next.

The little gear button allows you to make a few tweaks as to how we interact and interpret the data in your file –

Click the Gear button to access these properties.

The ‘Preview’ window defaults to 100 rows. That’s the amount of data we’ll take a peak at in order to ‘best guess’ how to shape your table on the next set of screens.

Hopefully you have a data model of sorts (in your head, at least!), and you KNOW what your column definitions should be.

The Most Important Part

You need to tell us how to store this data in your new table, which I’m calling ‘ACTIVITIES2.’ We’ll default the table name to the name of the input file.

We’ll default the column names in the table to the column names in the input file, taking are to add underscores. You can override anything you don’t like.

Don’t forget the Format Mask for your DATEs and TIMESTAMPs!

We’ll best guess the Format Mask for the temporal data types for you, but if we can’t manage it, you can enter your own. And I’ve had to do this for my Strava data here, actually.

If you switch over your VARCHAR2 data type to NUMBER, be sure to update the Precision. There’s a bug in 19.4 where we leave it as 4000 – that won’t work, so I’ve switched mine back to 38.

You can scroll right to see more of the 100 rows avail in the preview window to make sure things look ‘right.’

Click ‘NEXT’ to see what we’re going to do, before we actually do it.

Click Finish if you’re happy!

If you see something amiss, you can always go ‘Back’ and adjust. But we’re going to click Finish and see what happens.

It doesn’t take more than a second to actually array batch insert 500 rows, so it’s done by the time we print the dialog 🙂

A failed row, boo. Let’s see why.

If I click on that entry in the Data Loading log, I can get the details (as stored in the SDW$ERR$_ACTIVITIES2 TABLE)

Yeah, the database didn’t like that COMMA in the number value.

I can easily fix that data and do the IMPORT again manually, or I could do an INSERT as SELECT from that SDW$ table.

But I’m going to go LOOK at my data!

Notice that we automatically refresh the Worksheet browser list of TABLES, I can now see my ‘ACTIVITIES2’ table!

I’m trying to get back into shape this year. Sigh, don’t EVER get old/fat if you can help it!

SQL Developer Web in Autonomous

This IMPORT feature is only available for existing tables in our Autonomous Database Cloud Services, as SQL Developer Web hasn’t been upgraded to version 19.4 yet. That’s scheduled to happen ‘soon,’ so you’ll see this new feature appear there shortly.

SQL Developer Web, The Movie!

There’s a SQL Developer Web playlist with 4 videos and growing!

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