Introduction to Rank Function in Oracle

In this blog, we will see how to use the RANK analytical function in an Oracle database and how it helps us in an analysis.

What is the RANK function?

The RANK function is an Oracle analytical function which calculates the rank of values which are retrieved, or against a set of rows. The same values will share the same rank for a group of rows or the subsequent ranks as they are tied to the values so it might not be consecutive in sequence or number, as they may vary in each case.

Analytical functions are really helpful when we try to use them for reporting purpose and helps in taking business decisions.  To use the RANK function we need to use the “ORDER BY CLAUSE“.

You can learn more about the “Order by Clause” by clicking this link to another one of my articles.

The RANK function will be used after the “ORDER BY” clause is been applied to the rows, as shown in below syntax.

The syntax for RANK Function:

 RANK() WITHIN GROUP (ORDER BY  <<column name>> DESC/ASC) 

In RANK analytical functions we don’t need PARTITIONED BY  conditions to execute successfully.

Let us take an example on getting the ranks of the employees based on the departments.

SELECT FIRST_NAME ,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( ORDER BY salary DESC) "Rank" from EMPLOYEES  WHERE DEPARTMENT_ID =50 ;

Output:

FIRST_NAME                     LAST_NAME                     SALARY   DEPARTMENT_ID       Rank
------------------------------ ------------------------- ---------- --------------- ----------
Adam                           Fripp                           8200              50          1
Matthew                        Weiss                           8000              50          2
Payam                          Kaufling                        7900              50          3
Shanta                         Vollman                         6500              50          4
Kevin                          Mourgos                         5800              50          5
Nandita                        Sarchand                        4200              50          6
Alexis                         Bull                            4100              50          7
Sarah                          Bell                            4000              50          8
Britney                        Everett                         3900              50          9
Kelly                          Chung                           3800              50         10
Renske                         Ladwig                          3600              50         11 Jennifer                       Dilly                           3600              50         11 Trenna                         Rajs                            3500              50         13 Julia                          Dellinger                       3400              50         14
Laura                          Bissot                          3300              50         15
Jason                          Mallin                          3300              50         15
Winston                        Taylor                          3200              50         17
Samuel                         McCain                          3200              50         17
Stephen                        Stiles                          3200              50         17
Julia                          Nayer                           3200              50         17
Curtis                         Davies                          3100              50         21
Jean                           Fleaur                          3100              50         21

From the above output, we can see the that we don’t see the RANKS assigned is consecutive in sequence. We can see from the highlighted rows. In this case we see that the RANK 11 is assigned to two employees based on the salary and as they are getting the same salary, it has assigned one rank but the sequence is broken here having 11 in place of 12, which is missing from the assigned ranks and the next number i.e. 13 is assigned to the next row.

Similarly, we see the same scenario for the other rows too.  Here we are only checking for a specific department number. In a case where we need to show the similar results for all the departments, then we can use a partition by clause to the RANK function and the SQL query will be as below:

SELECT FIRST_NAME 
,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( PARTITION by department_id ORDER BY salary DESC) "Rank"
from EMPLOYEES ;

Expected result:

FIRST_NAME                     LAST_NAME                     SALARY   DEPARTMENT_ID       Rank
------------------------------ ------------------------- ---------- --------------- ----------
Jennifer                       Whalen                          4400              10          1
Michael                        Hartstein                      13000              20          1
Pat                            Fay                             6000              20          2
Den                            Raphaely                       11000              30          1
Alexander                      Khoo                            3100              30          2
Shelli                         Baida                           2900              30          3
Sigal                          Tobias                          2800              30          4
Guy                            Himuro                          2600              30          5
Karen                          Colmenares                      2500              30          6
Susan                          Mavris                          6500              40          1
Adam                           Fripp                           8200              50          1
Matthew                        Weiss                           8000              50          2
Payam                          Kaufling                        7900              50          3
Shanta                         Vollman                         6500              50          4
Kevin                          Mourgos                         5800              50          5
Nandita                        Sarchand                        4200              50          6
Alexis                         Bull                            4100              50          7
Sarah                          Bell                            4000              50          8
Britney                        Everett                         3900              50          9
Kelly                          Chung                           3800              50         10
Jennifer                       Dilly                           3600              50         11
Renske                         Ladwig                          3600              50         11
Trenna                         Rajs                            3500              50         13
Julia                          Dellinger                       3400              50         14
Laura                          Bissot                          3300              50         15
Jason                          Mallin                          3300              50         15
Stephen                        Stiles                          3200              50         17
Samuel                         McCain                          3200              50         17
Julia                          Nayer                           3200              50         17
Winston                        Taylor                          3200              50         17
Jean                           Fleaur                          3100              50         21
Curtis                         Davies                          3100              50         21
Alana                          Walsh                           3100              50         21

