Changing NLS_DATE_Format in Oracle Procedure

Question

is it possible to change NLS_DATE_Format in a function of Oracle

I have a function like this

   create or replace Procedure GETFLOWSHEETRESULT 
    (
....
    ) AS 

    BEGIN
    alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
    Select AVNR,Wert From E_MW_01MIN_MIT Where  DBTM='17-06-2018 16:20:00' and AVNR In (2085,2075,2089,2071);
    alter session set nls_date_format = 'DD-MON-RR';
    END GETFLOWSHEETRESULT;

but it gives me error:

Error(8,4): PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe The symbol "update was inserted before "ALTER" to continue.

I want to change the nls_Date_Format before the select command. as it is per session , how can I change it?

Rene Rene · Accepted Answer · 2018-08-01T05:54:18

It's most common to translate your date string into a date value using the to_date function.

Select AVNR,Wert 
From E_MW_01MIN_MIT 
Where  DBTM=to_date('17-06-2018 16:20:00','DD-MM-YYYY HH24:MI:SS') 
and AVNR In (2085,2075,2089,2071);

If you still want to change NLS_DATE_FORMAT try:

execute immediate 'alter session.... ';

Changing NLS_DATE_Format in Oracle Procedure

1 Answers