How to use oracle sequence while insert/update in SAS DI 4.8

[etl_tool]

Hi ,

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.

[Patrick]

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).

[etl_tool]

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

[etl_tool]

I took an approach of creating staging tables and then inserting or updating using SQL execute transformation.

[LinusH]

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... 😞

[etl_tool]

Thanks Linus. So if you select that option , how does it read the sequence from SQL server. Just for knowledge sake.

[LinusH]

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 ).

[Patrick]

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.