run oracle sql in routine

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

run oracle sql in routine

Post by abyss »

calling a routine job in sequential job, routine code as following:

Code: Select all

OracleCmd= "echo ": pExecSql :"; | sqlplus -S ": pOraUser :"/": pOraPass :"@": pOraDsn
Call DSExecute("NT", OracleCmd, Output, SystemReturnCode)
Call DSLogInfo("Sql Result: " : Output, "SQL")
Line=Field(Output,@FM,4)
Ans=Line
pExecSql parameter is for passing sql statement in to routine, when i run routine in test window i can not pass single quotation in to where condition, either command will fail or syntax error.

for example:
if i run this statement:

Code: Select all

SELECT max(to_date("'"01-01-1900 00:00:00"'", "'"DD-MM-RRRR HH24:MI:SS"'")) FROM <table_name> t WHERE ROWNUM = 1
or
SELECT to_char(MAX(MAX_UPDATE_RECORD_TIME), "'"DD-MM-RRRR_HH24:MI:SS"'") from BATCH_RUN_TABLE WHERE ETL_BATCH_NAME = "'BATCH_NAME'"  and ETL_BATCH_RUN_STATUS = "'Completed'"
it will success and return a date value

if i run:

Code: Select all

SELECT MAX(t.update_date_time) FROM sr_mrkdb.STUDENT_CLASSES_HISTORY t WHERE t.update_date_time >= to_date("'"01-jan-1900"'")
or
SELECT MAX(t.update_date_time) FROM sr_mrkdb.STUDENT_CLASSES_HISTORY t WHERE t.update_date_time >= to_date(: "'" : '01-01-1900 00:00:00' : "'" : ', ' : "'" : 'DD-MM-RRRR_HH24:MI:SS' : "'" : )
or
SELECT MAX(t.update_date_time) FROM sr_mrkdb.STUDENT_CLASSES_HISTORY t WHERE t.update_date_time >= to_date("'"01-01-1900 00:00:00"'" , "'"DD-MM-RRRR_HH24:MI:SS"'")
none of these will return any value, but syntax should be correct (at least for the part before where statement)

i read couple threads:
viewtopic.php?p=283199
viewtopic.php?p=280909
viewtopic.php?p=354481

none of them really solve the problem, does anyone know to to deal with it? or is there anyway to get around this?

thanks
Howard
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm curious if you tried anything in those other threads or did you just read them? Oracle doesn't like double-quotes and you typically preserve single quotes by enclosing them in single quotes, which one of the linked topics mentions. Or sometimes you need four for each one! There is also syntax to define a custom escape character.

Failing that, perhaps this topic helps if DataStage is removing them rather than Oracle:

viewtopic.php?t=153572
-craig

"You can never have too many knives" -- Logan Nine Fingers
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

i set that env variable value, still can not run oracle statement in routine :(

Code: Select all

SELECT to_char(MAX(MAX_UPDATE_RECORD_TIME), 'DD-MM-RRRR_HH24:MI:SS') from UOW_LOOKUP.ETL_BATCH_RUN WHERE ETL_BATCH_NAME = 'BATCH_NAME'  and ETL_BATCH_RUN_STATUS = 'Completed'
I spent whole day on it....
can you please explain how to use escape character?

viewtopic.php?p=280909
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So, what exactly happens when the routine runs that last SQL example? Is it just that you are losing the single quotes or is something else going on? If you have anything from the log showing specifically what it tried to run, that would be best.
-craig

"You can never have too many knives" -- Logan Nine Fingers
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

thanks
i can make the query above work now, i change the query to:

Code: Select all

'Select to_char(MAX(MAX_UPDATE_RECORD_TIME), ' : pTimestampFormatRn : ') from ETL_BATCH_RUN WHERE ETL_BATCH_NAME = ': pBatchNameRn : ' and ETL_BATCH_RUN_STATUS = ': pBatchStatusName 

parameter value: 
pTimestampFormatRn : "'"DD-MM-RRRR HH24:MI:SS"'"
pBatchStatusName: "'completed'"
pBatchNameRn:      "'BATCH_NAME'"
The sql i am still struggling to run is:

Code: Select all

SELECT MAX(t.update_date_time) FROM source_table t WHERE t.update_date_time >= to_date('<value from previous routine', 'DD-MM-RRRR HH24:MI:SS')
all i can see from log of the routine is:

Code: Select all

checkdate..JobControl (SQL): Sql Result: 
checkdate..JobControl (@GetMax): Routine DSU.srnSQLPlus did not finish OK, return code = ''
which doesn't give me any information at all.

the problem is if i call a function in where clause and contains quotation markm the sql wouldn't execute
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You'd have to code the routine to write the OracleCmd to the log yourself for it to show up, using DSLogInfo from what I recall.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Difficult knowing how to help without knowing if the quotes are being stripped (and by which layer) or if something else is going on. Any chance your DATE string doesn't match the format mask or that you captured to the job's log the actual ORA error?

And out of an abundance of curiosity, have you tried sending something like this:

Code: Select all

SELECT MAX(t.update_date_time) FROM source_table t WHERE t.update_date_time >= to_date('''<value from previous routine''', '''DD-MM-RRRR HH24:MI:SS''') 
Three single quotes in place of all of the single singles.
-craig

"You can never have too many knives" -- Logan Nine Fingers
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

thanks, i have been busing for another task, i will get back to you thin weekend or early next week.
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

Hi
I fixed the problem, as you said Craig, i used DSLogInfo to print sql to log file and i found out i couldn't execute the following command and no error output:

Code: Select all

SELECT MAX(t.update_date_time) FROM table t WHERE t.update_date_time >= to_date('30-06-2017 12:29:00', 'DD-MM-RRRR HH24:MI:SS'); | sqlplus -S <user name>/<password>@<database>
the problem is the greater sign '>', windows command output return value into a file. :x

windows escape charactor is ^ and i used a pipeline in the command so you need three ^ to make the command work. the right syntax is

Code: Select all

SELECT MAX(t.update_date_time) FROM table t WHERE t.update_date_time ^^^>= to_date('30-06-2017 12:29:00', 'DD-MM-RRRR HH24:MI:SS'); | sqlplus -S <user name>/<password>@<database>
thanks :D
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

admin need to fix the login problem :evil:
after i submit the post at first place system ask me to sign in and i lost everything after i log in
Post Reply