Oracle Bind Variable

[makreddy]

Hi,
Please let me know how we can use the Oracle Bind variable in Input queries. My DBA is forcing to use a bind variable in input query instead of a job parameter.

Thanks
Aravind

[keshav0307]

My DBA is forcing to use a bind variable in input query instead of a job parameter.

Why??
can you post the input query?

[makreddy]

Hi, Here is the Query.
SELECT
A.*
FROM
WORKALOC A,
RAWADDRESSTOALOC B,
RAWADDRESSATTRIBUTE C
WHERE
A.SOURCESYSTEMID = C.SOURCESYSTEMID
AND A.ADDRESSSOURCEKEY1 = C.ADDRESSSOURCEKEY1
AND A.ADDRESSSOURCEKEY2 = C.ADDRESSSOURCEKEY2
AND A.ADDRESSSOURCEKEY3 = C.ADDRESSSOURCEKEY3
AND B.SOURCEADDRSEQNUM = C.SOURCEADDRSEQNUM
AND
A.THREADID = #THREAD#


Here The Parameter #THREAD# is a job parameter. And it can have 100 distinct values. Our DBA is asking to use a bind variable, as Oracle is treating it as a hard coded value. SO when the thread value changes, it treats as a different query.

[keshav0307]

And it can have 100 distinct values

if the parameter have 100 values, how are you using it in
A.THREADID = #THREAD#
----------------------

you can put all the values in a sequential file.
and then design a job to read the sequential file have a lookup stage to read data from the oracle table, using sparse lookup by having
THREADID as key column.

The generated query will look like

SELECT
A.*
FROM
WORKALOC A,
RAWADDRESSTOALOC B,
RAWADDRESSATTRIBUTE C
WHERE
A.SOURCESYSTEMID = C.SOURCESYSTEMID
AND A.ADDRESSSOURCEKEY1 = C.ADDRESSSOURCEKEY1
AND A.ADDRESSSOURCEKEY2 = C.ADDRESSSOURCEKEY2
AND A.ADDRESSSOURCEKEY3 = C.ADDRESSSOURCEKEY3
AND B.SOURCEADDRSEQNUM = C.SOURCEADDRSEQNUM
AND
A.THREADID = :THREAD