Home » Developer & Programmer » Forms » frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 (Oracle forms,10g,Windows 2003)
|
|
|
|
| Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437411 is a reply to message #437409] |
Mon, 04 January 2010 05:45   |
katangur.nikethan
Messages: 31
Registered: December 2009
Location: Hyderabad
|
Member |
|
|
I am sending the code
WHEN_NEW_FORM_INSTANCE
declare
rec_details TB_TIMESHEET_DETAILS%ROWTYPE;
begin
Go_block('TB_TIMESHEET_USER');
execute_query;
select * INTO REC_DETAILS
FROM TB_TIMESHEET_DETAILS
where USER_ID = :TB_TIMESHEET_USER.USER_ID
AND WEEK_START_DATE = TO_CHAR(next_day(sysdate,'MON')-7,'MM/DD/YYYY');
IF REC_DETAILS.USER_ID IS NULL THEN
MESSAGE('No data available for this Duration');
ELSE
GO_BLOCK('TB_TIMESHEET_DETAILS');
:TB_TIMESHEET_DETAILS.STATUS := REC_DETAILS.STATUS;
:TB_TIMESHEET_DETAILS.LAST_MODIFIED_DATE := REC_DETAILS.LAST_MODIFIED_DATE;
:TB_TIMESHEET_DETAILS.DATE_1 := TO_CHAR(REC_DETAILS.DAY1_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.DATE_2 := TO_CHAR(REC_DETAILS.DAY2_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.DATE_3 := TO_CHAR(REC_DETAILS.DAY3_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.DATE_4 := TO_CHAR(REC_DETAILS.DAY4_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.DATE_5 := TO_CHAR(REC_DETAILS.DAY5_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.DATE_6 := TO_CHAR(REC_DETAILS.DAY6_START_TIME,'DD-MON-YY');
:TB_TIMESHEET_DETAILS.ST_1 := TO_CHAR(REC_DETAILS.DAY1_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ST_2 := TO_CHAR(REC_DETAILS.DAY2_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ST_3 := TO_CHAR(REC_DETAILS.DAY3_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ST_4 := TO_CHAR(REC_DETAILS.DAY4_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ST_5 := TO_CHAR(REC_DETAILS.DAY5_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ST_6 := TO_CHAR(REC_DETAILS.DAY6_START_TIME,'HH:MIN A.M.');
:TB_TIMESHEET_DETAILS.ET_1 := TO_CHAR(REC_DETAILS.DAY1_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.ET_2 := TO_CHAR(REC_DETAILS.DAY2_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.ET_3 := TO_CHAR(REC_DETAILS.DAY3_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.ET_4 := TO_CHAR(REC_DETAILS.DAY4_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.ET_5 := TO_CHAR(REC_DETAILS.DAY5_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.ET_6 := TO_CHAR(REC_DETAILS.DAY6_END_TIME,'HH:MI A.M.');
:TB_TIMESHEET_DETAILS.HOURS_1 := ROUND((REC_DETAILS.DAY1_END_TIME- REC_DETAILS.DAY1_START_TIME)*24,2);
:TB_TIMESHEET_DETAILS.HOURS_2 := ROUND((REC_DETAILS.DAY2_END_TIME- REC_DETAILS.DAY2_START_TIME)*24,2);
:TB_TIMESHEET_DETAILS.HOURS_3 := ROUND((REC_DETAILS.DAY3_END_TIME- REC_DETAILS.DAY3_START_TIME)*24,2);
:TB_TIMESHEET_DETAILS.HOURS_4 := ROUND((REC_DETAILS.DAY4_END_TIME- REC_DETAILS.DAY4_START_TIME)*24,2);
:TB_TIMESHEET_DETAILS.HOURS_5 := ROUND((REC_DETAILS.DAY5_END_TIME- REC_DETAILS.DAY5_START_TIME)*24,2);
:TB_TIMESHEET_DETAILS.HOURS_6 := ROUND((REC_DETAILS.DAY6_END_TIME- REC_DETAILS.DAY6_START_TIME)*24,2);
END IF;
end;
WHEN-BUTTON-PRESSED---- Add
Declare
DAY_STR VARCHAR2(3);
temp number(2,2);
Begin
DAY_STR := TO_CHAR(:ENTRY_DATE,'DY');
case DAY_STR
WHEN 'MON' THEN
:DATE_1 := :ENTRY_DATE;
:ST_1 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_1 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_1 := :COMMENT;
:HOURS_1 := ROUND((TO_DATE(:DATE_1||' '||:ET_1,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_1||' '||:ST_1,'DD-MON-YYYY HH:MI A.M.'))*24,2);
WHEN 'TUE' THEN
:DATE_2 := :ENTRY_DATE;
:ST_2 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_2 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_2 := :COMMENT;
:HOURS_2 := ROUND((TO_DATE(:DATE_2||' '||:ET_2,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_2||' '||:ST_2,'DD-MON-YYYY HH:MI A.M.'))*24,2);
WHEN 'WED' THEN
:DATE_3 := :ENTRY_DATE;
:ST_3 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_3 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_3 := :COMMENT;
:HOURS_3 := ROUND((TO_DATE(:DATE_3||' '||:ET_3,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_3||' '||:ST_3,'DD-MON-YYYY HH:MI A.M.'))*24,2);
WHEN 'THU' THEN
:DATE_4 := :ENTRY_DATE;
:ST_4 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_4 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_4 := :COMMENT;
:HOURS_4 := ROUND((TO_DATE(:DATE_4||' '||:ET_4,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_4||' '||:ST_4,'DD-MON-YYYY HH:MI A.M.'))*24,2);
WHEN 'FRI' THEN
:DATE_5 := :ENTRY_DATE;
:ST_5 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_5 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_5 := :COMMENT;
:HOURS_5 := ROUND((TO_DATE(:DATE_5||' '||:ET_5,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_5||' '||:ST_5,'DD-MON-YYYY HH:MI A.M.'))*24,2);
WHEN 'SAT' THEN
:DATE_6 := :ENTRY_DATE;
:ST_6 := :ST_HOURS||':'||:ST_MIN||' '||:ST_MARIDIEM;
:ET_6 := :ET_HOURS||':'||:ET_MIN||' '||:ET_MARIDIEM;
:COMM_6 := :COMMENT;
:HOURS_6 := ROUND((TO_DATE(:DATE_6||' '||:ET_6,'DD-MON-YYYY HH:MI A.M.')- TO_DATE(:DATE_6||' '||:ST_6,'DD-MON-YYYY HH:MI A.M.'))*24,2);
END CASE;
END;
WHEN-BUTTON-PRESSED ----- SUBMIT
DECLARE
REC_INSERT TB_TIMESHEET_DETAILS%ROWTYPE;
TEMP NUMBER;
BEGIN
REC_INSERT.USER_ID := :TB_TIMESHEET_USER.USER_ID;
REC_INSERT.WEEK_START_DATE := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_1,'DD-MON-YYYY');
REC_INSERT.WEEK_END_DATE := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_6,'DD-MON-YYYY');
REC_INSERT.DAY1_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_1||' '||:TB_TIMESHEET_DETAILS.ST_1,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY1_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_1||' '||:TB_TIMESHEET_DETAILS.ET_1,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY2_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_2||' '||:TB_TIMESHEET_DETAILS.ST_2,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY2_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_2||' '||:TB_TIMESHEET_DETAILS.ET_2,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY3_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_3||' '||:TB_TIMESHEET_DETAILS.ST_3,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY3_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_3||' '||:TB_TIMESHEET_DETAILS.ET_3,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY4_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_4||' '||:TB_TIMESHEET_DETAILS.ST_4,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY4_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_4||' '||:TB_TIMESHEET_DETAILS.ET_4,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY5_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_5||' '||:TB_TIMESHEET_DETAILS.ST_5,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY5_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_5||' '||:TB_TIMESHEET_DETAILS.ET_5,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY6_START_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_6||' '||:TB_TIMESHEET_DETAILS.ST_6,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.DAY6_END_TIME := TO_DATE(:TB_TIMESHEET_DETAILS.DATE_6||' '||:TB_TIMESHEET_DETAILS.ET_6,'DD-MON-YYYY HH:MI A.M.');
REC_INSERT.COMMENTS := :TB_TIMESHEET_DETAILS.COMM_1||:TB_TIMESHEET_DETAILS.COMM_2||:TB_TIMESHEET_DETAILS.COMM_3||:TB_TIMESHEET_DETAILS.COMM_4||
:TB_TIMESHEET_DETAILS.COMM_5||:TB_TIMESHEET_DETAILS.COMM_6;
REC_INSERT.TOTAL_HOURS := :TB_TIMESHEET_DETAILS.TOTAL;
IF :TB_TIMESHEET_USER.USER_ROLE = 'ADMIN' THEN
REC_INSERT.APPROVED_BY := :TB_TIMESHEET_USER.USER_NAME;
REC_INSERT.STATUS := :TB_TIMESHEET_DETAILS.STATUS;
ELSE
REC_INSERT.APPROVED_BY := NULL;
REC_INSERT.STATUS := 'N';
END IF;
SELECT COUNT(*) INTO TEMP FROM TB_TIMESHEET_DETAILS
WHERE USER_ID = :TB_TIMESHEET_USER.USER_ID
AND WEEK_START_DATE = :TB_TIMESHEET_DETAILS.DATE_1;
IF TEMP <0 THEN
UPDATE TB_TIMESHEET_DETAILS
SET DAY1_START_TIME = REC_INSERT.DAY1_START_TIME,
DAY1_END_TIME = REC_INSERT.DAY1_END_TIME,
DAY2_START_TIME = REC_INSERT.DAY2_START_TIME,
DAY2_END_TIME = REC_INSERT.DAY2_END_TIME,
DAY3_START_TIME = REC_INSERT.DAY3_START_TIME,
DAY3_END_TIME = REC_INSERT.DAY3_END_TIME,
DAY4_START_TIME = REC_INSERT.DAY4_START_TIME,
DAY4_END_TIME = REC_INSERT.DAY4_END_TIME,
DAY5_START_TIME = REC_INSERT.DAY5_START_TIME,
DAY5_END_TIME = REC_INSERT.DAY5_END_TIME,
DAY6_START_TIME = REC_INSERT.DAY6_START_TIME,
DAY6_END_TIME = REC_INSERT.DAY6_END_TIME,
COMMENTS = REC_INSERT.COMMENTS,
TOTAL_HOURS = REC_INSERT.TOTAL_HOURS,
STATUS = REC_INSERT.STATUS,
APPROVED_BY = REC_INSERT.APPROVED_BY,
LAST_MODIFIED_DATE = SYSDATE
WHERE USER_ID = REC_INSERT.USER_ID
AND WEEK_START_DATE = REC_INSERT.WEEK_START_DATE;
ELSE
INSERT INTO TB_TIMESHEET_DETAILS
VALUES(REC_INSERT.USER_ID,
REC_INSERT.WEEK_START_DATE,
REC_INSERT.WEEK_END_DATE,
REC_INSERT.DAY1_START_TIME,REC_INSERT.DAY1_END_TIME,
REC_INSERT.DAY2_START_TIME,REC_INSERT.DAY2_END_TIME,
REC_INSERT.DAY3_END_TIME,REC_INSERT.DAY3_END_TIME,
REC_INSERT.DAY4_START_TIME,REC_INSERT.DAY4_END_TIME,
REC_INSERT.DAY5_START_TIME,REC_INSERT.DAY5_END_TIME,
REC_INSERT.DAY6_START_TIME,REC_INSERT.DAY6_END_TIME,
REC_INSERT.COMMENTS,REC_INSERT.TOTAL_HOURS,
REC_INSERT.STATUS,REC_INSERT.APPROVED_BY,SYSDATE);
END IF;
END;
|
|
|
|
|
|
|
|
| Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437415 is a reply to message #437406] |
Mon, 04 January 2010 06:02 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Probably because you are using to_date on a date, which does an implicit to_char.
So this:
TO_DATE(:TB_TIMESHEET_DETAILS.DATE_1||' '||:TB_TIMESHEET_DETAILS.ST_1,'DD-MON-YYYY HH:MI A.M.');
Is effectively doing this:
TO_DATE(to_char(:TB_TIMESHEET_DETAILS.DATE_1, <default format mask>)||' '||:TB_TIMESHEET_DETAILS.ST_1,'DD-MON-YYYY HH:MI A.M.');
What you should code is this:
TO_DATE(to_char(:TB_TIMESHEET_DETAILS.DATE_1, 'DD-MON-YYYY')||' '||:TB_TIMESHEET_DETAILS.ST_1,'DD-MON-YYYY HH:MI A.M.');
I'm assuming st_1 is a number of the correct format, if not you will probably need some extra code to make sure it's in the correct format as well.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 05:19:45 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
