DATA Step, Macro, Functions and more

Oracle Sequence object

Reply
Contributor
Posts: 46

Oracle Sequence object

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: ~!@#$%^&*()_+
N/A
Posts: 0

Re: Oracle Sequence object

Posted in reply to JasonDiVirgilio
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.
Contributor
Posts: 46

Re: Oracle Sequence object

Posted in reply to deleted_user
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);
SAS Super FREQ
Posts: 8,866

Re: Oracle Sequence object

Posted in reply to JasonDiVirgilio
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
Contributor
Posts: 55

Re: Oracle Sequence object

Posted in reply to Cynthia_sas
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
Regular Contributor
Posts: 167

Re: Oracle Sequence object

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.

Contributor
Posts: 46

Re: Oracle Sequence object

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.

N/A
Posts: 0

Re: Oracle Sequence object

Posted in reply to JasonDiVirgilio
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;
Frequent Contributor
Posts: 139

Re: Oracle Sequence object

Posted in reply to JasonDiVirgilio
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
Contributor
Posts: 46

Re: Oracle Sequence object

Posted in reply to darrylovia
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 **;
Ask a Question
Discussion stats
  • 9 replies
  • 2207 views
  • 0 likes
  • 6 in conversation