Using environment varaible in the job paramter

[avneeshrai]

Using DRS stage to read from oracle database table, in the sql the from clause is a job paramter. In the job paramter I have a environment varaible, and the job aborts saying invalid character, can we use a environment varaible in the job parameter?


Here is the Job Paramter
#FromClause# = prdtbl where #$LoadType# =1

Here is how the SQL looks when we use SQL generated option

SELECT ABC FROM #FromClause#

The error is

DB: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00911: invalid character
: SQLExecDirect: Error executing statement

'SELECT ABC FROM prdtbl where #$LoadType# =1'


Is it because the entire sql is in between ' ' and it is being considered as a string, if so is there a workaround for this?


Thanks,

[jreddy]

Try this

#FromClause# = prdtbl where $LoadType =1

[chulett]

Your 'invalid character' is the #, which means your parameter is not being recognized and evaluated. This is because a parameter cannot resolve another parameter inline.

[avneeshrai]

Hi Craig,

So is there a work around for this issue, I did try just $ParamName and it too returns error, as expected.

Thanks,

[avneeshrai]

Hi Craig,

So is there a work around for this issue, I did try just $ParamName and it too returns error, as expected.

Thanks,

[ameyvaidya]

Here is the Job Paramter
#FromClause# = prdtbl where #$LoadType# =1

Here is how the SQL looks when we use SQL generated option

SELECT ABC FROM #FromClause#

Assuming you call the job from a sequence, add a uservariable activity before the job activity stage and create one uservariable "FromClause". Resolve the from clause in this uservariable and then pass it to your job.

[chulett]

So is there a work around for this issue, I did try just $ParamName and it too returns error, as expected.

Can you please explain exactly how you tried this and what error you are now getting? I hate to assume too much here.