Passing job parameter as a bind variable (not as literal)

hsahay · Post by **hsahay** » Mon Aug 03, 2009 3:30 pm

I have a job parameter PRC_GRP_CODE = 'GOLD'. We use data stage 7.5 on AIX 5.x and database is oracle 10g.

I want to pass the job parameter in the where clause of a user defined sql (eg in bind variable :1).

I know that if put it as #PRC_GRP_CDE# it will be sent to oracle as a string literal, but i would like to send it as a bind variable.

Please suggest how we can do this.

Code: Select all

SELECT mbr_id, member_name 
FROM members 
WHERE mbr_group_cde = :1

chulett · Post by **chulett** » Mon Aug 03, 2009 3:47 pm

No, you want the "literal", a bind variable implies having something to bind to that value from every record so just use the job parameter there. Well... let's clarify that.

You would use the Job Parameter in the SQL if you are sourcing from your database and want to constrain the source SQL based on a job parameter that could change from run to run.

You could use a bind variable for SQL that is performing a reference lookup and then you'd use the job parameter in the Key Expression of the lookup. But you as well could do a "keyless" lookup and fall back on having the job parameter directly in the SQL.

Assuming the former, i.e. a source query, I'll stick with my "you want the literal" answer.

hsahay · Post by **hsahay** » Mon Aug 03, 2009 4:14 pm

Hi Craig,

Thanks for the reply. I would need to pass it as a bind variable only. the query I posted is for illustrative purpose only. For our real query we have to put a sql profile on the oracle side due to performance reasons; without the bind variable it would be a dba task every time this job is run. That is why I was trying to pass it as a bind variable so that one single sql profile can be saved against our query.

Please let me know if there is a way to do this. I have searched for this and almost read every post with bind variable but I coudn't find an answer.

Thanks
Shyam

chulett · Post by **chulett** » Mon Aug 03, 2009 4:33 pm

First suggestion - always use real examples when you illustrate your problem. It is troubling when someone posts something "like" what they need and you answer based on that example only to find out it really isn't what they need to do.

So... you are using a sql profile rather than tuning the sql yourself? Typically one would "need" to do this with canned applications where you have no access to the queries themselves, here you could just properly tune the query manually. This way seems to be making your solution more complicated than it really needs to be.

Still assuming this is a source query (which you have neither confirmed not denied) meaning you will then need the rows it selects in your job to transform/load, there is no way to get a "bind variable" into play using the normal DB stages. You're only going to see bind variables on input links, links that move data into the database, not out of it.

About the only way I can see you getting what you need is by writing a stored procedure to do the select that way and then sourcing from it via the Stored Procedure stage. I believe. You should probably ask your DBA about that approach.

hsahay · Post by **hsahay** » Mon Aug 03, 2009 4:50 pm

Thank you for the answer.

chulett · Post by **chulett** » Mon Aug 03, 2009 6:24 pm

Do let us know how you end up working this out, please.