topic Re: How to use oracle sequence while insert/update in SAS DI 4.8 in SAS Data Management

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

etl_tool — Sun, 26 Apr 2015 02:27:50 GMT

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.

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

Patrick — Sun, 26 Apr 2015 21:01:13 GMT

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

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

etl_tool — Mon, 27 Apr 2015 02:06:47 GMT

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

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

etl_tool — Thu, 07 May 2015 13:02:34 GMT

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

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

LinusH — Thu, 07 May 2015 13:30:35 GMT

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

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

etl_tool — Thu, 07 May 2015 14:21:04 GMT

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

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

LinusH — Fri, 08 May 2015 05:39:56 GMT

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

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

Patrick — Sun, 10 May 2015 04:54:02 GMT

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.