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.

Using SQL Scripts in Oracle Application Express

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.

How it can be really helpful to use SQL scripts under SQL Workshop?
Let us take an example of any application which is developed using APEX with one form and report. The development team wanted to test it by inserting few records into the form and then viewing the reports. But once the testing is completed the team wanted to clear the data from the base tables and from the reporting tables, logs and other database objects. To achieve this the team can create the delete scripts for each object and execute them on database. This activity can also be used by saving the code into a file within the application so that it can be executed from the application and also be used in future.
In other words it’s a set of SQL commands that are saved into a file as scripts. The file or the script can have more than one SQL commands or it can be a PLSQL block.
Few things we need to keep in mind while using SQL scripts from SQL workshop. 

  • All the SQL Plus commands are ignored in SQL scripts
  • We cannot interact between SQL commands with SQL scripts
  • Bind variables are not supported in SQL scripts
  • We can copy the SQL commands from SQL scripts and run them on SQL Command under SQL workshop

To access SQL Scripts login to your Oracle Application Express.

img-1.JPG

Now click on “SQL Workshop” option.

img-2.JPG

 

Once you see the page as shown in above screen shot you can click on “SQL Scripts” as highlighted .

img-3.JPG

The above screen shot is the page where we can create any new script or use an existing scripts. This page will show a report where

we can view all the scripts that are created by the current user and if the user is a workshop administrator then it will display

all the scripts created in this workspace. We can also change the view by applying the filters.

img-4.JPG
The search options (i.e. a magnifying glass shown as above) can be used to search any specific columns or any columns. The view icons will change the view of the reports to display as ICON based reports. The delete option at the right is used to delete any script that we might need to by selecting the check box. The upload button is used to upload any new scripts from our local system to the SQL scripts and create option is used to create any new script.
Creating a new script :
Click on “Create button”.
img-5.JPG

Once clicked the editor will open as shown below:

img-6.JPG

Now we can enter the script name but the extension is optional so we will leave as it is. To demonstrate I am using a select statement to select all the records from employee table with name as “Sample_Create_Script”.

img-7.JPG

Now click on create.

img-8.JPG

Now we can see our script is created now let us run it. Once you click on run option we will see the below screen.

img-9.JPG

We can click on Run now option to show the result as below:

img-10.JPG

From the above page we can see the execution of the SQL statement is completed and the time taken for the execution. Now when we click on the magnifying glass we will see a summary report as shown in below screen:

img-11.JPG

When wee select the option as “Detail” and click on go we will see the below page as a result.

img-12.JPG

In this page we have an additional option to edit the existing script, so when we click on edit script it will show the below page.

img-13.JPG

In the above page we can modify the existing SQL statements and apply the changes by clicking on “Apply Changes”.
To delete an existing SQL Script:
Select the script that you want to delete by selecting the check box as shown in below screen:

img-14.JPG

Now click on “Delete Checked”. After clicking it will prompt you for confirmation.

img-15.JPG

Click “OK” .

img-16.JPG

Now we can see the script is deleted from the application.
We can export or import the scripts by using the export/import option from the Task lists, so we can download them and save to our local system or may utilize them on another workspace. To know how much we can store, we can check the SQL script quotas by clicking on “Show Quotas” option from Tasks.

 

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 )

Solve Error: ORA-65096 in 12c

In 12c database when we try to create any user we commonly see the error “Error: ORA-65096” .

We can solve this problem by setting up the undocumented hidden parameter i.e. “_ORACLE_SCRIPT” to true.  In other way we have to user the prefix by adding “C##” or “c##” before the username in container database but by enabling the hidden parameter from logging as SYS user we will be able to create the user without adding the prefix.

Here is a small sample how to create the user when we face error 65096.

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 19:04:49 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

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 opt
ions

SQL>create user sloba identified by ****** ;

Once you hit enter you will see the below error:


<pre>Error starting at line : 1 in command -
create user sloba identified by ******
Error report -
SQL Error: ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
           that was not valid for common users or roles.  In addition to
           the usual rules for user and role names, common user and role
           names must start with C## or c## and consist only of ASCII
           characters.
