Home » Developer & Programmer » Forms » frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 (Oracle forms,10g,Windows 2003)
frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437406] Mon, 04 January 2010 05:21 Go to next message
katangur.nikethan
Messages: 31
Registered: December 2009
Location: Hyderabad
Member
Hi all,

When i click on submit button it returns the error

" frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 "

But if i enter the data through toad it is being stored in the database & it is retrived sucesfully

Report message to a moderator

Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437409 is a reply to message #437406] Mon, 04 January 2010 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your date is in the wrong format / invalid.
The fact that you can enter dates in Toad proves nothing.
If you want more help post the code.

Report message to a moderator

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 Go to previous messageGo to next message
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;

Report message to a moderator

Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437412 is a reply to message #437406] Mon, 04 January 2010 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the datatype of :TB_TIMESHEET_DETAILS.DATE_1 and the other date items?
And which trigger is giving the error?

Report message to a moderator

Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437413 is a reply to message #437412] Mon, 04 January 2010 05:57 Go to previous messageGo to next message
katangur.nikethan
Messages: 31
Registered: December 2009
Location: Hyderabad
Member
I used Date datatype for that field & the error Occures when WHEN BUTTON PRESSED

--Nikethan

Report message to a moderator

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 Go to previous messageGo to next message
cookiemonster
Messages: 13938
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.

Report message to a moderator

Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437418 is a reply to message #437415] Mon, 04 January 2010 06:12 Go to previous messageGo to next message
katangur.nikethan
Messages: 31
Registered: December 2009
Location: Hyderabad
Member
st_1 is not a number format it's a char & i am desplaying the time format as 10:00 A.M. I also changed the property of the date_1 field to char from date. But it gets the same error.

--K.Nikethan Reddy

Report message to a moderator

Re: frm-40735: WHEN BUTTON PRESSED trigger raised unhandled exception ORA-01847 [message #437427 is a reply to message #437406] Mon, 04 January 2010 06:37 Go to previous message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
In that case my fix should work as stated, assuming the problem is what I think it is.
Did you try my fix (for all the dates) before you changed the datatype of the date columns?
If you did then you're going to have to work out which exact line is giving the error, use messages to do so.

Report message to a moderator

Previous Topic: Password should not be displayed in the database
Next Topic: Copilation of Forms10g in Unix environment
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 08:17:13 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.