ContexT

Application contexts provides e the implementation of fine-grained access control. They allow you to implement security policies. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).

The SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

The syntax for the SYS_CONTEXT function is:

SYS_CONTEXT( namespace, parameter, [ length ] )

Namespace is an Oracle namespace which is name of that context.

What is Namespace

A namespace is an abstract container or environment created to hold a logical grouping of unique identifiers or symbols (i.e., names). An identifier defined in that namespace is associated with it. The same identifier can be independently defined in multiple namespaces. That is, the meaning associated with an identifier defined in one namespace may or may not have the same meaning in another namespace.

For example, Bill works for company X and his employee ID is 123. John works for company Y and his employee ID is also 123. The reason Bill and John can be identified by the same ID number is because they work for different companies. The different companies in this case would symbolize different namespaces. There would be serious confusion if the two men worked for the same company, and still had the same employee ID. For instance, a pay-cheque issued to employee ID 123 would not identify which man should receive the cheque.

USERENV is an Oracle provided namespace that describes the current session ; It have so many parameters.

Eg: SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr,SYS_CONTEXT('USERENV', 'HOST', 16) host,SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL', 8) netprtc
,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) curruser,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) currschema FROM DUAL;

To set Grant
GRANT CREATE ANY CONTEXT TO schema_name;

Context is of two types Normal and Accessed Globally .
In Normal context is connection based; is not valid across sessions;Accessed globally context is visible across sessions.

create context some_context using some_package; (NORMAL)
create context global_context using global_package accessed globally;

Eg: (NORMAL)

create or replace context cntx_test using pk_test;

create or replace package pk_test as
procedure set_value_in_context(some_value in varchar2);
end pk_test;


create or replace package body pk_test as
procedure set_value_in_context(some_value in varchar2) is
begin
dbms_session.set_context('cntx_test', 'test_attribute', some_value);
end set_value_in_context;
end pk_test;


exec pk_test.set_value_in_context('PLSQL');

select sys_context('cntx_test', 'test_attribute') from dual;

Globally Accessed

create context global_context using global_package accessed globally;

create or replace package global_package as
procedure set_value_in_context(global_value in varchar2);
end global_package;


create or replace package body global_package as
procedure set_value_in_context(global_value in varchar2) is
begin
dbms_session.set_context('global_context', 'global_attribute', global_value);
end set_value_in_context;
end global_package;


exec global_package.set_value_in_context('valid accross sessions');

select sys_context('global_context', 'global_attribute') from dual;

One main Benefit of Context is parametrizing views with contexts

For parametrizing views Normal context are to be created because which is session dependent .

Example

conn scott/scott


create context ctx_vw_emp using pk_ctx_vw_emp;


create or replace view vw_emp as select * from emp where deptno =sys_context('ctx_vw_emp','dept');


create package pk_ctx_vw_emp as
procedure set_department (depno in number);
end;
/


create package body pk_ctx_vw_emp as
procedure set_department(depno in number) is
begin
dbms_session.set_context('ctx_vw_emp', 'dept', depno);
end set_department;
end pk_ctx_vw_emp;
/


exec pk_ctx_vw_emp.set_department('10');

select * from vw_emp;

exec pk_ctx_vw_emp.set_department('30');

select * from vw_emp;
---------------------------

Insert Statements

Insert into (select S_DESIGNATION, S_DESCRIPTIONDESG from designation_mast where N_DESGCODE=2) values ( 'tttttttttt','ere')

which is similar to

Insert into designation_mast(S_DESIGNATION, S_DESCRIPTIONDESG) values ( 'tttttttttt','ere')

Oracle Workspace Manager

Difference between Long Transaction (Oracle WorkSpace) and Short Transaction (Normal )

Long Transactions
-----------------------

Complete over days or weeks
Likelihood of conflict is low - Optimistic concurrency permits conflicts

Selective versioning tables
Update creates a new row version
Each update is part of a short transaction

Collections of updates isolated in workspaces until merged into production

Multi user update

Short Transactions
----------------------
Complete in seconds or less
Likelihood of conflict is high - Pesimistic concurrency permits conflicts
Data is in a single state
Updates accessible upon commit
Single user updates

-----------------------------------------------------------

WorkSpace Manager Operations
----------------------------------------