So now we understand how to use the RANK function in Oracle.

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

 

Advertisements

Partition By Clause in Oracle

An introductory discussion about how to use Partition by Clause in Oracle.

What is a “partition by” clause in Oracle?

It is used to break the data into small partitions and is been separated by a boundary or in simple dividing the input into logical groups. The analytical functions are performed within this partitions.  So when the boundaries are crossed then the function get restarted to segregate the data. The “partition by” clause is similar to the “GROUP BY” clause that is used in aggregate functions. They are also known as query partition clause in Oracle.

The syntax of PARTITION BY CLAUSE:

# ANALYTIC FUNCTION or FUNCTION NAME () OVER ( PARTITION BY COLUMN NAME )

Any arguments that need to be passed to the functions then it has to be passed within the parenthesis and after which we have to write the keyword OVER where the partition by will be used.   The column name is used where we want to use clause.

Example of PARTITION BY CLAUSE:

Let us try to identify the total or cumulative cost of each department when it comes to expenditure on salary.  To show this let us take the example from the Employee table which stores the data of the employee, department ID and salary etc..………continue reading from my original article that was published on Experts-Exchange.com and provides about how to use Partition by Clause in Oracle.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Installing Oracle 18c on Windows Operating system

In this article, we will see how to install Oracle 18c on the Windows operating system.  To install 18c first we need to download the setup from Oracle.com. In my case, I have already downloaded and extracted locally on my windows machine.

The below file will show the step by step guide on how to install Oracle 18c enterprise edition on Windows environment.

Install 18c on Windows

 

Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

 

Analytic Functions in Oracle

An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.

Analytic functions are used to calculate or compute aggregate values based on a group of rows, it is also called as a Window. This determines the range of rows used to perform the calculations from the current row. The analytic functions only work on top of the results that are retrieved from the SQL queries. This helps us to analyze the data much easier.

These types of functions also help us in calculating cumulative totals. The most common use of these functions on identifying the ranks for example if we need to know the top persons who are highly paid or top 5 sales executive those who have done the best sales for a year.Their functions are not affected by “Group by” or “Where” or “Having” clause in Oracle.

Main differences between analytic and aggregate functions

The main difference between these two functions is that aggregate functions are used to return a single result value based on a group of rows. For example, if we need to identify the total number of audits till date from Audit table, then we need to use count function to determine the number of row in audit object. Now if we use the analytic way then this will return multiple rows with certain results within a window.

Examples below show how to retrieve the results from an audit table using analytic function and on aggregate function.

Below is the select clause using aggregate function “sum” with expression list i.e. Salary column from Employee table joining with from clause returns the total amount spent by a company on salary.

Now let us see if we can use analytical functions on the same table to get different results.

From the above SQL, we can see that the same Sum functions are now used as analytic functions to get the sum, but on cumulative salary. When we use the analytic function, we must use “OVER” and we also need to use order by or partition by as analytic.

This article was just an introduction to Oracle Analytic Functions.

 

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Using full redaction for masking the column values in 12c

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle’s Advanced Security.

 

Why would we need to mask a column’s data? One of the most common uses is to enable us to hide sensitive data from being shown on the screen of any application.

There are multiple ways to do this and one of the ways is to use DBMS_RLS policy. Here is one nice article published by Surya.  In this tutorial, however, we will explore how we can make use of full redaction option from DBMS_REDACT to mask the column values. The actual reference of masking can be taken from Oracle documentation from here.

In this article I am going to explain only one part i.e. full redaction, but there are other features which can also be used based on our business requirement. such as:

Partial redaction:  

Using this option we can mask only certain portions of the column. For example, say if we want to mask all digits except the last 4 numbers from our credit card or our SSN number then we can use partial redaction.

Regular expressions:

This type of option can be used to mask characters based on patterns search.

Random redaction:

We can dynamically generate any dummy number that can be displayed on an application’s screen based on the data type of the column.

No redaction:

This type of option is only used to test the redaction policy. To test the code and verify the application before moving the actual making policy to any production environment.

To use full redaction type from DBMS_REDACT we need to first grant the schema, in my case I am going to take the example from SCOTT schema.  In 10g onward we used “DBMS_RLS.ADD_POLICY” to use to mask the columns now let us take a look at how we can use dbms_redact object in a 12c environment.

