Solved

ORA-01830 error in forms

Posted on 2004-04-29
20
1,341 Views
Last Modified: 2007-12-19
Hi i get such an error ORA-01830

In form i have global variable in a pre-update trigger

:global.from := Get_Item_Property('house.from',database_value);

/*:house.form is a date in the database.*/

In post-update trigger i have a condition

if :global.from = :house.from then ........

and then the forms validates this condition i get  ORA-01830. What wron could be?
Thanks

0
Comment
Question by:sky_lt
  • 7
  • 7
  • 3
  • +2
20 Comments
 
LVL 6

Expert Comment

by:musdu
ID: 10956815
Hi,

write :global.from and :house.from before this statement. Propably :global.form has a different date format.

message('global.form :'||:global.form,acknowledge);
message(house.form :'||:house.form,acknowledge);
if :global.from = :house.from then ........

regards.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10956830
I think that the problem is in the validation part which you have not posted here:

if :global.from = :house.from then
....
END IF;

ORA-01830 date format picture ends before converting entire input string

Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.

Action: Check the specifications for date format pictures and correct the statement.
0
 

Author Comment

by:sky_lt
ID: 10956851
Well in the debugger i see that the dates are equal in formats. But i get that error.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10956876
I think that it would be better if you post here your code:

if :global.from = :house.from then
-- what are you doing here ?
END IF;
0
 

Author Comment

by:sky_lt
ID: 10956892
IF
   :global.from = :house.from
THEN insert into orvis.house values(5,5);
ELSE
    message('Date has been changed!');
       END;
END IF;
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10956932
Maybe the problem is because of global variable - it is CHAR and :house.from is DATE ?
0
 

Author Comment

by:sky_lt
ID: 10956945
I tried

IF
   TO_DATE(:global.from,'YYYY.MM.DD') = :house.from
THEN insert into orvis.house values(5,5);
ELSE
    message('Date has been changed!');
       END;
END IF;

 but i get same error
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10957008
You can try this:
IF trunc(TO_DATE(:global.from,'YYYY.MM.DD')) = trunc(:house.from) THEN

or

try directly
IF Get_Item_Property('house.from',database_value)=:house.from THEN
This can be used also in a Pre-Update trigger, I think that the functionality of your form remains the same.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10957217
hi my friend

check the date format of ur database. i think the problem is with the insert statement. ur database is having different date format and u r trying to insert in a different format.

go to sqlplus and type select sysdate from dual.
this will give ur DB date format. accordingly insert the record in the DB .

regards
annamalai
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sky_lt
ID: 10967033
My database date format is YYYY.MM.DD
:global.from is varchar as ir is driven from function Get_Item_Property

The following statement works fine:

if :global.from = to_char(:house.from,'DD-MON-YYY')

But using the following statement i can't insert date to the table

to_date(:global.from,'DD-MON_YYYY');
insert into house values(:global.from,......................


What wrong could be?


0
 
LVL 6

Expert Comment

by:musdu
ID: 10967286
Hi,

please try this;

insert into house values(to_date(:global.from, 'DD-MON-YYYY'), .....

as annamalai said, your db date format is different than format  of your global variable. So you have to convert it into date format using to_date() function.

regards.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10974931
This is not working ?
INSERT INTO house (form) values (to_date(:global.form, 'DD-MON-YYYY'));

By the way - what is the column name - "form" or "from" ?
If it is "from" i would change it, it is a reserved word (http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/f_words.htm#14267)
0
 

Author Comment

by:sky_lt
ID: 10975331
Hi i think i foud the solution, but i get another problem now:

I wrote
in pre-update trigger
:global.form := TO_DATE(Get_Item_Property('house.from',database_value),'DD-MON-YYYY');

in post update i wrote

if :global.form = :house.from
  then insert into orvis.house values(:global.form,...........);

My database date format is YYYY.MM.DD and forms
instead of inserting date '2003.01.01' inserts date '0001.01.03'

Maybe all the problems are of the version FORMS 5.0.6.8.0 i tried to do all these steps on the later forms version and everything was fine.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10975488
This will work:
:global.form := TO_DATE(Get_Item_Property('house.from',database_value),'DD-MON-RRRR');
0
 

Author Comment

by:sky_lt
ID: 10996884
well i tried

:global.form := TO_DATE(Get_Item_Property('house.from',database_value),'DD-MON-RRRR');

 but now i get the ORA-01841: (full) year must be between -4713 and +9999

I do not understand why because everything seems fine if i use a newer verson of forms.
Thanks for any further help
0
 

Author Comment

by:sky_lt
ID: 10997518
At last... I solved the problem , but i do not like it

I wrote
in pre-update trigger
:global.form := TO_DATE(Get_Item_Property('house.from',database_value),'DD-MON-YYYY');

in post-update trigger i wrote

if :global.form = :house.from
  then
 declare
    d date;
    d1 char(11);
   d2 date;
BEGIN
     d := :global.form;
     d1 := to_char(d,'DD-MON-YYYY');
     d2 := to_date(d1,'DD-MON-YYYY');
 insert into orvis.house values(:global.form,...........);



0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11002847
It is fine that you have solved the problem.
0
 
LVL 6

Expert Comment

by:musdu
ID: 11002986
Hi,

if you use a package variable (with date type) instead of a global variable (:global.form), you don't need such format convertions.

regards.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12376107
PAQed, with points refunded (50)

Computer101
E-E Admin
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now