Workspace : Create , Refresh,merge,rollback,remove,goto,compress,alter
Savepoints : Create , Alter,Goto,Compare,Rollback and Delete
History : Gotodate
Privileges : Access,Create,Delete,Rollback,Merge
Locks : Exclusive and Shared (Exclusive lock prevents changes by any other user Shared locks allow other users in the workspace to change row )
Differences : Compares savepoints and workspaces
Detect /Resolve Conflicts : choose version to merge.

Since the versioning process removes the physical unique index from the base table, multiple session would be allowed to enter the same value into a column that has a unique constraint defined on it. Since the changes by the other session might be part of an uncommitted transaction, the instead of triggers that are defined on the view are unable to enforce the constraint in this particular case. As a result,OWM use the _LCK VIEW to prevent this scenario by maintaining a unique constraint on the underlying table which the dmls are applied to.


Disabling Versioning for a table
------------------------------------

Disable versioning when changes to the version-enabled table are completed
Improves performance
Workspace hierarchy and savepoints remain
The latest version of each row in LIVE workspace remains.

Freeze WorkSpace
---------------------
Freezing a workspace specifies the kind of user access allowed to the workspace .

NO_ACCESS is default
READ_ONLY allows all workspace users to read.
1WRITER: Sessions are allowed in the workspace, but only one user
1WRITER_SESSION: Sessions are allowed in the workspace, but only the database session (as opposed to the database user) that called the FreezeWorkspace procedure is allowed to perform write operations
WM_ONLY: Only Workspace Manager operations are permitted.

--------------------------------------------------------------

Detailed Calender Query

SELECT TRUNC( sd + rn ) time_id,
TO_CHAR( sd + rn, 'fmDay' ) day_name,
TO_CHAR( sd + rn, 'D' ) day_number_in_week,
TO_CHAR( sd + rn, 'DD' ) day_number_in_month,
TO_CHAR( sd + rn, 'DDD' ) day_number_in_year,
TO_CHAR( sd + rn, 'W' ) calendar_week_number,
( CASE WHEN TO_CHAR( sd + rn, 'D' ) IN ( 1, 2, 3, 4, 5, 6 ) THEN
NEXT_DAY( sd + rn, 'SATURDAY' ) ELSE
( sd + rn ) END ) week_ending_date,
TO_CHAR( sd + rn, 'MM' ) calendar_month_number,
TO_CHAR( LAST_DAY( sd + rn ), 'DD' ) days_in_cal_month,
LAST_DAY( sd + rn ) end_of_cal_month,
TO_CHAR( sd + rn, 'FMMonth' ) calendar_month_name,
( ( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) - TRUNC( sd + rn, 'Q' ) + 1 ) days_in_cal_quarter,
TRUNC( sd + rn, 'Q' ) beg_of_cal_quarter,
( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) end_of_cal_quarter,
TO_CHAR( sd + rn, 'Q' ) calendar_quarter_number,
TO_CHAR( sd + rn, 'YYYY' ) calendar_year,
( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
- TRUNC( sd + rn, 'YEAR' ) ) days_in_cal_year,
TRUNC( sd + rn, 'YEAR' ) beg_of_cal_year,
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' ) end_of_cal_year
FROM ( SELECT TO_DATE( '12/31/2002', 'MM/DD/YYYY' ) sd,
rownum rn FROM dual CONNECT BY level <= 6575 )
/

REGEXP 3

To get decimals points and vicevers


SELECT REGEXP_REPLACE('18.01', '(\d+)\.(\d+)', '\1') FROM dual;

SELECT REGEXP_REPLACE('18.0991', '(\d+)\.(\d+)', '\2') FROM dual;


For Credit-Card Number System

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r
FROM dual;

To get decimals points

select (18.9009-floor(18.9009))*power(10,length((18.9009-floor(18.9009)))-1) f from dual

or

SELECT REGEXP_REPLACE('18.9009', '(\d+)\.(\d+)', '\2') FROM dual;


Convert a name 'first middle last' into the 'last middle first' format


SELECT REGEXP_REPLACE('Hubert Horatio Hornblower','(.*) (.*) (.*)','\3 \2 \1') "Reformatted Name" FROM dual ;

To remove dollar sign

SELECT REGEXP_REPLACE('$1,234.56','\$',' ') FROM dual;


SELECT REGEXP_REPLACE('This is a test','t.+','XYZ') FROM dual;

SELECT REGEXP_REPLACE('Mississippi', 'si', 'SI', 1, 0, 'i') FROM dual;