First, let’s give the “GRANT” to the schema.

oracle@dev-VirtualBox ~ $ sql 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 1 14:28:18 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> GRANT EXECUTE ON sys.dbms_redact TO SCOTT; 
Grant succeeded. 
SQL> 

 
Now let us select the column which we want to mask , in this example I am using existing schema on oracle i.e. SCOTT and object name is EMP table. 

 

SQL> select ename, sal from emp; 
ENAME SAL
---------- ---------- 
SMITH 800 
ALLEN 1600 
WARD 1250 
JONES 2975 
MARTIN 1250 
BLAKE 2850 
CLARK 2450 
SCOTT 3000 
KING 5000 
TURNER 1500 
ADAMS 1100 
JAMES 950 
FORD 3000 
MILLER 1300 

14 rows selected.

Open in new window

Now, let's add the policy to mask the SAL column from the EMP table. To add the policy we can use something like the example given below:
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> 
BEGIN DBMS_REDACT.add_policy( object_schema => 'scott', object_name => 'EMP', column_name => 'SAL', policy_name => 'redact_sal', function_type => DBMS_REDACT.full, expression => '1=1' ); END;
/ 


PL/SQL procedure successfully completed. 

Open in new window


Let's verify our table now.
SQL> select ename, sal from emp; 
ENAME SAL ---------- ---------- 
SMITH 0 
ALLEN 0 
WARD 0 
JONES 0 
MARTIN 0 
BLAKE 0 
CLARK 0 
SCOTT 0 
KING 0 
TURNER 0 
ADAMS 0 
JAMES 0 
FORD 0 
MILLER 0 

14 rows selected. 

Open in new window


So we can now see the masking is successfully implemented.

Now, let’s see how we can use “SYS_CONTEXT” with the DBMS_REDACT package.

At the start of this article, I shared a link to where we can use “DBMS_RLS” but there, the only part we were using is to create a standalone function inside the DBMS_RLS package, but here we will see how we can use SYS_CONTEXT while adding the policy.  To demonstrate this I am going to create some sample tables that were used in the same blog.

So let’s first create the table:

SQL> CREATE TABLE XX_TEMP (EMP_SSN NUMBER , ENAME VARCHAR2(100), HIREDATE DATE); 

Table XX_TEMP created. 

Select allOpen in new window

Now, let’s add some sample data:

 
SQL> begin 
insert into xx_temp values (1234, 'VINAY', SYSDATE) ; 
insert into xx_temp values (1111, 'VENKET', SYSDATE) ; 
insert into xx_temp values (1234, 'SURYA', SYSDATE) ; 
commit; 
end; 

PL/SQL procedure successfully completed. 

Select allOpen in new window

So now our table will look something like this:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> conn scott 
Enter password: 
Connected. 
SQL> select * from XX_TEMP; 

EMP_SSN ENAME HIREDATE
 ---------- ---------------------------------------- 
1234             VINAY          02-FEB-18 
1111              VENKET       02-FEB-18 
1234             SURYA          02-FEB-18 

SQL> 

Open in new window

 

