Difference between SQL & PL/SQL

Difference between SQL & PL/SQL:

What is Oracle?

I cover this answer in the entry, Learn Oracle - What is Oracle?. I also cover SQL and PL/SQL very briefly in that article. I'll try to be a little more explicit in this entry.

What is SQL?

SQL is the Structured Query Language. Actually, I think I covered SQL pretty nicely in the article Sudhat commented on, Intro to basic SQL.

I'll just add that SQL is composed of DML and DDL. DML are the keywords you use to access and manipulate data, hence the name Data Manipulation Language. DDL are the keywords you use to create objects such as views, tables and procedures, hence the name Data Definition Language. Examples of DML are SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Examples of DDL are CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, etc.

Example DDL:

CREATE TABLE whatever ( column1 NUMBER NOT NULL PRIMARY KEY, column2 VARCHAR2(10), column3 DATE );
Example DML:

INSERT INTO whatever (column1, column2, column3) VALUES (1, 'Text Data', sysdate ); SELECT column1, column2, column3 FROM whatever; COMMIT;

What are SQL Commands?

SQL commands are commands that aren't actually a part of the SQL standard but are supported by the tools that support SQL. For example, SQL*Plus has been around a long time and many tools that allow scripting also allow SQL*Plus commands. When someone asks me about a SQL command, I first clarify if the mean a SQL keyword or a SQL*Plus (or some other tool) command.

SQL commands are usually meant to help format output: BREAK, BTITLE, COLUMN, PRINT or they are meant to create or store data or scripts: COMPUTE, DEFINE, STORE, SAVE. There are also commands that interact with the database: SHUTDOWN, CONNECT, COPY. And there is at least one that interacts with data: XQUERY.

So when you hear someone refer to a SQL command, first ask if they really mean SQL or if they mean the SQL tool they are using.

What is PL/SQL?

The quick answer is from the PL/SQL User Guide:

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.

But what does that mean? The key here are the words: procedural extension of SQL. PL/SQL is a procedural language like C++, Java, ADA, etc. If has variables and variable declarations, conditional controls like IF and CASE. It has looping structures such as LOOP, FOR LOOP and the WHILE LOOP. PL/SQL uses SQL to use, manipulate and save data to the database.

If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

Example PL/SQL:

BEGIN -- A PL/SQL cursor FOR c1 IN ( SELECT * FROM whatever ) -- This is SQL called by PL/SQL LOOP DBMS_OUTPUT.PUT_LINE( 'Column1 is: ' || TO_CHAR(c1.column1) || ', Column2 is: ' || c1.column2 || ', Column3 is: ' || TO_CHAR(c1.column3 ) ); END LOOP; END;

And now the answer to the main question: what is the difference between SQL and PL/SQL? SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don't normally have a "SQL application". You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.

I hope that clears it up some! In the next few days, I am going to repost a couple of articles I wrote last year on basic SQL syntax. I already posted part 1 here (and that was the post that Sudhat was commenting to). I figure to complete the set, I'll post the other two and if they get a decent response, I may continue the series.

                                              OR

There are so many variants of SQL that it is hard sometimes to figure out what to use. Here is a short article that briefly explains the difference between SQL and PL/SQL. Enjoy!
What is SQL?
SQL (pronounced “sequal”) stands for Structured Query Language. Withe SQL, you can view data – called Data Definiton Language or DDL and manipulate data – called Data Manipulation Languate or DML. All of the above are just a fancy way to say that with SQL, the user can both view and alter records in the database. To help , here are a couple of queries:
DDL or View
SELECT * FROM employees
View all of the records in the employee table
DML or Manipulate
UPDATE employees SET employeefirstname = ‘John’ WHERE employeeid = 101
Find the record for employee ID 101 and change the first name to John
What is PL/SQL?
The official answer is from the PL/SQL User Guide:
PL/SQL, Oracle’s procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.
Huh? That is what I thought at the beginning. But at a high level, all this means is that it can do all of the things that regular SQL can do, but also, it is procedural and can be used like a programming language (C++, Java, etc.) For instance, you can use loops and If . . . Then statements in your PL/SQL statements (Programs).
Here is a definition of PL/SQL from Lewis Cunningham (an Oracle database expert):
“If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.
Mr. Cunningham also does a very good job of spelling out the differences between SQL and PL/SQL.
SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don’t normally have a “SQL application”. You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.
Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.
To muddy the waters a little more, while you cannot write an application in SQL, you can use a procedural language to automate parts. These are called Stored Procedures and here is a link to all you want to know about stored procedures on this site.

                                                              OR

