DBMS_SESSION Package
The global variable’s value can be re initialized.
create or replace package p
as
g number := 0;
end;
/
create or replace procedure pr1
is
begin
p.g := p.g 1;
dbms_output.put_line('Value of g is ' || p.g);
end;
/
exec pr1;
exec pr1;
exec pr1;
After executing the modify_package_state procedure the initial value of the global variable g gets initialized.
The global cursors can be closed.
create table tx1 (name varchar2(30));
create or replace package p
is
cursor cf is select * from emp;
procedure pr1;
end p;
create or replace package body p
is
procedure pr1
is
c number := 0;
begin
for i in cf
loop
c := c + 1;
if c <= 3 then
--dbms_output.put_line(i.ename);
insert into tx1 values(i.ename);
else
dbms_session.modify_package_state(dbms_session.reinitialize);
end if;
end loop;
end pr1;
end;
Effective for scheduling a job – After every 2 hours the open cursors should be initialized.
create or replace package P is
cnt number := 0;
cursor c is select * from emp;
procedure print_status;
end P;
/
create or replace package body P is
procedure print_status is
begin
dbms_output.put_line('P.cnt = ' || cnt);
if c%ISOPEN then
dbms_output.put_line('P.c is OPEN');
else
dbms_output.put_line('P.c is CLOSED');
end if;
end;
end P;
/
set serveroutput on;
begin
p.cnt := 111;
p.print_status;
end;
/
P.cnt = 111
P.c is CLOSED
begin
dbms_session.modify_package_state(dbms_session.reinitialize);
end;
/
set serveroutput on;
begin
p.print_status;
end;
/
P.cnt = 0
P.c is CLOSED
UNIQUE_SESSION_ID Function
This function returns an identifier that is unique for all sessions currently connected to this database. Multiple calls to this function during the same session always return the same result.
Syntax
DBMS_SESSION.UNIQUE_SESSION_ID
RETURN VARCHAR2;
Pragmas
pragma restrict_references(unique_session_id,WNDS,RNDS,WNPS);
select DBMS_SESSION.UNIQUE_SESSION_ID from dual;
SET_NLS Procedure
This procedure sets up your national language support (NLS). It is equivalent to the following SQL statement:
ALTER SESSION SET <nls_parameter> = <value>
Syntax
DBMS_SESSION.SET_NLS (
param VARCHAR2,
value VARCHAR2);
Parameters
Table 65-8 SET_NLS Procedure Parameters
Parameter
Description
param
NLS parameter. The parameter name must begin with 'NLS'.
value
Parameter value.
If the parameter is a text literal, then it needs embedded single-quotes. For example, "set_nls('nls_date_format','''DD-MON-YY''')"
declare
format varchar2(30);
begin
format := '&Date_Format';
if lower(format) = 'dd-mon-yy' then
DBMS_SESSION.SET_NLS('nls_date_format','''DD-MON-YY''');
dbms_output.put_line(' The date according to ' || format || ' is ' ||sysdate);
elsif lower(format) = 'dd-mm-yy' then
DBMS_SESSION.SET_NLS('nls_date_format','''DD-MM-YY''');
dbms_output.put_line(' The date according to ' || format || ' is ' ||sysdate);
elsif lower(format) = 'mm-dd-yy' then
DBMS_SESSION.SET_NLS('nls_date_format','''MM-DD-YY''');
dbms_output.put_line(' The date according to ' || format || ' is ' ||sysdate);
else
dbms_output.put_line('Wrong format');
end if;
end;
Discussion about this article
| Author | Body |
Rahul
8/29/2009 3:53 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|