Solved

ORA-01830 error in forms

Posted on 2004-04-29
20
1,353 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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