ORACLE ---------- * It is a software package, that is used to handle or to maintaine the databse. * It is a software to give the structure of database storage. * Oracle 7, Oralce 8, Oracle 8i, Oracle 9i, Oracle 11i are available in market. * It is a company called" Oracle " to produce this software.


SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. Typically, each block performs a logical action in he program. Oracle can invoke a PL/SQL program by placing it in the SQLPLUS.
PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

the best is ORACLE as it is platform dependant and can run in UNIX and windows. whereas SQL only can run in windows.

easiest? i would say that, if you know SQL, it would be easy to know Oracle as Oracle is just an expansion of SQL.

Recommended Books For Oracle DBA

1. ORACLE DOCUMENTATION – http://tahiti.oracle.com
please read the following guides for attaining ultimate knowledge (for 11g release 2)
        a. concepts
        b. administrator’s guide
        c. 2 day DBA
        d. utilities
        e. backup and recovery basics
        f. backup and recovery advanced user’s guide
        g. performance tuning guide
        h. 2 day+ performance tuning guide
        i. oracle clusterware and oracle RAC administration and deployment guide
        j. 2 day+ RAC guide
        k. Data Guard Concepts and Administration
        l. Data guard broker
2. Expert Oracle Database Architecture 9i and 10g programming techniques – by Thomas Kyte (apress publications)
3. Expert Oracle 10g/11g Administration – by Sam R Alapati (apress publications)
4. Oracle 9i/10g/11g DBA Handbook – Oracle press (Tata Mcgrahill in India)
5. Oracle Insights – by Thomas Kyte (apress publications)
6. Oracle Tuning – by Donald K Burleson (rampant books)
7. Oracle Dataguard – by Bipul Kumar (rampant books)
8. Oracle 10g Grid & RAC – by Mike Ault & Madhu Tumma (rampant books)
9. RMAN Recipes for Oracle database 10g – by Darl Kuhn, Sam R Alapati and Arup Nanda (Apress publications)
10. Linux Recipes for Oracle DBAs – Darl Kuhn, Charles Kim and Bernard Lpouz (Apress publications)
11. Oracle Database 11g, A Beginner’s Guide  – By Ian Abramson, Michael Abbey, Michael J. Corey (Oracle press)
12. Oracle Db 10G Rman Backup & Recovery – by Matthew Hart and Robert G.Freeman (Oracle press)
13. Pro Oracle database 10g RAC on Linux – By Julian Dyke, Steve Shaw (Apress publications)
14. Oracle 10g RAC: grid, services & clustering – By Murali Vallath (Digital Press)
15. Oracle Database 10g Linux administration By Edward Whalen (Oracle press)
16. http://www.oracle.com/technology/pub/articles/tech_dba.html
17.Oracle Privacy Security Auditing by Arup nanda and Donald Burleson (rampant books)

User Management Category

Archive for the ‘User management’ Category

script to list all privileges for user

/* Setting formatting options */
SET TERMOUT OFF
SET SERVEROUTPUT ON
SET VERIFY OFF;
SET FEEDBACK OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE

/* Define bind variables */
VAR i_depth NUMBER;
VAR i_user VARCHAR2(32);

/* Retrieve user input */
SET TERMOUT ON

ACCEPT USER  PROMPT ‘User: ‘
ACCEPT DEPTH  DEFAULT 3 PROMPT ‘Depth [3]: ‘

SET TERMOUT OFF

/* Bind input */
exec :i_depth := &DEPTH;
exec :i_user := ‘&USER’;

SET TERMOUT ON

