Importing sequences from Oracle DB

LANDO · Post by **LANDO** » Mon Jan 30, 2006 3:06 am

Hello,

For a certain table I need to create a surrogate key.
I created a sequence in Oracle .... Now How can I import this sequence into DS ?
I tried doing it with : 'Select seq.nextval from dual' but that failed !
I donot want to have a DS Internal sequence as I do need this sequence accessible.

Thanks,

jzparad · Post by **jzparad** » Mon Jan 30, 2006 4:23 am

Lando,

I assume you're using an OCI lookup into a transformer. I've tried this method and it works ok for me. Please give more details of the error and how your job is set up.

LANDO · Post by **LANDO** » Mon Jan 30, 2006 5:02 am

jzparad,

this is the error message I get when I try to 'View_Data' :
'
DS_DIM_LANGUAGE..Oracle_OCI_15: ORA-08002: sequence SEQ_LANGUAGE.CURRVAL is not yet defined in this session
DS_DIM_LANGUAGE..Oracle_OCI_15.DSLink1: DSP.Open GCI $DSP.Open error -100.'
the job Is Set :
Oracle_source, Oracle_select_sequence(My problem)
Transformer
Oracle_target

chulett · Post by **chulett** » Mon Jan 30, 2006 9:08 am

I'm not sure you can actually 'View Data' with a structure like that. What happens when you try and run the job?

jzparad · Post by **jzparad** » Mon Jan 30, 2006 4:21 pm

Lando,

Craig's right.

What you're trying to do is similar to "select seq.currval from dual" in sqlplus and you will get the same ORA-08002 error unless you have done a "select seq.nextval from dual" in the same session. When you view the data in DataStage you are creating a new session. Hence the error.

LANDO · Post by **LANDO** » Tue Jan 31, 2006 2:14 am

Hi All,

You are both absolutly right !!!

The Job run perfectly well and indeed I cannot view the Data with the 'View Data' button.

Thanks for the help, I learned alot about DS from this one