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
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