• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4939
  • Last Modified:

How to use 'alter session' in a package

Hi Experts,

I need to use 'alter session' for two dozen functions stored in a package. Is there a way to alter the session for the entire package, or do I need to repeat these lines in every function?

This raises also the entire question of 'alter session' scope. If a function uses 'alter session', and if I use that function in a query, how would that affect things? The name suggests that this is a global setting, but perhaps there is a way to alter some settings locally (only for a package in my case). I would prefer that my functions do not have side effects.

I couldn't find any good explanation in the documentation, perhaps I just missed the right paragraph...

Thanks
Markus -- (°v°)
0
harfang
Asked:
harfang
  • 10
  • 3
  • 3
  • +3
4 Solutions
 
Shaju KumbalathCommented:
plz refer the below url
http://www.dba-oracle.com/t_pl_sql_one_time_only_procedure.htm
 
u can call the alter session  withot an end statement in the package body so that it will called in first time only in the session
0
 
Devinder Singh VirdiCommented:
How you are calling these package? You can use this command before calling procedure as well. If you want to check whether your session has changed desired parameter use the following query.
select id, name, value
from gv$ses_optimizer_env
where
sid = <>
order by name;
0
 
MilleniumaireCommented:
To call DDL from within pl/sql you will need to run it as dynamic sql e.g.:

begin
  execute immediate 'alter session .....';
end;

More info here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
harfangAuthor Commented:
shajukg,

Thanks, this is what I was looking for. Unfortunately, 'alter session' doesn't work there, nor does it work within the functions. I guess I will have to manage that elsewhere.

Is there an alternate syntax for setting session attributes?

    <temporary> alter session set nls_sort = generic_m_ai;
    <do stuff>
    <temporary> alter session set nls_sort = binary;
    <do other stuff>

I basically need to control how '=', '>' and '<' behave. Sometimes I need a linguistic comparison, sometimes I need a binary comparison...


virdi_ds,

I'm calling the package(s) from an Access front-end. This means I can alter the session at the connection level, or maintain more than one session if I need different settings.

This is still in the "brainstorming" stage. Implementation is fluid and different approaches are welcome.


(°v°)
0
 
harfangAuthor Commented:
Milleniumaire,

The syntax using "execute immediate" is accepted at compile time, but generates a run-time error when the package is used. This is the case where ever I place the statement.

Using "alter session" directly produces a compile error.

Apparently, you can't change session parameters from within a package, function, or procedure.

Is there a "StrComp" function? Something to override the current setting of NLS_COMP?

(°v°)
0
 
sventhanCommented:
Do you have enough privileage to run the alter session?
Check this out...
http://www.experts-exchange.com/Database/Oracle/9.x/Q__24988417.html 
0
 
Franck PachotCommented:
Which runtime error ? they always have an error code and message.
0
 
harfangAuthor Commented:
sventhan,

Yes, I can run 'alter session' from the SQL+ console and from Access, with the expected result. I can't run it within the function, or in the in package initialisation.

franckpachot,

You are right, I should have written it down. I'll get back to this on Monday, I don't have remote access so it will have to wait.

Thanks
(°v°)
0
 
Shaju KumbalathCommented:
USE EXECUTE_IMMEDIATE 'ALTER SESSION...' IN THE PACKAGE
0
 
Devinder Singh VirdiCommented:
Try using execute immediate in procedure.
then using dba account, use the following query, it will show you the setting.
select id, name, value
from gv$ses_optimizer_env
where
sid = <>
order by name;
0
 
harfangAuthor Commented:
Sorry, everybody, this will have to hold until the 4th of January. I have no access to Oracle from home, and the Botanical Garden is closed.

But I will need to resolve this then.

Merry Christmas!
(°v°)
0
 
MilleniumaireCommented:
It looks like this is a permissions issue as I can successfully execute your sample alter session statements from a stored procedure e.g.

create or replace procedure pauls_test1 as
begin
    execute immediate 'alter session set nls_sort = generic_m_ai';
    execute immediate 'alter session set nls_sort = binary';
end;

exec pauls_test1;

Running this (from Toad) gives:

Procedure created.
PL/SQL procedure successfully completed.

You will need to ensure the Oracle account being used to run the execute immediate statement has been granted alter session privilege e.g.
grant alter session to myaccount;

This will usually be the account that owns the package/procedure.
0
 
harfangAuthor Commented:
Thanks. I will try that again in January. I was able to alter the session directly, so I assumed privileges weren't the issue.
(°v°)
0
 
MilleniumaireCommented:
Are the privileges assigned via a role and directly to the Oracle account?  PL/SQL in stored packages/procedures etc requires privileges to be assigned directly to the account, not through roles, which may explain why could run the alter session outside of the package.

See the PL/SQL Blocks and Roles section on the following page:

http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c24privs.htm#4770

Note:
Definer Rights is the norm when creating a package/procedure/function.  The code runs with the privileges of the owner of the package, not the privileges of the person who is running the package.  When pl/sql is run using definer rights then it will ignore roles, hence you will need to assign privileges directly to the owner of the package.
0
 
harfangAuthor Commented:
That makes perfect sense. I wasn't going to study privileges and security until later in the project (this is my first Oracle project), but "Definer Rights" and "Invoker Rights" is clearly an important concept from the start.

(°v°)
0
 
harfangAuthor Commented:
OK, time to close this.

Results: you can indeed perform an "alter session" in a procedure, but not in a package of functions, or in a function for that matter. At least not the way I need it.

  ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

So this point is moot, I need to find another solution to my problem.

(°v°)
0
 
harfangAuthor Commented:
Thanks for the help
0
 
Shaju KumbalathCommented:
have u tried PRAGMA AUTONOMOUS_TRANSACTION in your ackage function?
0
 
harfangAuthor Commented:
Thanks shajukg,

I hadn't, but I just did:

I use a simple function "test_order", basically:

    return case when 'B'<'a' then 'binary' else 'text' end;

and the following:

  function my_problem return nvarchar2 as
  pragma autonomous_transaction;
  begin
    execute immediate 'alter session set nls_comp = ansi';
    return test_order();
  end;

The session gets altered, even when called as a function from a query:

  alter session set nls_comp=binary;
  select  test_order, my_problem, test_order from dual;

Returns binary, text, text. During the first call to test_order, comparison is binary, during the second call, it has changed to text...


Thank you for pointing that out. It now works, and also answers the second part of my question, about the scope of "alter session". I'm guessing you cannot change the setting temporarily.

I'm writing a follow-up question about my latest tests around the same issue. Please check it out.

(°v°)
0
 
Franck PachotCommented:
Hi,
Your solution has a problem because you cannot know in wich order oracle will run the functions.
I suggest you use the NLSSORT() function:

instad of comparing 'B'<'a' you can compare: nlssort('B','nls_sort=binary')<nlssort('a','nls_sort=binary')
or nlssort('B','nls_sort=english')<nlssort('a','nls_sort= english')

Regards,
Franck.
0
 
harfangAuthor Commented:
Yes, this is the path I have been taking this afternoon (see the related question). It will take some time to implement, but it should be more robust in the end (and without any side effects).

(°v°)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now