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,
Using environment varaible in the job paramter
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Wed Jan 02, 2008 11:45 pm
- Location: Hyderabad
-
- Participant
- Posts: 12
- Joined: Wed Jan 02, 2008 11:45 pm
- Location: Hyderabad
-
- Participant
- Posts: 12
- Joined: Wed Jan 02, 2008 11:45 pm
- Location: Hyderabad
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Re: Using environment varaible in the job paramter
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.avneeshrai wrote:
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#
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
Can you please explain exactly how you tried this and what error you are now getting? I hate to assume too much here.avneeshrai wrote:So is there a work around for this issue, I did try just $ParamName and it too returns error, as expected.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers