DSXchange

Posted: **Mon Jun 26, 2017 5:04 am**

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

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

Posted: **Mon Jun 26, 2017 6:44 am**

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

Posted: **Tue Jun 27, 2017 12:33 am**

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

Posted: **Tue Jun 27, 2017 7:05 am**

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.

Posted: **Tue Jun 27, 2017 7:55 pm**

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

Posted: **Tue Jun 27, 2017 8:48 pm**

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.

Posted: **Thu Jun 29, 2017 9:53 am**

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.

Posted: **Fri Jun 30, 2017 1:02 am**

thanks, i have been busing for another task, i will get back to you thin weekend or early next week.

Posted: **Fri Jul 07, 2017 1:17 am**

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.

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

Posted: **Fri Jul 07, 2017 1:19 am**

admin need to fix the login problem

after i submit the post at first place system ask me to sign in and i lost everything after i log in

DSXchange

run oracle sql in routine

run oracle sql in routine