Passing runtime values to an sql command in a routine

[RAJARP]

Hi,

This is my before job routine using which I am running one oracle sql command using sql plus

Code: Select all

ErrorCode = 0      ;* set this to non-zero to stop the stage/job

*Below values are job parameters and passed to the routine through InputArg
ORA_UID = Field(InputArg,';',1)
ORA_PWD = Field(InputArg,';',2)
ORA_DSN = Field(InputArg,';',3)
p_ZONE_CD=Field(InputArg,';',4)
p_ZONE_ID=Field(InputArg,';',5)


Call DSLogInfo("ORA_UID":ORA_UID,DSJ.ME) 
Call DSLogInfo("ORA_PWD":ORA_PWD,DSJ.ME) 
Call DSLogInfo("ORA_DSN":ORA_DSN,DSJ.ME) 
call DSLogInfo("ZONE_CD":p_ZONE_CD,DSJ_ME)
call DSLogInfo("ZONE_ID":p_ZONE_ID,DSJ_ME)


Cmd1 = 'sqlplus -s ':ORA_UID:'/':ORA_PWD:'@':ORA_DSN:' << EOF'
Cmd2='set heading off'
Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =12 and ZONE_CD='NY' ; "
call DSLogInfo("cmd3":Cmd3,DSJ_ME)
Cmd4 = 'EOF'
Cmd = Cmd1:Char(10):Cmd2:Char(10):Cmd3:Char(10):Cmd4
Call DSExecute('UNIX',Cmd,Output1,ReturnCode)
Call DSLogInfo("Output is" :Output1,"")
Ans=Output1

This routine works perfect and give me the desired result.

Now, I want to pass the value of two filter conditions in the above sql command in run time.

And, I am not able to do that successfully

I tried

Code: Select all

Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =p_ZONE_ID and ZONE_CD=p_ZONE_CD 

and

Code: Select all

Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =&p_ZONE_ID and ZONE_CD=&p_ZONE_CD 

But the value in the variable is not getting translated as required inside the SQL command.

P.S
call DSLogInfo("ZONE_CD":p_ZONE_CD,DSJ_ME)
call DSLogInfo("ZONE_ID":p_ZONE_ID,DSJ_ME)
gives me NY and 12 resp i my director logs during execution.So, there is no issue with this

Any pointers would be appreciated.

Thanks,
Raja

[chulett]

You cannot pass anything from a before routine to the job as it is already running. What you can do is use a Sequence to run the routine, then the output can be passed to any downstream activity as a job parameter.

[ray.wurlod]

Parameter references are not introduced by ampersand; rather they are enclosed by a pair of sharp signs. You may find it easier to pass the entire SQL statement as a job parameter.

[RAJARP]

Thank you both,

I don't want to modify my sequncer.I want to do it in routine
@ craig,
Bascially, I am not trying to pass any parameter into the running job

Code: Select all

Cmd3="DELETE from MDR.ZONE_CONTROL where ZONE_AVG=(SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =12 and ZONE_CD='NY') ; "

This is going to be my final command, once i get the 'select' part working.

@ Ray,

Code: Select all

Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =#p_ZONE_ID# and ZONE_CD=#p_ZONE_CD# ; "

I tried this.
But call DSLogInfo("cmd3":Cmd3,DSJ_ME)

gave this as in my director log

SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID =#p_ZONE_ID# and ZONE_CD=#p_ZONE_CD#

Instread of expected

Code: Select all

SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID ='12' and ZONE_CD='NY'

should i give some escape characters??

Regards,
Raja R P

[chulett]

Ah, ok. Your problem is your Cmd3 string is built inside a pair of quotes so no variable substitution is done. Keep the string sections in quotes and concatenate the variables into it:

Code: Select all

Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID = '" : p_ZONE_ID : "' and ZONE_CD = '" : p_ZONE_CD : "' ; "

That way the variables are resolved and their values (rather than their names as you are seeing now) are included in the final command string.

[RAJARP]

Hi craig,
Thanks much!!!

this piece did the trick.

Cmd3="SELECT MAX(ZONE_AVG) FROM MDR.ZONE_CONTROL WHERE ZONE_ID ='":p_ZONE_ID:"' and ZONE_CD='":p_ZONE_CD:"' ;"

I should have been careful with double quotes , single quotes and the append operator colon :

Regards,
Raja R P