*Action:   Specify a valid common user or role name.
Error starting at line : 3 in command -

 

To solve this error enable the hidden parameter to “True”:

SQL>alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>reate user sloba identified by ***** ;

User SLOBA created.

SQL>grant connect , resource to sloba;

Grant succeeded.

Verify by logging into the database with the new user:

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 19:04:49 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

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 opt
ions

SQL> conn sloba
Enter password:
Connected.
SQL>

Thank you for reading my blog ,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 )

Introduction to SQL Command Tool in Oracle APEX

Introduction:

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.

The SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.
We can use most of the actions using SQL command tool that we have seen using APEX object browser. For example creating any database objects or manipulating it. But in object browser we cannot execute the simple SQL statements that we can use here. In general we can utilize this tool like how we use SQL Developer tool to query or manipulate the database objects.
Navigation to SQL Command tool:

  • Login to your APEX application:

1.png

 

Click on “SQL Workshop” as shown in above screen shot.
2.png

 

Now you can see under “SQL workshop” we have an option to select “SQL Commands” . This is the place where we can select and write any SQL statements or any PLSQL code to execute and check the output. Once you select the option as shown in above screen shot we will see the below screen which is called as “SQL Command Tool”.
3.png

 

The above screen shot provide the details on where we can write our SQL or PLSQL statements and how we can execute it. The tool is basically divided into two sections , in the top section we can type any SQL queries  or PLSQL block and the bottom section is the place where we get the results of SQL/PLSQL block.
The below screen shot shows an example like how we can execute SQL and PLSQL statements on the top sections and see the result in the bottom sections.
SQL-Tool.png

 

In the top section of the SQL commands tool we can see there is button called “Clear Command” , to clear the screen of the top section this option is used. Form the above screen shot we can see that we have executed on PLSQL block now let us see what will happen when we click on the  “Clear Command” button.

 
4.png

 

From the above screen we can see that only the top section is cleared where we have written our PLSLQ code to execute but the result remains the same unless the next SQL or PLSQL statements are executed.
We also see another button beside that called ” Find Tables” , once clicked on it we will see another window which helps us to search any table name from our database and check the details of the columns and its types.
6.png

I selected the table name called “SI_USERS_T” .
7.png

We can create an database object using SQL command tool for example let us create a simple table called “XX_EMP” :
8.png

From the above screen shot we can see that we have created the table successfully and now let us write one PLSQL block to insert few records.
9.png

Now let us query the table and see the result.
10.png

We can also check the explain plan for the query that was executed by clicking the Explain tab in the bottom section of the SQL command tool.
11.png

 

This tool also allows us to save any queries using the save option on top section beside “Run” button.  Once we click on “SAVE” we will get the window to give a name and provide some description as shown in below screen.
12.png

Once you filled the information then click on SAVE as shown in the above screen shot.  Now if we want to see what the SQL that are been saved , we can check under “Saved SQL” under the bottom section of the SQL command tool .
13.png

Now we know how to use SQL command tool. Both SQL command tool and object browser allows us to view and modify the database objects but the only difference is the interface .  This tool is for those who are familiar with Oracle SQL and PLSQL.

 

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 )

Introduction to Oracle APEX SQL Workshop- The object browser

Summary

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.

 

In this blog I am going to show how we can use and utilize SQL Workshop before we start developing any web application using APEX. In my earlier articles I have shown how to install and upgrade to APEX 5 version. Here are the links to my earlier articles:

Before we get into the SQL Workshop we must have an account or we must have installed APEX on server or desktop. I installed Oracle database 11gR2 and then installed Oracle APEX 5. Assuming that we have the Oracle APEX set-up done and we open the URL of APEX version 5 we will see the screen below:

1.jpg

We have to login to the ADMIN account and create a workspace where we can login and start developing the application using the builder or we can use SQL Workshop. In my case I have created a workshop earlier and now I will login into the application.
2.jpg

