EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle SYS_CONTEXT()

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle SYS_CONTEXT()

Oracle SYS_CONTEXT()

Introduction to Oracle SYS_CONTEXT()

SYS_CONTEXT is nothing but an Oracle function that returns relevant information of the current Database session. This function helps to retrieve information about Oracle Environment. This is a parameter based function that accepts parameters and returns the corresponding value of that parameter.

Points of Concentration:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • This function returns information about the current instant.
  • This function can be used in SQL or PL / SQL statements.
  • It must be executed locally
  • SYS_CONTEXT function evaluates once per SQL statement execution.
  • The Function returns the output as a VARCHAR2 data type.
  • The default length of the output is 256 bytes.

Syntax

Oracle SYS_CONTEXT function syntax is as follow

SYS_CONTEXT (Namespace, Parameter, [, Length])

Explanation:

  • Namespace: It is a valid ORACLE SQL Identifier. It must be already created.
  • Parameter: It can be any string. It’s not case sensitive. The length of the parameter must be less than or equal to 30 bytes (length <= 30 bytes). An attribute or value of this parameter must be already set using the DBMS_SESSION.SET_CONTEXT procedure.
  • Length: It is optional. It is used to change or override the length using the ‘length’ parameter. The valid range of length parameter is 1 to 4000 bytes. Oracle ignores invalid length range and uses the default range. The default range of return value is 256 bytes.

Operators used with Oracle SYS_CONTEXT()

‘USERENV’ is an Oracle’s built-in Namespace. It is an SQL identifier and is used to retrieve the information of the current database session. The associated parameters with the namespace are information variables.

The parameters are listed below which are associated with namespace ‘USERENV’ are useful to retrieve current session information in the database: All listed parameters are available in Oracle 11g version.

Parameter Description

Length

(bytes)

ACTION It returns application name 32
AUDITED_CURSORID It returns the cursor id of the SQL NA
AUTHENTICATION_DATA Returns Authentication data that are being used for login 256
AUTHENTICATION_TYPE Returns type of user’s authentication i.e. Database, OS, Network, etc. 30
BG_JOB_ID Returns the job id of the current session if Oracle establishes background process, else returns NULL 64
CLIENT_IDENTIFIER Returns client identifier is set by the application through DBMS_SESSION.SET_IDENTIFIER procedure 64
CLIENT_INFO Returns the user’s session info that is stored using the DBMS_APPLICATION_INFO package. 64
CURRENT_SCHEMA Returns the name of the default schema of the current schema 30
CURRENT_SCHEMAID Returns ID of the default schema of the current session 30
CURRENT_SQL

CURRENT_SQLn

It returns the first 4k bytes SQL that triggered for audit and n is an integer from 1 to 7 that return subsequent 4k bytes increment. If n=2 then it returns 8k to 12k bytes. 4
CURRENT_USER Returns the current user name 30
CURRENT_USERID Returns current user-id 30
DB_DOMAIN Returns database domain name that is specified in the DB_DOMAIN initialization parameter. 256
DB_NAME Returns database name that is specified in the DB_NAME initialization parameter. 30
DB_UNIQUE_NAME Returns database name that is specified in the DB_UNIQUE_NAME initialization parameter. 30
ENTRYID Returns current audit entry number 30
EXTERNAL_NAME Returns the external name of the DB user 256
FG_JOB_ID Returns job id that establishes by the client foreground process in the current session else NULL. 30
GLOBAL_CONTEXT_MEMORY Returns number that is being used by the globally accessed context in the system global area NA
HOST Returns client’s machine name 54
INSTANCE Returns current instance identification number 30
IP_ADDRESS Returns client’s machine IP address 30
ISDBA Returns TRUE if a user is DBA else FALSE 30
LANG Returns ISO abbreviation for the language 62
LANGUAGE Returns the language, territory of the current session in this form:language_territory.characterset 52
MODULE Returns application name 48
NETWORK_PROTOCOL Returns communication network protocol 256
NLS_CALENDAR Returns calendar of the current session 62
NLS_CURRENCY Returns currency of the current session 62
NLS_DATE_FORMAT The returns date format of the current session 62
NLS_DATE_LANGUAGE Returns language for expressing dates 62
NLS_SORT Returns type of sorting Binary or Linguistic 62
NLS_TERRITORY Returns Territory name of the current session 62
OS_USER Returns OS user name who initiated database session 30
PROXY_USER Returns user name who initiated database session on behalf of SESSION_USER 30
PROXY_USERID Returns the ID of the PROXY_USER 30
SERVICE_NAME Returns the service name which is connected in the given session 64
SESSION_USER Returns database user name who authenticated current user 30
SESSION_USERID Returns the ID of the SESSION_USER 30
SESSIONID Returns identifier of an auditing session 30
STATEMENTID Returns number of SQL statements that audited in a given session NA
TERMINAL Returns OS identifier for the client of the current session. 10
Note: CURRENT_USER and CURRENT_USERID attributes are being deprecated.SESSION_USER and SESSION_USERID attributes can be used respectively instead.

Examples to Implement Oracle SYS_CONTEXT() function

Below are some examples mentioned:

Example #1

In this section we’ll see the implementation of Oracle SYS_CONTEXT ( )function and its behavior.

Code:

SELECT SYS_CONTEXT('USERENV','CURRENT_USER') User_name FROM DUAL;

Output:

Oracle SYS_CONTEXT()1

Explanation: In the above example parameter, ‘CURRENT_USER returns the user name of the session in which the SQL statement gets executed. But this parameter is being deprecated, so it won’t be available for the use. Use the SESSION_USER parameter instead.

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,901 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

Example #2

Code:

SELECT SYS_CONTEXT('USERENV','SESSION_USER') User_name FROM DUAL;

Output:

scott

Explanation: In the above example, parameter ‘SESSION_USER’ being used instead of ‘CURRENT_USER and it returns the same output.

Example #3

Code:

SELECT SYS_CONTEXT('USERENV','DB_NAME') Database_Name FROM DUAL;

Output:

Orc 1

Explanation: In the above example parameter, ‘DB_NAME’ returns the Database name of the current session in which the SQL statement gets executed.

Conclusion

Oracle SYS_CONTEXT () function is a very useful function to retrieve the relevant information about the current database session. By using Oracle Regular Expressions various useful or important information about the current session can be retrieved that helps us to find out where the SQL is running who or how many users are logged on, the status of the session, etc.

Recommended Articles

This is a guide to Oracle SYS_CONTEXT(). Here we discuss an introduction to Oracle SYS_CONTEXT(), syntax, operators, examples. You can also go through our other related articles to learn more –

  1. Oracle ROLLBACK
  2. Oracle While Loop
  3. Oracle LENGTH()
  4. Oracle UNIQUE Constraint

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Course Training Learn More