This discussion is archived
4 Replies Latest reply: Jan 21, 2013 6:37 AM by Purvesh K RSS

NLS Settings in Stored Procedure

N K Pro
Currently Being Moderated
Hello

I've been struggling for a while with the output of one of my SPs
It goes like this:
Begin
Declare cursor as query
loop and write lines to a file
end

Problem is, I cannot seem to be able to set NLS settings properly. I need the dates to come out as YYYY-MM-DD HH24:MI:SS but it doesnt seem to work at all. Columns are DATE datatype but no matter what I do (Obviously I am doing something wrong!) I am not able to get HH24:MI:SS output only YYYY-MM-DD.

I've tried using TO_DATE in the query, executing immediate ALTER SESSION SET NLS_DATE.. , using DBMS_SESSION.SET_NLS . Nothing seems to work, any help is appreciated (Maybe I am misplacing the DMBS_SESSION.SET_NLS or something?).

Sample code:
CREATE OR REPLACE PROCEDURE TEST  (parameters.. ) AS 
/*variables*/
   
BEGIN 
  DECLARE CURSOR c_AICTT IS 
          SELECT TO_DATE(DATE1,'YYYY-MM-DD HH24:MI:SS'), DATE2 FROM DATES; 
.
.
.
Thanks in advance,
N K
  • 1. Re: NLS Settings in Stored Procedure
    SomeoneElse Guru
    Currently Being Moderated
    SELECT TO_DATE(DATE1,'YYYY-MM-DD HH24:MI:SS'), DATE2 FROM DATES;
    This is a huge mistake. If DATE1 is already DATE type then doing a TO_DATE on it is wrong.
  • 2. Re: NLS Settings in Stored Procedure
    Stew Ashton Expert
    Currently Being Moderated
    Looks like what you want is TO_CHAR, not TO_DATE.

    Don't change the NLS settings; they "belong" to the client, not to your code.

    TO_CHAR will do an explicit conversion, so the NLS settings will not come into play.
  • 3. Re: NLS Settings in Stored Procedure
    riedelme Expert
    Currently Being Moderated
    N K wrote:
    Hello
    Problem is, I cannot seem to be able to set NLS settings properly. I need the dates to come out as YYYY-MM-DD HH24:MI:SS but it doesnt seem to work at all. Columns are DATE datatype but no matter what I do (Obviously I am doing something wrong!) I am not able to get HH24:MI:SS output only YYYY-MM-DD.

    I've tried using TO_DATE in the query, executing immediate ALTER SESSION SET NLS_DATE.. , using DBMS_SESSION.SET_NLS . Nothing seems to work, any help is appreciated (Maybe I am misplacing the DMBS_SESSION.SET_NLS or something?).

    Sample code:
    CREATE OR REPLACE PROCEDURE TEST  (parameters.. ) AS 
    /*variables*/
    
    BEGIN 
    DECLARE CURSOR c_AICTT IS 
    SELECT TO_DATE(DATE1,'YYYY-MM-DD HH24:MI:SS'), DATE2 FROM DATES; 
    TO_DATE() should do the job, as should ALTER SESSION_SET NLS_DATE_FORMAT if done properly.

    Assuming DATE1 is a DATE datatype you should not be using TO_DATE on it since it is already a date. TO_CHAR() for display purposes might be more appropriate
  • 4. Re: NLS Settings in Stored Procedure
    Purvesh K Guru
    Currently Being Moderated
    Check if TO_CHAR helps you.


    It did help me:
    create or replace
    function write_date_to_file
    return number is
    file_name     utl_file.file_type;
    v_date          varchar2(30);
    begin
      dbms_output.put_line('Into Proc...');
         file_name := utl_file.fopen('PURVESH_DIR', 'date.txt','W');
         v_date := to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS');
         utl_file.put_line(file_name, v_date);
         utl_file.fclose(file_name);
      dbms_output.put_line('Finshed proc...');
    end;
    
    
    File Data:-
    21-Jan-2013 20:06:16

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points