COLUMN PRIVS FORMAT A150
SELECT (
CASE WHEN LEVEL > 1
THEN LPAD(‘ ‘, 4*(LEVEL-1)) || CHR(212) || CHR(205) || ‘> ‘
END
) || GRANTED_ROLE “PRIVS”
FROM
(
/* THE USERS */
SELECT NULL  AS GRANTEE
, USERNAME  AS GRANTED_ROLE
FROM  DBA_USERS
WHERE UPPER(USERNAME) = UPPER(:i_user)
/* THE ROLES TO ROLES RELATIONS */
UNION
SELECT GRANTEE
, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
/* THE ROLES TO SYS PRIVILEGE RELATIONS */
UNION
SELECT GRANTEE
, PRIVILEGE
FROM DBA_SYS_PRIVS
UNION
/* THE ROLES TO OBJECT PRIVILEGE RELATIONS */
SELECT GRANTEE
, RPAD(DBA_TAB_PRIVS.OWNER || ‘.’ || TABLE_NAME,62,’ ‘) ||  ‘ (‘ || DBA_OBJECTS.OBJECT_TYPE || ‘ -> ‘ || PRIVILEGE || ‘)’
FROM  DBA_TAB_PRIVS
JOIN DBA_OBJECTS ON DBA_OBJECTS.OBJECT_NAME = DBA_TAB_PRIVS.TABLE_NAME
WHERE  OBJECT_TYPE NOT IN (‘PACKAGE BODY’,'SYNONYM’)
)
START WITH GRANTEE IS NULL
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE AND LEVEL <= :i_depth;

granting permissions on v$ views

Sometimes we may get a requirement in which we need to provide access on dynamic performance views like v$session, v$process etc. I had seen this when application team want to capture session information from their webpages.

In such situations, application team will ask to grant select on those views. when you try the same as just like normal grant statement, you will get following error

SQL> grant select on v$session to PAST1;
grant select on v$session to PAST1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The reason for this is v$session is synonym to the view v_$session. so you need to grant select on that main view instead of synonym

SQL> grant select on v_$session to PAST1;

Grant succeeded.

Same you need to follow for all other v$ views. But for data dictionary views, you can directly grant permissions

SQL> grant select on dba_users to PAST1;

Grant succeeded.

Note : Due to security reasons, never we should encourage granting permissions on data dictionary of Oracle until very much required

Some info reg Auditing

Server Setup
Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command.
SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
———————————— ———– ——————————
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

SQL>Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }The following list provides a description of each setting:

none or false – Auditing is disabled.
db or true – Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended – As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system’s audit trail.
Note. In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

To enable auditing and direct audit records to the database audit trail, we would do the following.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN
SQL> STARTUP

CONNECT sys/password AS SYSDBA
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;These options audit all DDL and DML, along with some system events.
DDL (CREATE, ALTER & DROP of objects)
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
SYSTEM EVENTS (LOGON, LOGOFF etc.)
Next, we perform some operations that will be audited.

CONN audit_test/password
CREATE TABLE test_tab (  id  NUMBER);
INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;
DROP TABLE test_tab;

View Audit Trail
The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:
SELECT view_name
FROM   dba_views
WHERE  view_name LIKE ‘DBA%AUDIT%’
ORDER BY view_name;

VIEW_NAME
——————————
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

SQL>The three main views are:

DBA_AUDIT_TRAIL – Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL – Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing.
The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.

COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail
WHERE  owner = ‘AUDIT_TEST’
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME
———- ———————————– ———- ———- —————————-
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00  AUDIT_TEST TEST_TAB   CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00  AUDIT_TEST TEST_TAB   INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00  AUDIT_TEST TEST_TAB   UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00  AUDIT_TEST TEST_TAB   SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00  AUDIT_TEST TEST_TAB   DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00  AUDIT_TEST TEST_TAB   DROP TABLE

6 rows selected.

SQL>When the audit trail is directed to an XML format OS file, it can be read using a text editor or via the V$XML_AUDIT_TRAIL view, which contains similar information to the DBA_AUDIT_TRAIL view.

COLUMN db_user       FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name   FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT db_user,
       extended_timestamp,
       object_schema,
       object_name,
       action
