Oracle Sequence object

[JasonDiVirgilio]

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

[deleted_user]

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]

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]

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]

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]

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]

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]

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]

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]

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 **;