Trouble with Perl function getDate and Oracle date field

Highlighted
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.
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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download