Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1371
  • Last Modified:

ORA-01830 error in forms

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
sky_lt
Asked:
sky_lt
  • 7
  • 7
  • 3
  • +2
1 Solution
 
musduCommented:
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
 
Helena Markováprogrammer-analystCommented:
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
 
sky_ltAuthor Commented:
Well in the debugger i see that the dates are equal in formats. But i get that error.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Helena Markováprogrammer-analystCommented:
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
 
sky_ltAuthor Commented:
IF
   :global.from = :house.from
THEN insert into orvis.house values(5,5);
ELSE
    message('Date has been changed!');
       END;
END IF;
0
 
Helena Markováprogrammer-analystCommented:
Maybe the problem is because of global variable - it is CHAR and :house.from is DATE ?
0
 
sky_ltAuthor Commented:
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
 
Helena Markováprogrammer-analystCommented:
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
 
annamalai77Commented:
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
 
sky_ltAuthor Commented:
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
 
musduCommented:
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
 
Helena Markováprogrammer-analystCommented:
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
 
sky_ltAuthor Commented:
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
 
Helena Markováprogrammer-analystCommented:
This will work:
:global.form := TO_DATE(Get_Item_Property('house.from',database_value),'DD-MON-RRRR');
0
 
sky_ltAuthor Commented:
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
 
sky_ltAuthor Commented:
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
 
Helena Markováprogrammer-analystCommented:
It is fine that you have solved the problem.
0
 
musduCommented:
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
 
Computer101Commented:
PAQed, with points refunded (50)

Computer101
E-E Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 7
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now