1

I have a need to change the session's NLS_CALENDAR parameter using a PL/SQL procedure (Note: it is a constraint in the application I am working that I must use a procedure).

I am trying to do this:

-- this procedure is a part of pl/sql package Test_Pkg
PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION
      SET NLS_CALENDAR = :cal'
      USING IN calendar_;
END Set_Nls_Calendar;

Then in the client side, I am trying to invoke the procedure:

Test_Pkg.Set_Nls_Calendar('Thai Buddha');

But this get's me ORA-02248: invalid option for ALTER SESSION.

sampathsris
  • 21,564
  • 12
  • 71
  • 98

1 Answers1

3

You can't use bind variables in DDL statements, only in some parts of DML statements. At the moment :cal is being seen as an identifier, not a bind, so you're trying to do:

ALTER SESSION SET NLS_CALENDAR = :cal

... which would give the same error as :cal isn't a valid option. The calendar_ value isn't being used at all.

You have to concatenate the value, while enclosing it in quotes:

PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION SET NLS_CALENDAR = ''' || calendar_ || '''';
END Set_Nls_Calendar;
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    That worked. I did not know that I **can't use bind variables in DDL statements**. Thanks, I learned something. – sampathsris Aug 22 '14 at 10:36
  • 1
    [Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/questions/25489002/why-cannot-i-use-bind-variables-in-ddl-scl-statements-in-dynamic-sql) – sampathsris Aug 25 '14 at 23:34