Originally posted by: SystemAdmin

Hi all,

Is there a way to get the next sequence number back from Oracle in the form of an integer? I'm trying to select the next value in a sequence from an Oracle database, using the following SQL:

select BATC_BATCID_PK_SEQ.nextval AS NextBatchID from dual

Database Designer generates a tree with a single binary object in it. Likewise when I run the query in a map, I get a binary object back. It would make my life much easier if it was an integer when it arrived in DataStage. I'm running DSTX 8.0.1 on Windows XP for Development. Any idea how to pull this off?

Thanks,
Jesse
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Of course, the moment I posted this it came to me. It works when it is:

select to_char(BATC_BATCID_PK_SEQ.nextval) AS NextBatchID from dual
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

Or you can do it through a Function.

Create fucntion get_nextval(next_val out varchar2 ) is
begin
select to_char(secuence.next_val) into next_val from dual;
return next_val;
end;

so you have defined the type at the output of the function as Varchar2 ( string of text )

Message was edited by: Kepa
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: janhess

You could also use dblookup and leave everything under the control of the SQL.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender