Solved

ORA-01830 error in forms

Posted on 2004-04-29
20
1,355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

630 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