We can see the SQL workshop options as shown in the above image. Navigate to SQL Workshop where we can see there are various options. This will allow us to manipulate the database directly from APEX. The two major options like “Object browser and SQL Commands”.

3.jpg

Click on “Object Browser” and we will see the screen below.

4.jpg

 

The object browser provides a graphical interface to interact with the database easily. We can utilize this option to see each description of each table, its column properties, constraints, indexes and triggers including contents. It also allows us to make any changes to descriptions or the data of a table.

5.jpg

 

From the drop down we can choose the object types that will later will display the details. For example we select the options as procedures and then we will see the list:

6.jpg

Let us see the search option available on the object browser and how we can use it. For example I have selected Table as object type and want to search a table name called “SI_USERS_T”.

7.jpg

This will allow us to see all the tables that have “SI_USERS”; if we use like operators this will not list any tables. For example say we try to search like “%SI_USERS%’ then we will not get any results.

8.jpg

 

The refresh button present just beside the search operation will display all the objects where the object type is “TABLE” as this option is already selected by us.

9.jpg

 

Select one of the tables. Once we select any of the table using object browser we will see the options highlighted.

10.jpg

Let us see what the options available under “data” are:

11.jpg

We can see that we can edit the data, add another row, check the count by clicking count option, query the table by putting conditions to each column, and scroll. We can also download the data in CSV format.

12.jpg

 

The object browser also allows us to create a list of the object types.

13.jpg

 

Click on the option “Table” from the drop down. The interface allows us to give the table name and columns details.

14.jpg

15.jpg

 

Once you have filled the information — the table name, how many columns you need and what the datatype should be — click on Next button. To show an example I am calling my table name as “slobatab” and used two columns, one with number and another with varchar2 datatypes.

16.jpg

 

We can define the Primary key of the table or we can leave it, based on our needs. If we want to use the primary key then we can define a sequence to prepopulate the data using a sequence number. Here we will use a primary key on our “ID” field and use sequence but will create it using the option ”Populated from a new sequence” .

17.jpg

Now click on Next button, and we will see the options to create the foreign key.

18.jpg

Next, we will get the option to specify the constraints. I am using the default option and proceeding further.

19.jpg

 

Finally we will see the option to create our table using the object browser.

20.jpg

 

Click on Create Table button.

21.jpg

Now we see the table is created. This is one example of how we can utilize the object browser.

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 )

Composite Queries in Oracle

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​

What are composite queries in Oracle ?
When multiple queries are joined together using any set operators within SQL then they are called composite queries. UNION, INTERSECT, MINUS, UNION ALL are some of the operators used to get certain desired results.

Syntax of composite queries:

SELECT <<COLUMN NAME>>
FROM <<TABLE NAME>>
[ WHERE ] [ GROUP BY … ] 
{ set operator }
SELECT <<COLUMN NAME>>
FROM <<TABLE NAME>>
[ WHERE … ] [ GROUP BY … ] 
[ ORDER BY … ]

 

We can use multiple set operators and add more SQL queries to satisfy any business logic but we have to make sure that the ORDER BY clause is always at the end of the final query.
The syntax should be something like below:

SELECT <<COLUMN NAME>>
FROM <<TABLE NAME>>
[ WHERE … ] [ GROUP BY … ]  
{ set operator }
SELECT <<COLUMN NAME>>
FROM <<TABLE NAME>>
[ WHERE … ] [ GROUP BY … ]  
{ set operator }
SELECT <<COLUMN NAME>>
FROM <<TABLE NAME>>
[ WHERE … ] [ GROUP BY … ]  
[ ORDER BY … ]

 

To know more about the syntax you can refer to SQL Language Reference book from Oracle’s on-line documentation, Chapter 19 “SQL Statements: SAVEPOINT to UPDATE”. Select the section “SELECT” or try searching for “Recursive Subquery Factoring “. In this section you will find all the Set operators used for composite SQL queries.

UNION ALL: This returns the union of everything and includes the duplicate rows too.
UNION: This operator will remove the duplicate if both queries have the same set of rows.
INTERSECT: Will fetch all the rows present in both the queries like the in operator. 
MINUS: This will fetch all the rows that are present in one query and remove the others from it; this is simple subtraction.

