DSXchange

Hello everyone,

I have this Oracle SELECT command which calculates the maximum value of a column and adds a 1 to it. I would like to call this in a Datastage Sequence job which I managed to do by first creating a .sql file containing the command.

My problem is however in filtering just the result and using the same. In the Datastage Director log I see:

Reply=0
Output from command ====>

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 17 10:30:33 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

old 1: SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE PERD_ARRT_INFO = '&1'
new 1: SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE PERD_ARRT_INFO = '201309'

ITE
----------
2

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


What I need is just the value 2

Anyone know of a function to do this?

I have done this in IBM DB2 and thus have EReplace and Convert functions in place but the same stubbornly refuses to work for Oracle and returns a lot of text.

Thanks guys

Tony

How are you calling sqlplus? Have you tried the "-s" command line option to run in 'silent' mode? I know I've done this in the past but I don't recall the gory details, I'll see if I can turn anything up on my PC.

Another option: since everything is returned in a dynamic array, you can test to see how many elements it has and then only take the last one.

The -s command helped a lot. Thanks Craig.

So to answer your question I was calling the command sqlplus -s in an Execute Command Activity in a sequence job. The SQL command itself is in a .sql file on the server and thus the execute command looks like:

sqlplus -s user/password@dsn @file.sql <parameter>
The sql statement is:

SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE DATE = '&1';

As you can guess I'm trying to extract the maximum value of the iteration number and increment the same by 1.

The output was much cleaner (I've named the column ITE) but I still had to format it and replace some characters. The output I got was:

Reply=0
Output from command ====>
old 1: SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE DATE = '&1'
new 1: SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE DATE = '201309'

ITE
----------
2

I then used EReplace(Trim(Convert(@FM,'', Recup_ITERATION.$CommandOutput)),'ITE---------- ','')[1] in order to get just the value 2 which is what I was after.

Thanks a lot for the ideas..

The entire command output (all the lines) are in the $CommandOutput variable for the stage. Using the field function you can get it to return just the "2". Add a User Variable stage and create a user variable that evaluates to field(stage.$CommandOutput,@FM,14).

Where 14 (guestimate) is the line number of the output you want returned.

I'd probably end up writting a routine so that I could use DSExecute() to run sqlplus and have full function access to parse out what I wanted from the returned values afterwards.

Glad you got it sorted out!

One could always create a re-usable routine using DS ODBC.H , but the one that created stopped working after a while , and moreover always gives a darned license warning error ( Thanks to Data Direct ) - even if its called by a DATASTAGE routine.

try using this in a command activity stage if you prefer it that way :
Use the Command.$Output from the command activity stage and remove the field mark using Convert() function in an user variable activity

The quotes around the #PARAMETER# may need some tweaking

Good Luck.

Can you please use the below format when you are setting up connection with oracle and executing the query.

sqlplus -s User_Id/Password@DB_Name<<!
SET HEADING OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
WHENEVER SQLERROR EXIT 1
SELECT 1 FROM DUAL;
EXIT;
!

Ramesh - That is wicked. Although my problem was sorted (and I marked this topic Resolved) I tried your solution. Its a bit more technical but works. The plus point is I need one .sql file less which makes my deliverable package a little easier.

Thanks

Tony