04-25-2015 10:27 PM
I'm joining a sybase table and a sas data set to insert/update data using a table loader in oracle. The table ID needs to be loaded using a sequence which resides in oracle. Can you please let me know how to use an oracle sequence to generate a ID column? I tried adding a dummy column to the same oracle table joined it to the result set and tried specifying seqname.nextval in a expression editor but getting an error:
ERROR: Unresolved reference to table/correlation name seqname.
04-26-2015 05:01 PM
The approach I would take:
- Join the Sybase & SAS table creating a SAS WORK table.
- Bulk-load this SAS Work table into an Oracle staging table (either a global temporary table or simply a new Oracle table).
- What I would do: First drop the staging table (just in case) using Proc Datasets with NOLIST NOWARN, then create the table using explicit SQL pass-through, then bulk-load into the table using a table loader.
- Load the Oracle staging table into the existing target Oracle table using pass-through SQL (the SQL EXECUTE transformation). Loading Tables
- Drop the staging table (using Proc Datasets).
04-26-2015 10:06 PM
Thanks partick. So there is no easy way to do it using just SAS Datasets and transformations? We can do that easily using other ETL tools like talend and Informatica
05-07-2015 09:30 AM
I used the IGNORE_READ_ONLY_COLUMNS libname option when we had a similar issue with SQL Server.
But unfortunately, it's available on all DBMS ACCESS except Oracle... :-(
05-07-2015 10:21 AM
Thanks Linus. So if you select that option , how does it read the sequence from SQL server. Just for knowledge sake.
05-08-2015 01:39 AM
As I recall it, when we did a append /insert libname style it ignored the seq Id column (which was defined with a automatic constraint ).
05-10-2015 12:54 AM
That's the approach I would have taken as well because it allows you to bulk-load into the staging table.
I've played now a bit with the sequence object so here the options I found working:
- Fully implement the sequencing in the database by using a before insert trigger. If done so then even a proc append works (you need to set options nowarn/force as you won't pass in the key field from source).
- Use pass-through SQL as you've done it.
- Query the sequence object from within SAS to retrieve the current value for LAST_NUMBER, then increment the key within SAS and populate the key field on the source side, then ALTER the sequence object to set LAST_NAME to the new key value - and load the source table into Oracle.