To demonstrate the composite queries look at these examples.
Example 1:

Connected to Oracle Database 11g Release 11.2.0.1.0 
Connected as *****

SQL> SELECT 'A', 'B'
   FROM DUAL
 UNION
 SELECT 'C', 'D' FROM DUAL; 
 
'A' 'B'
--- ---
A   B
C   D

 

If you run this individually you will see the first query will fetch A and B with column names A and B, and the next query will fetch C and D with column names C and D, but when we run both of them using the UNION set operator, we get all the rows but the column names refer to the first query and not the second one. Even if we define the column names we still get the first query column names and try to match the datatype:

SQL> SELECT 'A' as col1, 'B' as col2
   FROM DUAL
 UNION
 SELECT 'C' as col3, 'D' as col4 FROM DUAL;

COL1 COL2
---- ----
A    B
C    D

 

For example if I am using a number in first query and VARCHAR in second query, what will happen? If the datatype does not match then we will see the error as below:

SQL> SELECT 'A' as col1, 1 as col2
  FROM DUAL
UNION
SELECT 'C' as col3, 'D' as col4 FROM DUAL;

SELECT 'A' as col1 ,1 as col2  FROM DUAL UNION SELECT 'C' as col3,'D' as col4 FROM DUAL

ORA-01790: expression must have same datatype as corresponding expression

 

There is another factor we need to consider while writing composite SQL queries:  “ORDER BY“. This should always be at the end of a composite query as like below:

SQL> SELECT 'A' as col1, 'B' as col2
  FROM DUAL
UNION
SELECT 'C' as col3, 'D' as col4
  FROM DUAL
UNION
SELECT 'E' as col5, 'F' as col6 from DUAL order by 1;

COL1 COL2
---- ----
A    B
C    D
E    F

 

If we reorder the positions of the values from third query to the second, we still have ORDER BY 1 set (alphabetical order):

SQL> SELECT 'A' as col1, 'B' as col2
  FROM DUAL
UNION
SELECT 'E' as col3, 'F' as col4
  FROM DUAL
UNION
SELECT 'C' as col5, 'D' as col6 from DUAL order by 1;

COL1 COL2
---- ----
A    B
C    D
E    F

 

This shows the error we will get if we put the ORDER BY condition in the second or first query and remove it from the last query:

  SQL> SELECT 'A' as col1, 'B' as col2
   FROM DUAL
 UNION
 SELECT 'C' as col3, 'D' as col4
   FROM DUAL
  order by 1
 UNION
 SELECT 'E' as col5, 'F' as col6 from DUAL;

SELECT 'A' as col1, 'B' as col2
   FROM DUAL
 UNION
 SELECT 'C' as col3, 'D' as col4
   FROM DUAL
  order by 1
 UNION
 SELECT 'E' as col5, 'F' as col6 from DUAL

ORA-00933: SQL command not properly ended

SQL> SELECT 'A' as col1, 'B' as col2
  FROM DUAL
 order by 1
UNION
SELECT 'C' as col3, 'D' as col4
  FROM DUAL
UNION
SELECT 'E' as col5, 'F' as col6 from DUAL;

SELECT 'A' as col1, 'B' as col2
  FROM DUAL
 order by 1
UNION
SELECT 'C' as col3, 'D' as col4
  FROM DUAL
UNION
SELECT 'E' as col5, 'F' as col6 from DUAL

ORA-00933: SQL command not properly ended

SQL>

 

We can see that if we put the ORDER BY in above query we get the “ORA-00933” error. But if we remove the ORDER BY condition and move it to the end we will not see any error in the composite query:

SQL> SELECT 'A' as col1, 'B' as col2
  2    FROM DUAL
  3  UNION
  4  SELECT 'C' as col3, 'D' as col4
  5    FROM DUAL
  6  UNION
  7  SELECT 'E' as col5, 'F' as col6 from DUAL order by 1
  8  ;

COL1 COL2
---- ----
A    B
C    D
E    F

 

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 )