Home » SQL & PL/SQL » SQL & PL/SQL » not a valid month error
not a valid month error [message #236262] Wed, 09 May 2007 07:57 Go to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
hi i executed the below code. it gives me not a valid month error. ora-01843 can you tell me where the mistake is and what to do to correc this.
DECLARE

TYPE T_CREATE_DATE IS TABLE OF PYMT.CREATE_TSP%TYPE;
V_CREATE_DATE T_CREATE_DATE;

l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;

BEGIN
SELECT CREATE_TSP BULK COLLECT INTO V_CREATE_DATE FROM PYMT;

FORALL INDX IN V_CREATE_DATE.FIRST..V_CREATE_DATE.LAST

UPDATE PYMT
SET CREATE_TSP = TO_DATE('02/03/2007 11:00:00')
WHERE TO_CHAR(CREATE_TSP,'MM/DD/YYYY HH24:MI:SS') = V_CREATE_DATE(INDX);

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' RECORDS ARE UPDATED');
DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME-L_START)/100,2)||' SECONDS');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

anand
Re: not a valid month error [message #236270 is a reply to message #236262] Wed, 09 May 2007 08:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
See if you find any differences between the two blocks below.

SQL> set serverout on
SQL>
SQL> DECLARE
  2     mydate   DATE;
  3  BEGIN
  4     mydate    := TO_DATE ('02/03/2007 11:00:00');
  5  EXCEPTION
  6     WHEN OTHERS
  7     THEN
  8        DBMS_OUTPUT.put_line (SQLERRM);
  9  END;
 10  /
ORA-01843: not a valid month

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> DECLARE
  2     mydate   DATE;
  3  BEGIN
  4     mydate    := TO_DATE ('02/03/2007 11:00:00','dd/mm/yyyy hh24:mi:ss');
  5     dbms_output.put_line('all is well');
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.put_line (SQLERRM);
 10  END;
 11  /
all is well

PL/SQL procedure successfully completed.


MHE
Re: not a valid month error [message #236273 is a reply to message #236270] Wed, 09 May 2007 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry Maarten but
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.put_line (SQLERRM);
 10  END;

Is a bad way to code and should not be shown as an example that could be follow by juniors.

Why not just removing this useless part?

Regards
Michel

Re: not a valid month error [message #236276 is a reply to message #236273] Wed, 09 May 2007 08:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 09 May 2007 15:15
Sorry Maarten but
<snip>
Why not just removing this useless part?
You're right. Here's an improved variant. Same question for the OP: do you notice any differences:
SQL> set serverout on
SQL>
SQL> DECLARE
  2     mydate   DATE;
  3  BEGIN
  4     mydate    := TO_DATE ('02/03/2007 11:00:00');
  5     dbms_output.put_line('all is well');
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 4


SQL>
SQL>
SQL> DECLARE
  2     mydate   DATE;
  3  BEGIN
  4     mydate    := TO_DATE ('02/03/2007 11:00:00','dd/mm/yyyy hh24:mi:ss');
  5     dbms_output.put_line('all is well');
  6  END;
  7  /
all is well

PL/SQL procedure successfully completed.

SQL>


MHE
Re: not a valid month error [message #236310 is a reply to message #236262] Wed, 09 May 2007 09:40 Go to previous messageGo to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I have changed the date format as TO_DATE(DATE,'MM/DD/YYYY HH24:MI:SS'). Eventhough I'm getting the same error. Please clarify me.

Anand
Re: not a valid month error [message #236317 is a reply to message #236310] Wed, 09 May 2007 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you see how Maarten post his example?
Post the same way.

Regards
Michel

[Updated on: Wed, 09 May 2007 10:12]

Report message to a moderator

Re: not a valid month error [message #236349 is a reply to message #236310] Wed, 09 May 2007 12:48 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And the difference between your post an Maarten's in that he is using a string and you are using a column. Does that help any?
Re: not a valid month error [message #236412 is a reply to message #236262] Wed, 09 May 2007 23:32 Go to previous messageGo to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
No it is not helping. Can any one suggest me a right one. I am using forall statment in my procedure and taking the date column to update. it is not updating and gives me a not a valid month error. Any right answer is appriciated.

thanks
anand
Re: not a valid month error [message #236417 is a reply to message #236262] Wed, 09 May 2007 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Any right answer is appriciated.
Would you like some cheese with you whine?
You post no actual error message(s), but expect volunteers solve your problem when we can't see your code & what you are doing.
You're On Your Own (YOYO)!
Re: not a valid month error [message #236418 is a reply to message #236412] Wed, 09 May 2007 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again: why don't you post your execution as Maarten post his?

If you don't: YOYO.

Regards
Michel
Re: not a valid month error [message #236512 is a reply to message #236412] Thu, 10 May 2007 02:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So far we got this:
  1. In the procedure below: ORA-01843: not a valid month
    DECLARE
       TYPE t_create_date IS TABLE OF pymt.create_tsp%TYPE;
    
       v_create_date   t_create_date;
       l_start         NUMBER        DEFAULT DBMS_UTILITY.get_time;
    BEGIN
       SELECT create_tsp
       BULK COLLECT INTO v_create_date
       FROM   pymt;
    
       FORALL indx IN v_create_date.FIRST .. v_create_date.LAST
          UPDATE pymt
          SET create_tsp = TO_DATE ('02/03/2007 11:00:00')
          WHERE  TO_CHAR (create_tsp, 'MM/DD/YYYY HH24:MI:SS') =
                                                              v_create_date (indx);
       DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' RECORDS ARE UPDATED');
       DBMS_OUTPUT.put_line (   ROUND ((DBMS_UTILITY.get_time - l_start) / 100, 2)
                             || ' SECONDS'
                            );
    END;
  2. I've posted the suggestion that your TO_DATE calls should have a date format.
  3. The error remains


If you want further help with this, we need the following
- The modified code.
- DATA TYPE of pymt.create_tsp. Because in the SET clause of your update, you are assigning a DATE to it but in the WHERE clause you are comparing it to a VARCHAR2. This means that in one or the other there's some implicit casting involved. That's probably (we cannot be sure unless you post more details) your culprit.

MHE
Re: not a valid month error [message #236617 is a reply to message #236262] Thu, 10 May 2007 07:02 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
Hi,

I assume PYMT.CREATE_TSP is a date field.

Please try with this one.

DECLARE
   TYPE T_CREATE_DATE IS TABLE OF PYMT.CREATE_TSP%TYPE index by binary_integer;
   V_CREATE_DATE T_CREATE_DATE;
   l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
BEGIN
   SELECT trunc(CREATE_TSP) BULK COLLECT INTO V_CREATE_DATE FROM PYMT;
   dbms_output.put_line(V_CREATE_DATE.count);
   FORALL INDX IN V_CREATE_DATE.FIRST..V_CREATE_DATE.LAST
      UPDATE PYMT
      SET CREATE_TSP 
         = TO_DATE('02/03/2007 11:00:00','MM/DD/YYYY HH24:MI:SS')
      WHERE trunc(CREATE_TSP) = V_CREATE_DATE(INDX);
   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' RECORDS ARE UPDATED');
   DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME-L_START)/100,2)||' SECONDS');
END;

[Updated on: Thu, 10 May 2007 07:27] by Moderator

Report message to a moderator

Re: not a valid month error [message #236625 is a reply to message #236617] Thu, 10 May 2007 07:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
kdipankar, the trunc() is not correct according to the format anand provided in his TO_CHAR.

MHE
Re: not a valid month error [message #236634 is a reply to message #236625] Thu, 10 May 2007 07:47 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
Hi,
I think if he does not want to match the date upto seconds but only upto year then trunc should work. It seems from the code that that is the case. Anyway if we assume that PYMT.CREATE_TSP is a date field what could be the solution?
Also changing a date field in a table to character and then comparing it with another date-to-char field is a very bad practice.


regards,
Dipankar.
Re: not a valid month error [message #236782 is a reply to message #236262] Fri, 11 May 2007 00:36 Go to previous messageGo to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
hi myself got the solution. thanks for the effort. but in forums MHE should use some genuine words and not like culprit. when a junior member asks question you should take the patience in such a way to answer.otherwise don't give the answer. it is not professional way of answering. please change your way of attitude towards your junior members.

once again thanks everyone for the reply.


Re: not a valid month error [message #236796 is a reply to message #236782] Fri, 11 May 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You ask for help.
You don't post what is requested to help.
It's not a professional way of asking.
You should make the effort to post question correctly otherwise don't ask.
You have to change your attitude towards those who help you.

Finally, you don't post the "solution" you found.
Why keep it for you alone?
Share is the word in forum, isn't it?
Please change your attitude if you still want to be helped.

Regards
Michel

Re: not a valid month error [message #236805 is a reply to message #236782] Fri, 11 May 2007 01:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pstanand wrote on Fri, 11 May 2007 07:36
hi myself got the solution. thanks for the effort. but in forums MHE should use some genuine words and not like culprit. when a junior member asks question you should take the patience in such a way to answer.otherwise don't give the answer. it is not professional way of answering. please change your way of attitude towards your junior members.

once again thanks everyone for the reply.


dictionary.com
Cul·prit /ˈkʌlprɪt/ Pronunciation Key - Show Spelled Pronunciation[kuhl-prit] Pronunciation Key - Show IPA Pronunciation
–noun
1. a person or other agent guilty of or responsible for an offense or fault.
2. a person arraigned for an offense.
Culprit is a genuine word, but perhaps I should have been more clear. That was NOT a comment towards you, but to your code. I meant to say that pymt.create_tsp was treated both as a date and as a varchar2, one of those two lines (the SET clause or the WHERE clause involving pymt.create_tsp) was the one causing your error (i.e. the "culprit"). That's the curse of not speaking English as your mother tongue but as a third language: sometimes you express yourself in a less fortunate way. There was absolutely no offense intended to any member of this forum.

But, we're glad you found the solution yourself. What was the error? What did you do to solve it. It's nice to share some feedback to the forum.

MHE

[Updated on: Fri, 11 May 2007 01:16]

Report message to a moderator

Re: not a valid month error [message #236813 is a reply to message #236782] Fri, 11 May 2007 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pstanand wrote on Fri, 11 May 2007 07:36
but in forums MHE should use some genuine words and not like culprit. when a junior member asks question you should take the patience in such a way to answer.otherwise don't give the answer. it is not professional way of answering. please change your way of attitude towards your junior members.

Ever heard of a dictionary? Or is that word too difficult too?
If you took the effort of first looking up the word culprit, you would have seen that Maaher was HELPING you!
Talking about non-professionalism...
Re: not a valid month error [message #236830 is a reply to message #236262] Fri, 11 May 2007 01:54 Go to previous messageGo to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
The discussion is moving on different direction. Let we all put a full stop to that. Actually the pymt.create_tsp is a date field. I missed giving the date format as ('MM/DD/YYYY HH24:MI:SS') while setting the date in the update statement. Later I found this and corrected my code. You people are taking too much patience to find the dictionary and trying to teach me english. I don't have any concern with this. But forum must be a healthy one. So i want to make fullstop for this. Looking forward for your help.

thanks
Re: not a valid month error [message #236837 is a reply to message #236830] Fri, 11 May 2007 02:10 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the feedback. I'll lock this one to end the discussion.

MHE
Previous Topic: Data stored in DB is not in the same order as they were inserted
Next Topic: insert using table in pl/sql
Goto Forum:
  


Current Time: Thu Dec 08 08:09:08 CST 2016

Total time taken to generate the page: 0.15746 seconds