Re: Oracle and PeopleSoft date format problem

[Barbara Wood <BARBARA_WOOD@NON-HP-PALOALTO-OM13.OM.HP.COM>]

        I hope this helps (I only looked at case 4):

        Even though you say your Oracle date format is default is DD-MON-YYYY, I
        bet it isn't. It probably is DD-MON-YY.

        In your BEGIN-SETUP section include the following sql statement:

        Begin-SQL
        alter session set nls_date_format='DD-MON-YYYY'
        End-SQL





______________________________ Reply Separator _________________________________
Subject: Oracle and PeopleSoft date format problem
Author:  Non-HP-j-lee (j-lee@ADMIN.NDIS.UMN.EDU) at HP-PaloAlto,mimegw9
Date:    4/14/98 6:11 AM


Hello!
I am new to writing SQRs.
The problem I have is with a date field to be loaded
from a flat file into a PeopleSoft table.

The environment that I am working in has the following elements:
Window 95, Novell NT, SQRW V3.0.18.1.1, PeopleSoft Tools 7, and Oracle 7 DBMS.
In PeopleSoft, a date field is defined as 10 characters long whereas
the default date format in Oracle is DD-MMM-YYYY.
I have tried different ways to format the date field value in my SQR.
Each time I got a different error message.
Has any one of you experienced a similar problem?  How was your problem solved?

SQL Status = -1400, SQL Error  = ORA-01400: mandatory (NOT NULL) column is missi
ng or NULL during insert
Case 1:
begin-sql on-error=sql-error
INSERT INTO PS_TBL (Date_Field) Values(to_date('10101900','mmddyyyy'));
end-sql
Case 2:
begin-sql on-error=sql-error
INSERT INTO PS_TBL (Date_Field) Values('10_Oct_00');
end-sql


SQL Status = -1858, SQL Error  = ORA-01858: a non-numeric character was found wh
ere a numeric was expected
Case 3:
begin-sql on-error=sql-error
INSERT INTO PS_TBL
(Date_field)
Values(to_date('1900-10-10','yyyy-mm-dd'));
end-sql
end-procedure

SQL Status = -1830, SQL Error  = ORA-01830: date format picture ends before conv
erting entire input string
Case 4:
INSERT INTO PS_TBL
(Date_field)
Values('10_Oct_1900');
end-sql
end-procedure

Thanks in advance!










Jennifer (Jen) Lee
626-1679