Trouble with Perl function getDate and Oracle date field

One Star

Trouble with Perl function getDate and Oracle date field

Hi,
I am currently trying to insert in a date field of table in an Oracle XE DB. I use the Perl function getDate(DD/MM/YY) in a tMap to OracleOutput which is the same date format of my local machine on Windows environnement.
When I run the job, I get the following error:
"DBD:Smiley Surprisedracle::st execute failed: ORA-01843: not a valid month (DBD ERROR: error possibly near <*> indicator at char 109 in '
INSERT
INTO B_VER_ART
(CODEARTIC, VER_ART, VER_EFFET_DEBUT, VER_EFFET_FIN, NOMG)
VALUES
(Smiley Tongue1, Smiley Tongue2, :<*>p3, Smiley Tongue4, Smiley Tongue5)
') "
My encoding type is ISO-8859-15.
Can you help to solve my problem, please? I can't say if it might be a bug or if, more surely, a wrong manipulation.
Tags (1)
Community Manager

Re: Trouble with Perl function getDate and Oracle date field

Hi
Which version of TOS did you sue?
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Trouble with Perl function getDate and Oracle date field

Hello,
in your tOracleOutput component, you have to add an additional column for each date column.
For your VAR_EFFET_DEBUT column, you will add :
Name : 'var_effet_debut'
SQL Expression : to_date(?,'DD/MM/YY')
Position : replace
Reference Column : VAR_EFFET_DEBUT
And so on for each date column. By this way, the NLS settings don't matter for date insertion.
If you don't do that, you have to adjust NLS_LANG and NLS_DATE_FORMAT so the default format match your date value.
Louis
One Star

Re: Trouble with Perl function getDate and Oracle date field

Thanks for your reactivity. I'll complete the information I gave.
Which version of TOS did you sue?

Shong, My Version is TOS 2.2.2.
For your VAR_EFFET_DEBUT column, you will add :
Name : 'var_effet_debut'
SQL Expression : to_date(?,'DD/MM/YY')
Position : replace
Reference Column : VAR_EFFET_DEBUT

Louis, I dont' see where you want me to add the SQL Expression and Position. I already have a DB_columns in my tOracleOutput for each date type columns of my Oracle Table and I can't use the SQL function to_date() in Expression columns of my Tmap. TOS waiting for a PERL expression.
To go further, the Input data comes from a generic DB but there's no date matching with my columns that why I want to use the system date. Here 's my NLS_PARAMETER from the Output DB which are the same as the date format I used with.
PARAMETER                      VALUE
------------------------------ ---------------------------
NLS_DATE_FORMAT DD/MM/RR
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR

Regards.
One Star

Re: Trouble with Perl function getDate and Oracle date field

I'm sorry, I was wrong, beginner mistake. I show you NLS_SESSION_PARAMETERS and not NLS_DATABASEPARAMETER which are
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

So it's normal if I've been rejected, because the date format I used is not the right one. Now, I need to find a Perl function which fits with this format.
Employee

Re: Trouble with Perl function getDate and Oracle date field

Hello,
Louis was speaking about Additional columns in tOracleOutput properties.
Regards,
One Star

Re: Trouble with Perl function getDate and Oracle date field

Yes, I found it but as I said sooner my date format is NLS_DATE_FORMAT DD-MON-RR and the Perl function getDate('DD-MON-YY') doesn't work. I can only use getDate('DD-MM-YY').
On your advise, I try to get an SQL expression which can convert a 'DD-MM-YY' date format to a 'DD-MON-RR'. Neither, I can't use a to_date(date,'DD-MON-RR'); and I don't want to change the NLS_DATE_FORMAT.
Do I need to write a Perl function to convert to this format or is there an other way to fit?
Employee

Re: Trouble with Perl function getDate and Oracle date field

No matter your NLS_DATE_FORMAT. It's just a presentation parameter, it doesn't mean your date is stored as DD-MON-RR in the database.
The output of my job is:
Starting job topic1959 at 17:19 17/01/2008.
.-----------------------------.
| tLogRow_1 |
+-----------------+-----------+
| parameter | value |
+-----------------+-----------+
| NLS_DATE_FORMAT | DD-MON-RR |
'-----------------+-----------'
.-----------.
| tLogRow_2 |
+-----------+
| my_date |
+-----------+
| 17-JAN-08 |
'-----------'
Job topic1959 ended at 17:19 17/01/2008.
One Star

Re: Trouble with Perl function getDate and Oracle date field

Thanks a lot, plegall, yours answers are always very clear as before ;-)
Thanks to Shong, Louis and mhirt too.
One Star

Re: Trouble with Perl function getDate and Oracle date field

This advice doesn't seem to work in version 4.0.2. I'm using the Java mode with the result "Unparseable date: ""12/09/1991"" yet that is the format I'm expecting.
I added an Additional Column with:
Name= 'Birth_Date'
SQL = "TO_DATE(?,'MM/DD/YYYY')"
Position = Replace
Reference Column = Birth_Date
------------
If I remove the leading and trailing double quotes from the SQL Expression, the code generated is as below and is syntactically wrong for Oracle.

String insert_tOracleOutput_1 = "INSERT INTO " + tableName_tOracleOutput_1 + " (National_Id,Last_Name,First_Name,Middle_Name,Name_Suffix,\"" + "Birth_Date" + "\",Campus_Id,FERPA,Empl_Id,D_COMM_CON_FLG,Program_Status) VALUES (?,?,?,?,?," + TO_DATE(?,'MM/DD/YYYY') + ",?,?,?,?,?)";
-------------------------------
Is there a solution for Oracle date insertion into a Oracle table when using version 4.0.2 with Java code production?
Thanks