4 Replies Latest reply: Jan 21, 2013 8:37 AM by Purvesh K RSS

    NLS Settings in Stored Procedure

    EmaxG
      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
          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
            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
              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
                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