ORA-00911: invalid character Error

g_rkrish · Post by **g_rkrish** » Mon May 17, 2010 1:52 am

I have a simple job just a oci stage where i have sysdate dual in which refered to a set of dates.the field am using has a fuction to_char for ex in the source i have to_char(sysdate,'MMDDYY') which again refered to the source databases.hte error am gettng is ORA-00911: invalid character any thoughts

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon May 17, 2010 2:47 am

I assume what you have supplied is only part of the sql.

Try running the SQL outside datastage and ensure it returns no errors or warnings.

g_rkrish · Post by **g_rkrish** » Mon May 17, 2010 2:55 am

Sainath.Srinivasan wrote:I assume what you have supplied is only part of the sql.

Try running the SQL outside datastage and ensure it returns no errors or warnings. ...

I can view from the view data but i gives error on the job

ray.wurlod · Post by **ray.wurlod** » Mon May 17, 2010 3:41 am

Find out what Oracle error number -911 actually means - use the oerr command to get a recommendation about what to do about it.

chulett · Post by **chulett** » Mon May 17, 2010 6:13 am

Usually that comes from an unresolved parameter - #XXX# - or you've tacked on a trailing semi-colon. Both the hash and semi are "invalid characters" here.

g_rkrish · Post by **g_rkrish** » Mon May 17, 2010 6:59 am

chulett wrote:Usually that comes from an unresolved parameter - #XXX# - or you've tacked on a trailing semi-colon. Both the hash and semi are "invalid characters" here. ...

Basically i have the query in the i/p DB stage is

Select to_char(sysdate,'MMDDYY') from dual

On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY') so will that changing again gives the problem or do i take to char out in the source DB stage

any thoughts

chulett · Post by **chulett** » Mon May 17, 2010 7:06 am

So, more than just "a simple job just an oci stage", eh? Sorry, but I have no idea what you mean by "On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY')".

If it means you are using that bind variable in your source query, you can't, that would need to be a job parameter. If "ref DB" means some kind of lookup, that should work provided your first field is marked as a key.

g_rkrish · Post by **g_rkrish** » Sat May 22, 2010 6:30 am

chulett wrote:So, more than just "a simple job just an oci stage", eh? Sorry, but I have no idea what you mean by "On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY')".

If it means you are using that bind variable in your source query, you can't, that would need to be a job parameter. If "ref DB" means some kind of lookup, that should work provided your first field is marked as a key.

sorry about the late reply actually the bug is not with code but with the data stage versions we are migrating from 7.1 to.8.1 .eventually 8.1 gives o/p of oconv(sysdate,'MMDDYY') as 05/22/10 but 7.1 gives as 05 22 10 so corrected the query worked fine.thanks all for the i/p

chulett · Post by **chulett** » Sat May 22, 2010 6:48 am

Ah... yes, things were "fixed" or tightened up to ensure proper operation as the product matured and kudos for catching that issue. And we know you meant "7.5" when you said "8.5".

g_rkrish · Post by **g_rkrish** » Sat May 22, 2010 6:56 am

chulett wrote:Ah... yes, things were "fixed" or tightened up to ensure proper operation as the product matured and kudos for catching that issue. And we know you meant "7.5" when you said "8.5".

Typos

chulett · Post by **chulett** » Sat May 22, 2010 8:06 am

As noted, we know. You can always go back and edit it, if you so desire. Of course, then people will wonder what the heck I'm talking about.