Now, let’s add the policy using SYS_CONTEXT:

 
BEGIN DBMS_REDACT.add_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
column_name => 'EMP_SSN', 
policy_name => 'redact_syscontext', 
function_type => DBMS_REDACT.full, 
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SCOTT''' ); 
END;
/ 

PL/SQL procedure successfully completed. 

Open in new window

Now let’s grant the same table to other schema and see how the table will look:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> conn scott 
Enter password: 
Connected. 

SQL>GRANT SELECT ON SCOTT.XX_TEMP TO sloba; 

Grant succeeded.  

Open in new window

Now, let’s login with sloba and verify the table.

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 2 09:51:19 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> conn sloba 
Enter password: 
Connected. 
SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
---------- --------------------- --------- 
0 VINAY 02-FEB-18 
0 VENKET 02-FEB-18 
0 SURYA 02-FEB-18 

SQL> conn scott 
Enter password: 
Connected. 

SQL> select * from xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 

SQL> 

Open in new window

From the above, we can see that the policy is applied for other users, other than scott user.

If we don’t want to use the policy we can drop it by using the following command:

SQL> 
BEGIN 
DBMS_REDACT.drop_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
policy_name => 'redact_syscontext' );
END;
 / 

Open in new window

Now, let’s verify from sloba user after dropping the policy:

 
SQL> conn sloba 
Enter password: 
Connected. 

SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 
SQL> 

Open in new window

So now you can see that after dropping the policy we can view all the data from the object that we masked earlier.

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Getting monthly data with latest records in oracle

In this blog I am going to show how we can get monthly data for a given set of records. In many cases we try to get the data into reports with monthly , weekly etc. To achive this we can use N number of tools which are provided but sometime we have to write it because there is a need by other teams or external sources to make use of these records. Let us take an small example for my table which has the records something like as below :

 

From the above result set we need to pull only the yellow colored once. Why only the yellow , the query that we need to build is like to get the audits for every month. Now if the same audit is performed in two months multiple times with changed status then we need the latest record for each month.

Here are some sample tables with insert scripts:


create table xx_audit_tab
(
AUDIT_ID VARCHAR2(4000)
,AUDIT_TITLE VARCHAR2(4000)
,MON VARCHAR2(12 BYTE)
,AUDIT_STATUS VARCHAR2(1020)
,AUDIT_CLASSIFICATION VARCHAR2(4000)
,AUDIT_TYPE VARCHAR2(4000)
,INSTANCE_ID NUMBER
,CREATION_DATE DATE
,STAGE_NAME VARCHAR2(120)
,MAX_DATE DATE );

Insert statement for sample data :

begin
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0081','Test Audit#1','SEP','Audit Started',null,null,10852947,to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('20-SEP-16 15.59.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0082','Test_0921','SEP','Audit Started',null,null,10890405,to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 00.06.59','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10907640,to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('21-SEP-16 03.37.42','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912648,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Audit Started','Audit','Cycle - Limited',10912358,to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 04.35.02','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0083','Audit 01','SEP','Final Report','Audit','Cycle - Limited',10918771,to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('21-SEP-16 05.59.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158734,to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '),'Approve_Audit',to_timestamp('23-SEP-16 05.58.41','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0086','Test_0923_LE_MLOV','SEP','Audit Started',null,null,11158877,to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 05.59.34','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11179201,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178808,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Cancelled',null,null,11445557,to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('26-SEP-16 15.17.04','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Audit Started',null,null,11178658,to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.38.13','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0087','923_Audit','SEP','Fieldwork',null,null,11186517,to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 11.03.11','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Re-opened',null,null,14396114,to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('19-OCT-16 00.19.26','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','OCT','Cancelled',null,null,15273698,to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '),'Manage_Audit_End',to_timestamp('25-OCT-16 11.28.17','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179664,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11413653,to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 09.02.29','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Final Report',null,null,11414199,to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.47','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415951,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Closed',null,null,11415952,to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('26-SEP-16 10.25.54','DD-MON-RR HH24.MI.SS '));
Insert into xx_audit_tab (AUDIT_ID,AUDIT_TITLE,MON,AUDIT_STATUS,AUDIT_CLASSIFICATION,AUDIT_TYPE,INSTANCE_ID,CREATION_DATE,STAGE_NAME,MAX_DATE) values ('AUD-0088','923_Audit2','SEP','Audit Started',null,null,11179552,to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '),'Create_Audit',to_timestamp('23-SEP-16 09.46.38','DD-MON-RR HH24.MI.SS '));
commit;
end;

 

To get the desired result we need to use “ROW_NUMBER” an analytical function .

SQL code:


select * from (
SELECT *
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY mon,audit_id ORDER BY creation_date DESC) RN
FROM xx_audit_tab t
)sub
WHERE RN = 1 )
order by audit_id
;

 

 

SQL Error: ORA-00921 in Oracle 12c

How to solve if you get SQL error “ORA-00921” while dropping an existing user in Oracle 12c.

I got one user existing on my database called “SLOBA”. Now let us try to drop it.

 

drop user SLOBA cascase ;

Output as error:


Error starting at line : 4 in command -
drop user SLOBA cascase
Error report -
SQL Error: ORA-00921: unexpected end of SQL command
00921. 00000 - "unexpected end of SQL command"
*Cause:
*Action:
Error starting at line : 4 in command -
drop user SLOBA
Error report -
SQL Error: ORA-28014: cannot drop administrative users
28014. 00000 - "cannot drop administrative users"
*Cause: An attempt was made to drop administrative users.
Administrative users can be dropped only by SYS during
migration mode.
*Action: Try dropping administrative users during migration mode.

 

Now alter the system session “_oracle_script” to true.

alter session set "_oracle_script"=true;

Output :

Session altered. 

Now let us try to drop the user again.


drop user SLOBA cascade;
User SLOBA dropped.