User Variable Activity

kiran259 · Post by **kiran259** » Mon Jan 25, 2010 3:42 am

I have a parallel job where the query is parameterized(RCP ON).
Oracle Enterprise------>Seq File
This parallel job is connected in a job sequence wherein UserVariable activity used.
A where condition is kept in the query like "where to_char(sample_date,'YYYY-MM-DD')='2010-01-25'".
I am passing '2010-01-25' as a Job Param and using in UserVar Activity.I used " \' ":test_date:" \' ".But DS is logging an error stating "query not ended properly".Same query is running successfully from SQL command line.

Any solutions will be appreciated.

Thanks

ArndW · Post by **ArndW** » Mon Jan 25, 2010 3:45 am

What happens if you add a ";" to the end of your query?

kiran259 · Post by **kiran259** » Mon Jan 25, 2010 4:10 am

ArndW wrote:What happens if you add a ";" to the end of your query? ...

------Same error

I forgot to mention another error:ORA-00972 identifier too long.
After some search,I found that some clients do not accept VARCHAR>31.I am using Oracle 10g version.
I have columns which had VARCHAR>31.Is this creating problem?

Thanks

ArndW · Post by **ArndW** » Mon Jan 25, 2010 4:31 am

The contents of the columns can be larger than 31 characters, but this is referring to your column name length(s) which need to be shortened.

kiran259 · Post by **kiran259** » Mon Jan 25, 2010 5:06 am

All the column name lengths are <30.But still DS logging "Oracle identifier too long" and "query not ended properly" errors.

ArndW · Post by **ArndW** » Mon Jan 25, 2010 6:25 am

could you post the full error message, which (I think) will also contain the query as seen by Oracle.

kiran259 · Post by **kiran259** » Mon Jan 25, 2010 6:49 am

select <schemaname.tablename.columnname>(30 column names) from <schemaname.tablename> where to_char(schemaname.tablename.test_date,'YYYY-MM-DD')='2010-07-25'----Query used

I checked with select * from <tablename> where to_char(test_date,'YYYY-MM-DD')='2010-07-25'.

Errors logging for both:
Message:ORA-00972:Identifier too long
Statement:"select <schemaname.tablename.columnname>(30 column names) from <schemaname.tablename> where to_char(schemaname.tablename.test_date,'YYYY-MM-DD')='2010-07-25'" Provided query statement did not prepare correctly

The same query is running fine in parallel job but problem aroused in job sequence where User Variable Activity is used for passing test_date at run time.

ArndW · Post by **ArndW** » Mon Jan 25, 2010 8:35 am

Did you replace the text "schemaname" in the post or is that what is displayed? Cut-and-Paste your datastage SQL into the tool of your choice and see if it works or also shows an error.

chulett · Post by **chulett** » Mon Jan 25, 2010 8:57 am

kiran259 wrote:After some search,I found that some clients do not accept VARCHAR>31.I am using Oracle 10g version.

If this is true, then perhaps you need to install an updated Oracle client.

kiran259 · Post by **kiran259** » Tue Jan 26, 2010 10:42 pm

The mistake done by me was appending double quotes at the front and rear ends of the query in UserVar Activity.It was running successfully after I remove them.But DS puzzled me giving the Oracle:identifier too long...

Thanks for your help