BookmarkSubscribeRSS Feed
JasonDiVirgilio
Quartz | Level 8
If I have a dataset that I want to use to append to an Oracle table (using Proc Append), and the oracle table uses a sequence object to create primary key values, how do I populate the rows in my dataset with the Oracle sequence values that Oracle requires prior to appending to the table (or can it be done during the load ?)

I'm trying to avoid using Oracle sqlldr if I can. Message was edited by: ~!@#$%^&*()_+
9 REPLIES 9
deleted_user
Not applicable
Unfortunately, I left most of my Oracle reference materials behind in the last job 2 years ago.

But, I think you should not use Proc Append, but use PROC SQL instead, using pass-through code:
[pre]
proc sql;
connect to Oracle (...);

...

disconnect from oracle;
quit;
[/pre]

In that way, Oracle is in control of the append and should/may create the primary key. If I had my Oracle SQL reference books, I could be of more help. As it is, I would have to go Online to find the information at Oracle, which you can do just as easily.

In any case, I think this is more of an Oracle question than a SAS question.
JasonDiVirgilio
Quartz | Level 8
But does Proc SQL allow me to select the transaction data from a SAS dataset and at the same time access the Oracle connection to select values from the Oracle sequence object?

I didn't think something like the following is allowed (or I can't seem to make it work):

insert into oraclelib.my_oracle_table (select seq.nextval,
col1,
col2,
from unixlib.my_sas_dataset
where x=y);
Cynthia_sas
SAS Super FREQ
Hi:
You might want to consult the SAS/Access to Oracle documentation and look at some more examples of INSERT. There is a BULKLOAD option that may be relevant in this situation.

http://support.sas.com/kb/25/447.html
http://support.sas.com/kb/9/184.html
http://support.sas.com/kb/19/884.html

For more help with your particular situation, data and SAS/Acess-wise, you might consider contacting SAS Tech Support.

cynthia
JMarkW
Fluorite | Level 6
Cynthia,

I know how to use dual to obtain the next value of a Oracle column with individual selects. The technique is very slow for high volume batch processing.

Do you have an example of a bulkload with the nextval being accomplished in the bulkload instead of a singleton call to dual? Message was edited by: JMarkW
Sheeba
Lapis Lazuli | Level 10

Hi,

I would like to know how to connect to dual from sas to obtain next value.....

I tried

proc sql;
connect to oracle as oralib ( path='XXXX' authdomain="XXXX");

    create table seq as select * from connection to oralib (

   SELECT s_id.nextval as seqval
     
      from dual    );

    
disconnect from oralib;
    quit;

I am getting the following error ERROR: ORACLE prepare error: ORA-02289: sequence does not exist. SQL statement: SELECT s_id.nextval as seqval from dual.

Please guide on how to proceed in this regard.

JasonDiVirgilio
Quartz | Level 8

The "sequence does not exist" error usually is telling you that the Oracle sequence (s_id) either has not been created or the user ID you are using to access it doesn't have permission in Oracle to access it. I don't see anything wrong with your Proc Sql code.

deleted_user
Not applicable
1)If you did not use SAS, how would you load data into the Oracle table? Would you really access the Oracle sequence number first? Or would it automatically be assigned? I don't know, I'm just asking.

2) Yes, you can mix access, somewhat, in proc sql try something like
[pre]
proc sql noprint;
connect to oracle ( %our_oracle );

insert into oraclelib.my_oracle_table
( select a.key, b.col1, b.col2
from unixlib.my_sas_data as b, connection to oracle (select seq.nextval key) as a
where b.x = y
);

disconnect from oracle;
quit;
darrylovia
Quartz | Level 8
Unfortunately, I don't think SAS/ACCESS for ORACLE supports directly what you want to do. If you were using SQL Server you could use option in your libname statement IGNORE_READ_ONLY_COLUMNS which enables a sql insert into a table with an identity column.

But, you could do a 2 pronged approach to do this.

First, I would create global temporary table in oracle and insert your SAS table into it using proc sql without the pass through. See Temporary Table Support for Oracle in the online documenation for specifics.

Finnally, you can use a sql pass-through to insert the global temporary table with your target table.


As with Chuck I don't remember my oracle too well. But I attempted to put a template on what to do below.



* code template;

libname ora oracle user=???? pw=???? path=??? connection=shared; /* shared lets you do a temp table in the data base*/

data ora.temp_my_sas_dataset;
set unixlib.my_sas_dataset
where x=y;
run;

/* via sql-passthrougth */
proc sql;
connect to Oracle (user=???? pw=???? path=??? );
execute( insert into myschema.my_oracle_table select *from temp_my_sas_dataset);

disconnect from oracle;
quit;

-Darryl
JasonDiVirgilio
Quartz | Level 8
Firstly, thanks to everyone for the great ideas.

I went with an approach similar to darrylovia's idea, just without the temp table. I got the number of sequence values I would need to insert (numobs) and created a temp dataset with those values. I added a rownum column that is just a sequential number starting at 1 and going to numobs. I added that same rownum column to my transaction data so I could merge the Oracle Sequence values with the transactions...

I still want to try Chuck's approach...if I get time !!

** loop from 1 to number of sequence values I need. **;
%if &numobs > 0 %then %do;
%do i = 1 %to &numobs;

proc sql;
connect to oracle as connect1 (
user = &username
password = &password
path = "&db"

);

** get an Oracle sequence value **;
create table seq as select * from connection to connect1 (
select my_sequence_object.nextval as seqval
from dual
);

quit;

** append the Oracle sequence value to a dataset. **;
data vseqs;
length rownum 8.;
set vseqs
seq;
rownum=_n_;
run;
%end;
%end;

**Then merge by rownum **;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5431 views
  • 0 likes
  • 6 in conversation