Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-01861: literal does not match format string

Posted on 2003-11-10
4
Medium Priority
?
79,306 Views
Last Modified: 2011-08-18
Hi Gurus,

I have a table WO as:

SQL> Desc WO
actstart    date
actfinish   date
estdur     float
wonum    varchar2(10)
....

In a package, I declare:

   type actstart_tab_type is table of date index by binary_integer;
   actstart_tab     actstart_tab_type;

What I want to do it calculate the ACTSTART(date) by using  ACTFINISH (date)  minus ESTDUR (h)
In one of the triggres:

BEGIN
     IF :new.status = 'COMP' THEN
....
      wo_act_time_pkg.actstart_tab(wo_act_time_pkg.actstart_tab.COUNT+1) :=
      to_date((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI');  
     END IF;  
END;
/


and then in other:

UPDATE WO  SET ACTSTART= (wo_act_time_pkg.actstart_tab(i)
        where WONUM = wo_act_time_pkg.wo_tab(i);

I  got ORA-01861: literal does not match format string when the first trigger was fired. Tried with many TO_DATE format but could not solve problem. Oracle 8163

Please help.

Thanks
0
Comment
Question by:baonguyen1
  • 2
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 450 total points
ID: 9715310
to_date((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI') has no meaning. It is DATE!!!!


(:new.actfinish-:old.estdur/24)   - use this as date.

If you are curious about the value look at the result so:

to_char((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI')

0
 
LVL 8

Author Comment

by:baonguyen1
ID: 9719985
Thanks  schwertner. It was my big mistake.

It works now.

Cheers,
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9721395
Thanks baonguyen1!
But why you grade the answer with B?
You got the truth, I am not guilty that you were surprised!
0
 
LVL 8

Author Comment

by:baonguyen1
ID: 9722300
I did not aware that I just grade with B. Can I have chance to reverse to A ?

Thanks a lot for your help and sorry for a B grade.

Cheers,

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…
Suggested Courses

782 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