oracle nls_setting inside pl/sql procedure.

In some procedures or functions we may need to set the NLS parameter. we can do this like alter session statement.

The package  dbms_session can be used to achieve that.

Syntax :

 dbms_session.set_nls('nls_date_format','''your foramt here''');

Example

  1  declare
  2  str varchar2(4000);
  3  begin
  4   dbms_session.set_nls('nls_date_format','''dd/mm/yyyy HH24:mi:ss''');
  5  select sysdate into str from dual;
  6  dbms_output.put_line(str);
  7* end;
SQL> /
08/12/2016 12:33:02