FROM   v$xml_audit_trail
WHERE  object_schema = ‘AUDIT_TEST’
ORDER BY extended_timestamp;

DB_USER    EXTENDED_TIMESTAMP                  OBJECT_SCH OBJECT_NAM     ACTION
———- ———————————– ———- ———- ———-
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00  AUDIT_TEST TEST_TAB            1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00  AUDIT_TEST TEST_TAB            2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00  AUDIT_TEST TEST_TAB            6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00  AUDIT_TEST TEST_TAB            3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00  AUDIT_TEST TEST_TAB            7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00  AUDIT_TEST TEST_TAB           12

6 rows selected.

SQL>Several fields were added to both the standard and fine-grained audit trails in Oracle 10g, including:

EXTENDED_TIMESTAMP – A more precise value than the exising TIMESTAMP column.
PROXY_SESSIONID – Proxy session serial number when an enterprise user is logging in via the proxy method.
GLOBAL_UID – Global Universal Identifier for an enterprise user.
INSTANCE_NUMBER – The INSTANCE_NUMBER value from the actioning instance.
OS_PROCESS – Operating system process id for the oracle process.
TRANSACTIONID – Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
SCN – System change number of the query. This column can be used in flashback queries.
SQL_BIND – The values of any bind variables if any.
SQL_TEXT – The SQL statement that initiated the audit action.
The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL parameter is set to db,extended or xml,extended.

Maintenance and Security
Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest. Over time you can refine the level of auditing to match your requirements.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;The OS and XML audit trails are managed through the OS. These files should be secured at the OS level by assigning the correct file permissions.

Fine Grained Auditing (FGA)
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.

CONN audit_test/password

CREATE TABLE emp (
 empno     NUMBER(4) NOT NULL,
 ename     VARCHAR2(10),
 job       VARCHAR2(9),
 mgr       NUMBER(4),
 hiredate  DATE,
 sal       NUMBER(7,2),
 comm      NUMBER(7,2),
 deptno    NUMBER(2)
);

INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001);
COMMIT;The following policy audits any queries of salaries greater than £50,000.

CONN sys/password AS sysdba

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => ‘AUDIT_TEST’,
    object_name     => ‘EMP’,
    policy_name     => ‘SALARY_CHK_AUDIT’,
    audit_condition => ‘SAL > 50000′,
    audit_column    => ‘SAL’);
END;
/Querying both employees proves the auditing policy works as expected.

CONN audit_test/password
SELECT sal FROM emp WHERE ename = ‘Tim’;
SELECT sal FROM emp WHERE ename = ‘Larry’;

CONN sys/password AS SYSDBA
SELECT sql_text
FROM   dba_fga_audit_trail;

SQL_TEXT
——————————————
SELECT sal FROM emp WHERE ename = ‘Larry’

1 row selected.

SQL>Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => ‘AUDIT_TEST’,
    object_name     => ‘EMP’,
    policy_name     => ‘SALARY_CHK_AUDIT’,
    audit_condition => ‘SAL > 50000′,
    audit_column    => ‘SAL’,
    handler_schema  => ‘AUDIT_TEST’,
    handler_module  => ‘FIRE_CLERK’,
    enable          => TRUE);
END;
/The DBMS_FGA package contains the following procedures:

ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

– Clear down the audit trail.
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

– Apply the policy to the SAL column of the EMP table.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => ‘AUDIT_TEST’,
    object_name     => ‘EMP’,
    policy_name     => ‘SAL_AUDIT’,
    audit_condition => NULL, — Equivalent to TRUE
    audit_column    => ‘SAL’,
    statement_types => ‘SELECT,INSERT,UPDATE,DELETE’);
END;
/

– Test the auditing.
CONN audit_test/password
SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;

– Check the audit trail.
CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
————————————–
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998

4 rows selected.

– Drop the policy.
CONN sys/password AS SYSDBA
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => ‘AUDIT_TEST’,
    object_name     => ‘EMP’,
    policy_name     => ‘SAL_AUDIT’);
END;
/
Courtesy from : www.oracle-base.com