Is there a way to embed an alter query into a oracle sql procedure?

Question

I am trying to create a procedure that I can call upon that would alter the date format and I am having some issues and could use some help.

If I try this code:

create or replace PROCEDURE DATE_SESSION IS 

BEGIN
    ALTER SESSION SET NLS_date_format = 'MM/DD/YYYY';
END;

I get this error:

Error starting at line : 1 in command -
DATE_SESSION
Error report -
Unknown Command

And if I try this code:

create or replace PROCEDURE DATE_SESSION IS 

    BEGIN
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_date_format = 'MM/DD/YYYY';';
    END;

And then execute it with:

DATE_SESSION

I get this error:

Error starting at line : 1 in command -
DATE_SESSION
Error report -
Unknown Command

Any insight would be greatly appreciated.

Answer 1

You have to use double apostrophe.
I don't recommend discarding the time component of dates values.

BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_date_format=''MM/DD/YYYY''';
END;

Answer 2

If you're going to use a lot of dynamic SQL, it's worth learning the alternative quoting syntax. This syntax lets us embed code within code without doubling the number of single quotes. This makes the code more readable and easier to test.

create or replace procedure date_session is 
begin
    execute immediate
    q'[
        alter session set nls_date_format = 'MM/DD/YYYY'
    ]';
end;
/

However, I wonder if this question is an X-Y problem. Changing the date format is intended to be done so that different clients can view data in their preferred format. Setting the format inside a stored procedure makes me wonder if it's being set to fix some program that foolishly assumed a specific date format. You should never have to change the session level date format to make a program work. If that's the case here, it's better to modify the original program and remove any implicit date conversions. (On the other hand, we can't always control the source code, so these solutions are sometimes necessary.)