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.
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.
The little gear button allows you to make a few tweaks as to how we interact and interpret the data in your file –
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.
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.
If you see something amiss, you can always go ‘Back’ and adjust. But we’re going to click Finish and see what happens.
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)
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!
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.
