Results 1 to 4 of 4

Thread: Date Format

  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Red face Unanswered: Date Format

    Hi all,

    I have text files with insert statements like this :
    Insert into x values( 1, '01-FEB-2005', 7 , ....)
    but I have problem with '01-FEB-2005'

    I changed NLS_DATE_FORMAT to DD-MON-YYYY

    but the problem still there


  2. #2
    Join Date
    Jan 2004
    Posts
    370
    And what, exactly, is the problem?

    .

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Perhaps the columns aren't in the order you think? (Wild guess.)

    Also you might improve performance by using cursor_sharing = force:

    Code:
    ALTER SESSION SET CURSOR_SHARING = FORCE;
    
    INSERT INTO x (col1, col2, col3) VALUES (1, DATE '2005-02-01', 7);
    ...
    
    ALTER SESSION SET CURSOR_SHARING = EXACT;

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Well, if it is possible (that is : if you are the creator of the text file), I would put :
    Code:
    Insert into x values( 1, TO_DATE('01-FEB-2005', 'DD-MON-YYYY'), 7 , ....);
    Remember explicit convertions are ALWAYS MUCH BETTER than implicit ones. One day you'll forget to alter your session and insert incorrect data .

    Furthermore, I agree with WilliamR : it is a good habit always to precise the columns in an insert statement (though it was not your primary point, WilliamR, was it ? ). Therefore you won't have to recode your statements if you add a column to the table (unless it is a NOT NULL column of course), and you won't have problems if you ever change the order of columns in your table.

    Concerning CURSOR_SHARING, FORCE may help you in such a case (many insert statements with different values) because it will avoid flooding the shared pool with many insert statements that won't ever be reused and potentially kick out of the shared pool other statements that would need to stay in there since they are reused frequently. It will also help a little performance-wise, as WilliamR said, because it will reuse the same context for all these insert statements, doing "soft parses" instead of "hard parses".

    HTH & Regards,

    RBARAER
    Last edited by RBARAER; 12-12-05 at 06:44. Reason: Agree with WilliamR
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •