how to pass table name to oracle stage?

yabhinav · Post by **yabhinav** » Mon Jan 11, 2010 5:02 am

Is it possible to send table name to oracle stage when using it in conjunction with a lookup?

I'm able pass column names using ORCHESTRATE.column_name but when I do the same for table name it says invalid table.

Is there any work around for this?

P.S: I'm using sparse lookup

chulett · Post by **chulett** » Mon Jan 11, 2010 7:25 am

No. While the table name can be a job parameter it cannot be dynamically supplied from the input data, if that's what you are trying to do. Now, that doesn't mean you couldn't build some kind of stored procedure to do that, I would think.

yabhinav · Post by **yabhinav** » Tue Jan 12, 2010 7:49 am

Thanks for the information Craig. I'm planning to frame the query before the job starts and send it as a parameter.

chulett · Post by **chulett** » Tue Jan 12, 2010 8:56 am

You might want to look into the 'sql file' option instead of passing in the query as a parameter.

yabhinav · Post by **yabhinav** » Wed Jan 13, 2010 4:29 am

chulett wrote:You might want to look into the 'sql file' option instead of passing in the query as a parameter. ...

Can you please elaborate. I have no idea where to find the sql file option?

yabhinav · Post by **yabhinav** » Wed Jan 13, 2010 4:34 am

I got it. Its available in the RDBMS stage. I was searching for the option in Oracle stage!

chulett · Post by **chulett** » Wed Jan 13, 2010 8:01 am

It may not be documented all that well, it works in more than just the DRS stage from what I recall.

kandyshandy · Post by **kandyshandy** » Wed Jan 13, 2010 9:43 am

yabhinav wrote:Thanks for the information Craig. I'm planning to frame the query before the job starts and send it as a parameter.

Let us know if this works. I remember that i tried this years back and was not lucky...

Sreenivasulu · Post by **Sreenivasulu** » Wed Jan 13, 2010 12:46 pm

You can build the query by writing a routine and then you can pass the table name.
Regards
Sreeni