SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Contributor
Posts: 21

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

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.

Respected Advisor
Posts: 4,173

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

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

Contributor
Posts: 21

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

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

Contributor
Posts: 21

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

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

Super User
Posts: 5,441

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

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... :-(

Data never sleeps
Contributor
Posts: 21

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

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

Super User
Posts: 5,441

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

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

Data never sleeps
Respected Advisor
Posts: 4,173

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

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.

Ask a Question
Discussion stats
  • 7 replies
  • 1131 views
  • 2 likes
  • 3 in conversation