Solved

ORA-01830 error in forms

Posted on 2004-04-29
20
1,348 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
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.

 
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
 

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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