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
Oracle command in Datastage Sequence
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
Oracle command in Datastage Sequence
Tony
BI Consultant - Datastage
BI Consultant - Datastage
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
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..
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..
Last edited by TonyInFrance on Wed Apr 23, 2014 4:37 am, edited 1 time in total.
Tony
BI Consultant - Datastage
BI Consultant - Datastage
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.
Where 14 (guestimate) is the line number of the output you want returned.
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.
Code: Select all
echo "SELECT NVL(MAX(NUM_ITERATION),0)+1 AS ITE FROM TABLE WHERE DATE = '#PARAMETER#'; " | sqlplus -s user/pwd@dsn | tail -2 | head -1
The quotes around the #PARAMETER# may need some tweaking
Good Luck.
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
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;
!
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;
!
Soumya
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg