Greetings,
I'm on SAS 9.4 and trying to use OR_IDENTITY_COLS to Use Oracle Sequence when loading a table. The documentation @ https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n17merknv57i0un1vhszwcwbzab9.htm&doc... is vague.
Has anyone used this. If so, can you provide an example
Thanks
SAS Tech Support provided the answer:
Note: That if you're using another Oracle schema's sequence, you need to add it as follows.
'{Schema Name Here}.TEST_SEQ.NEXTVAL'
Here is an example:
DATA TEST_IMPORT;
INPUT LOC_NM $;
DATALINES;
EIN
BVN
TST
RUN;
*NWDWLIB is a macro variable with the string of connecting to one of our Oracle DBs;
LIBNAME &NWDWLIB;
PROC SQL;
INSERT INTO NWDWLIB.TEST_LOC
(OR_IDENTITY_COLS=(DW_TEST_LOC_ID='TEST_SEQ.NEXTVAL'),DW_TEST_LOC_ID,LOC_NM)
SELECT ., LOC_NM
FROM WORK.TEST_IMPORT;
QUIT;
LIBNAME NWDWLIB CLEAR;
One for tech support I reckon. I couldn't find a syntax that worked as intended.
HI Chris,
Thanks for your opinion.
I was able to get it to work syntactically. However, the sequence # didn't flow through to the Oracle table.
I'll contact my company's tech support rep and update this thread accodringly.
Thanks again.
Troy
SAS Tech Support provided the answer:
Note: That if you're using another Oracle schema's sequence, you need to add it as follows.
'{Schema Name Here}.TEST_SEQ.NEXTVAL'
Here is an example:
DATA TEST_IMPORT;
INPUT LOC_NM $;
DATALINES;
EIN
BVN
TST
RUN;
*NWDWLIB is a macro variable with the string of connecting to one of our Oracle DBs;
LIBNAME &NWDWLIB;
PROC SQL;
INSERT INTO NWDWLIB.TEST_LOC
(OR_IDENTITY_COLS=(DW_TEST_LOC_ID='TEST_SEQ.NEXTVAL'),DW_TEST_LOC_ID,LOC_NM)
SELECT ., LOC_NM
FROM WORK.TEST_IMPORT;
QUIT;
LIBNAME NWDWLIB CLEAR;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.