Oracle OCI Stage

[opdas]

Hi,
We are using the 8.0.1 version.
We are facing problem while reading and writting from and to Oracle OCI stage.
Though we can view (view button) the data from the stage but when the job runs we get the following error:ORA-01036: illegal variable name/number.
But the same query runs fine from SQL prompt. The Query Type is: Custom SQL

The second problem we are facing is with the date data type when we use the same OCI stage.
What I have observed is while reading the query should read as "YY-MON-DD" and while writting to the stage it should always be as "DD-MON-YY", otherwise we get some other date in the target.
For example if I have 29-May-2007 in the source and I use "DD-MON-YY" to read it in the target I would get : 07-Jun-2029. I 'm not running any transformation on this column.

Its really tesing our patience.


Regards
Om

[Hemant_Kulkarni]

Hi,

Code: Select all

error:ORA-01036: illegal variable name/number. 

It was a bug in 7.5.1A, there was a patch available though. Looks like they havent fixed it in DS 8.

For the date conversion, try to use Oracle to_date() function

[opdas]

Hi,
Thanks for your response.

I'm using oracle to_char() function. when reading and to_date() function when writting but the constaraint is I could only use the following combination otherwise I dont get the desired result.

When reading to_char(column_name,"YY-MON-DD")
When writting to_date(:var_name,"DD-MON-YY")

Regards
Om

[chulett]

The Query Type is: Custom SQL

The root of your problem.

Allowing the stage to generate the sql would avoid the vast majority of the problems you are having and allow you to cut back on your high blood pressure medication.

ps. The fact that it 'runs fine' from the SQL prompt is pretty much irrelevant. It just means that there are no syntax issues with the query, but provides no clue if it is appropriate for use under OCI.

[opdas]

Craig,
Thanks for your response.
I'm certainly not an angry man and dont have high BP, I control my diet and exercise daily for this. :D

Allow me to explain my problem:
We are facing problem whenwe use the Date datatype in my source-target columns.
If we simply allow the stage to generate the SQL in the source and target OCI stage we end up geting wrong date in the target for example we have 22-Jul-2007 as my source value I would get 07-Jul-2022 as value in my target.
When approached IBM Support they asked us to add DS_NO_FF envrionment variable in our job properties and default it to 1 but I didnt find any such environment variable .The ticket is still open with IBM.

So the work-around we found is to use custom SQL in source OCI stage and convert the date datatype to 'YY-MON-DD' instead of 'DD-MON-YY' it does when the stage generates the SQL.

Hope I'm clear this time.

Regards
Om

[chulett]

No, it was pretty clear the first time and not something all that unusual around here as issues handling Oracle DATE fields aren't all that uncommon. Well, except for the DS_NO_FF thing, never heard of it and have no clue that 'FF' would stand for.

You really shouldn't have any problems with Oracle DATE fields if you let the stage generate the sql and follow some simple standards. I've got literally thousands of jobs doing exactly this without issue. I posted this quite a number of times in the past, I guess once more won't hurt.

I always use a datatype of Timestamp for my Oracle DATE fields rather than declaring them as Date. Always. Because an Oracle DATE also contains a time portion, this let's me explicitly control the time when I deal with dates, including dates that "don't have one". Quoted because all dates in Oracle carry a time portion whether you want one or not.

You'll note that when you do that, the TO_CHAR mask the stage generates for SELECT statements and the TO_DATE mask used on the target side both look like this:

YYYY-MM-DD HH24:MI:SS

An ISO standard timestamp. Not only is it easy to manipulate but they can be directly compared to each other in this format as 'strings'. You'll also note that dates that "don't have" a time portion will show a 'zero' time. This also means you'll always need to add a time portion when loading 'outside' dates into Oracle, more than likely a standard zero time.

Split the timestamp at the space if you need to handle the two pieces separately, something easily done with the substring operator (since it is a consistent size) or with the Field function. Some people might consider this extra work to maintain, but I prefer to have full control over the data and to explicitly set a zero time if that's what's needed.

I've built a library of standard routines for dealing with 'Oracle' dates which all developers use. This would include transforming dates from other sources to 'Oracle date format', optionally handling the time as well or using a zero time, get the System or Job Start time in an Oracle consumable format, etc. They're not all that hard to code up and keeps my minions from having to re-invent the wheel each time.

The end result? Zero problems with Oracle date/time information here. No need to IBM Support or whatever DS_NO_FF does, nor any kind of custom sql workaround.

Hope that helps.