hi all,
Oracle 11g 11.2.0.3.0
Forms 10g
SQL> l
1 SELECT *
2 FROM V$NLS_PARAMETERS
3* WHERE PARAMETER='NLS_DATE_FORMAT'
SQL>
SQL> /
PARAMETER VALUE
------------------------------ --------------------
NLS_DATE_FORMAT DD-MON-RR
I am facing a problem, i had a Form which contain 5 columns ( 5th column is not in use )
1) Emp Code
2) Emp Name
3) Time-In
4) Time-out
reading data from a CSV file which contain data in format below:
35,SHOBI,1/2/2017 8:51,1/2/2017 18:31
14,LALAM,1/2/2017 9:00,1/2/2017 17:41
33,MADHULAL,1/2/2017 9:15,1/2/2017 19:13
34,TULSI,1/2/2017 9:31,1/2/2017 18:32
10,MUBEEN,1/2/2017 9:38,1/2/2017 19:05
Time-In and Time-out contain data in format mm/dd/yyyy hh24:mi
the below code in form to read CSV data in Forms grid, data can be read successfully but the problem is for Time-in and Time-out we have set property in forms as
Date and format mask is hh24:mi
when reading data in this column as to_date(rec.att_data, 'mm/dd/yyyy hh24:mi) it is showing 00:00 for all the rows it read, how i can handle it to have normal data (Time portion) as mentioned in CSV ( on Forms both columns are Date type and their format is hh24:mi ) please help.
FUNCTION READ_CSV RETURN varchar2 IS
xx NUMBER:=0;
v_fName VARCHAR2(500);
vfname client_text_io.file_type;
readline VARCHAR2(4000);
BEGIN
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);
--
-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
Go_Block('ATT_DATA_FROM_EXCEL_SHEET');
First_Record;
vfName := CLIENT_TEXT_IO.FOPEN (v_fName, 'R');
SET_DATE_FORMAT_4_SESSION;
LOOP
CLIENT_TEXT_IO.GET_LINE (vfName, readline);
if Length(readline) > 0 then
for rec in (SELECT REGEXP_SUBSTR (readline, '[^,]+', 1, LEVEL) att_data
FROM dual CONNECT BY REGEXP_SUBSTR (readline, '[^,]+', 1, LEVEL) IS NOT NULL)
loop
xx:=xx+1;
if xx = 1 then
:EMP_CODE := rec.att_data;
elsif xx = 2 then
:EMP_NAME := rec.att_data;
elsif xx = 3 then
if rec.att_data not like 'Not%' then
:TIME_IN := to_date(rec.att_data, 'mm/dd/yyyy hh24:mi');
else
:TIME_IN := null;
end if;
elsif xx = 4 then
if rec.att_data not like 'Not%' then
:TIME_OUT := to_date(rec.att_data, 'mm/dd/yyyy hh24:mi');
else
:TIME_OUT := null;
end if;
xx:=0;
end if;
end loop;
end if;
Synchronize;
CLIENT_TEXT_IO.NEW_LINE;
Next_Record;
END LOOP;
ELSE
message ('Error: Not a valid CSV file selected');
Raise Form_Trigger_Failure;
END IF;
CLIENT_TEXT_IO.FCLOSE(vfName);
First_Record;
Return ('Successful...');
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLIENT_TEXT_IO.FCLOSE(vfName);
Return ('Un-Successful...');
WHEN OTHERS THEN
CLIENT_TEXT_IO.FCLOSE(vfName);
message('Error: '||SQLERRM);
Return ('Un-Successful...');
Raise Form_Trigger_Failure;
END;
CREATE OR REPLACE PROCEDURE SET_DATE_FORMAT_4_SESSION IS
BEGIN
DBMS_SESSION.set_nls ('nls_date_format', '''mm/dd/yyyy hh24:mi''');
END;
regards