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
Oracle Bind Variable
Moderators: chulett, rschirm, roy
Oracle Bind Variable
Aravind
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
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.
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.
Aravind
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
if the parameter have 100 values, how are you using it inAnd it can have 100 distinct values
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