Reorder the columns in CSV file using Python

Many time we work on files to load or process the flat files. In this post, I am showing if we receive the file but the columns are not in order then how to rearrange them in the right format. There are multiple ways to handle this but here I am using a simple python code which will reorder my columns.

Say for example my right order of my file should be as below:

 empno, ename, sal 

But say I receive a file with

 sal, ename, empno 

image

Now here is the simple python script which will change the file into right order :

import csv

with open('file2.csv', 'r') as infile, open('file2_modified.csv', 'a') as outfile:
fieldnames = ['empno','ename','sal']
writer = csv.DictWriter(outfile, fieldnames=fieldnames)
writer.writeheader()
for row in csv.DictReader(infile):
writer.writerow(row)

Save the above code as “target_file_format.py” or anyname you want.

Execute the python script , and check the result.

image

You can modify the code in various ways as per your need.

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 )

Advertisements

Access Windows shared directory from Ubuntu 16.04.3 LTS VirtualBox Guest

In this post I am going to show how to access windows shared directory from Ubuntu 16.04.3 LTS virtual machine.

First create a directory on the host. For example I am creating one directory called on my E drive [ My host is Windows 7 ] .

Now open VirtualBox go to settings of your Ubuntu guest, under shared section add your directory that was created with full permission. Once this is completed login to Ubuntu virtual machine and type the below:

 sudo usermod -aG vboxsf $(whoami) 

Executed on my machine as like below:

sloba@sloba-VirtualBox:~$ sudo usermod -aG vboxsf $(whoami)
[sudo] password for sloba:
sloba@sloba-VirtualBox:~$ reboot

After that reboot your Ubuntu machine you can access the shared drive as shown below.


sloba@sloba-VirtualBox:~$ ls
Desktop Downloads Music Pictures Templates
Documents examples.desktop oraInventory Public Videos
sloba@sloba-VirtualBox:~$ cd /media/
sloba@sloba-VirtualBox:/media$ ls -lrt
total 12
drwxrwx--- 1 root vboxsf 0 Jan 22 21:12 sf_vmshare

sloba@sloba-VirtualBox:/media$ cd sf_vmshare/
sloba@sloba-VirtualBox:/media/sf_vmshare$

 

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 )

 

 

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.

For and While Loops in Python

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.

In python there are two types of loops: 

  • While loop
  • For loop

When we want to run or execute a piece of code multiple times sequentially, this requires a “for loop”, but there is another alternative called a “while loop”. The while loop can only be used when a specific condition is met, or to execute the code for n number of times—or without having a stop.

The While Loop

Let us try to understand the logic of the while loop. Let’s say a person hits a target using his bow till all the arrows are used up. In this case, as long as the container has an arrow in it, the person will take the arrow and hit the target using his bow.
Let’s assume the number of arrows in the container is 10. The target can be shot as long as the number of arrows is greater than zero. The final step would be to put the bow down when all the arrows have been shot.  So basically, as long as the arrows are present in the container, the loop of hitting the target using the bow will continue.  The logic of the while loop is: as long as the condition is true the loop continues.
Format of while loop in Python:

while <<expression>>:
    statements 
    ....
    ....

 

The while loop starts with the keyword “while” as shown in the above snippet. In the expression, we can use any condition that we want to make it true till we want the loop to be executed—followed by a colon.  Next, there could be one or more statements, but a tab is required before any statement. There is one important thing we need to keep in mind: if in any case the expression never becomes false, then the while loop will never stop, making it an “infinite loop”. In this case, the program will never end.
Now let us convert the example into a proper Python program. 

no_of_arrows = 10
while no_of_arrows > 0 :
	no_of_arrows = no_of_arrows -1
	print ("Number of arrows left in the container" + str(no_of_arrows))
print ("All arrows in the container is over and while loop completed")

 

Here is the output we get:

 

>>> 
============ RESTART: C:/Users/swadhin.ray/Desktop/While_loop.py ============
Number of arrows left in the container 9
Number of arrows left in the container 8
Number of arrows left in the container 7
Number of arrows left in the container 6
Number of arrows left in the container 5
Number of arrows left in the container 4
Number of arrows left in the container 3
Number of arrows left in the container 2
Number of arrows left in the container 1
Number of arrows left in the container 0
All arrows in the container is over and while loop completed
>>> 

 

The Python program automatically determines the end of the “while” when the Python interpreter finds a statement that is not tabbed. We don’t have to explicitly define it.

 

The For Loop:

When any string or list is iterated in sequence, this requires a for loop.
Here is a simple example to list the letters of a word using a for loop in python.

 

for x in 'apple':
     print ('Lits of letters in apple :',x)
print ("Apple letter over!")

 

The result will be as shown below:

 

============= RESTART: C:/Users/swadhin.ray/Desktop/for_loop.py =============
Lits of letters in apple : a
Lits of letters in apple : p
Lits of letters in apple : p
Lits of letters in apple : l
Lits of letters in apple : e
Apple letter over!
>>> 

 

Now say for example we have a list which has a few names and we want to loop it.

names = ['Ray', 'Mike',  'David']
for j in names:        
   print ( j , ' is a name')

The above code will print the below result or output:

 

============= RESTART: C:/Users/swadhin.ray/Desktop/for_loop.py =============
Ray  is a name
Mike  is a name
David  is a name
>>> 

 

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 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 )

Data Mining for Predictive Social Network Analysis

Social networks, in one form or another, have existed since people first began to interact. Indeed, put two or more people together and you have the foundation of a social network. It is therefore no surprise that, in today’s Internet-everywhere world, online social networks have become entirely ubiquitous.

Within this world of online social networks, a particularly fascinating phenomenon of the past decade has been the explosive growth of Twitter, often described as “the SMS of the Internet”. Launched in 2006, Twitter rapidly gained global popularity and has become one of the ten most visited websites in the world. As of May 2015, Twitter boasts 302 million active users who are collectively producing 500 million Tweets per day. And these numbers are continually growing.

Given this enormous volume of social media data, analysts have come to recognize Twitter as a virtual treasure trove of information for data mining, social network analysis, and information for sensing public opinion trends and groundswells of support for (or opposition to) various political and social initiatives. Twitter Trend Topics in particular are becoming increasingly recognized as a valuable proxy for measuring public opinion.

social network analysis and data mining

This article describes the techniques I employed for a proof-of-concept that effectively analyzed Twitter Trend Topics to predict, as a sample test case, regional voting patterns in the 2014 Brazilian presidential election.

The Election

General presidential elections were held in Brazil on October 5, 2014. No candidate received more than 50% of the vote, so a second runoff election was held on October 26th.

In the first round, Dilma Rousseff (Partido dos Trabalhadores) won 41.6% of the vote, ahead of Aécio Neves(Partido da Social Democracia Brasileira) with 33.6%, and Marina Silva (Partido Socialista Brasileiro) with 21.3%. Rousseff and Neves contested the runoff on October 26th with Rousseff being re-elected by a narrow margin, 51.6% to Neves’ 48.4%. The analysis in this article relates specifically to the October 26th runoff election.

Partido dos Trabalhadores (PT) is one of the biggest political parties in Brazil. It is the political party for the current and former presidents, Dilma Roussef and Luis Inacio Lula da Silva. Partido da Social Democracia Brasileira (PSDB) is the political party of the prior president Fernando Henrique Cardoso.

Data Mining and Extracting Twitter Trend Topic Data

I began social media data mining by extracting Twitter Trend Topic data for the 14 Brazilian cities for which data is supplied via the Twitter API, namely: Brasília, Belém, Belo Horizonte, Curitiba, Porto Alegre, Recife, Rio de Janeiro, Salvador, São Paulo, Campinas, Fortaleza, Goiânia, Manaus, and São Luis.

I queried the Twitter REST API to get the top 10 Twitter Trend Topics for these 14 cities in a 20 minute interval (limited by some restrictions that Twitter has on its API). Limiting the query to these 14 cities is done by specifying their Yahoo! GeoPlanet WOEIDs (Where On Earth IDs).

For this proof-of-concept, I used Python and a Twitter library (cleverly called “twitter”) to get all the social network data for the day of the runoff election (Oct 26th), as well as the two days prior (Oct 24th and 25th). For each day, I performed about 70 different queries to help identify the instant trend topics.

Below is an example of the JSON object returned in response to each query (this example was based on a query for data on October 26th at 12:40:00 AM, and only shows the data for Belo Horizonte).

[{"created_at": "2014-10-26T02:32:59Z",
  "trends":
	[{"url": "http://twitter.com/search?q=%23GolpeNoJN",
	  "name": "#GolpeNoJN", "query": "%23GolpeNoJN", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%23SomosTodosDilma",
	  "name": "#SomosTodosDilma", "query": "%23SomosTodosDilma", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%23EAecio45Confirma",
	  "name": "#EAecio45Confirma", "query": "%23EAecio45Confirma", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Uilson",
	  "name": "Uilson", "query": "Uilson", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%22Lucas+Silva%22",
	  "name": "Lucas Silva", "query": "%22Lucas+Silva%22", "promoted_content": null}, 
	 {"url": "http://twitter.com/search?q=%22Marcelo+Oliveira%22",
	  "name": "Marcelo Oliveira", "query": "%22Marcelo+Oliveira%22", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Cruzeiro",
	  "name": "Cruzeiro", "query": "Cruzeiro", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Tupi",
	  "name": "Tupi", "query": "Tupi", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=%22Real+x+Bar%C3%A7a%22",
	  "name": "Real x Bar\u00e7a", "query": "%22Real+x+Bar%C3%A7a%22", "promoted_content": null},
	 {"url": "http://twitter.com/search?q=Wanessa",
	  "name": "Wanessa", "query": "Wanessa", "promoted_content": null}
	],
  "as_of": "2014-10-26T02:40:03Z",
  "locations": [{"name": "Belo Horizonte", "woeid": 455821}]
}]

Brief Intro to Social Network Analysis

Social Network Theory is the study of how people, organizations, or groups interact with others inside their network. There are three primary types of social networks:

  • Egocentric networks are connected with a single node or individual (e.g., you and all your friends and relatives).
  • Socio-centric networks are closed networks by default. Two commonly-used examples of this type of network are children in a classroom or workers inside an organization.
  • Open system networks are networks where the boundary lines are not clearly defined, which makes this type of network typically the most difficult to study. The type of socio-political network we are analyzing in this article is an example of an open system network.

Social networks are considered complex networks, since they display non-trivial topological features, with patterns of connection between their elements that are neither purely regular nor purely random.

Social network analysis examines the structure of relationships between social entities. These entities are often people, but may also be social groups, political organizations, financial networks, residents of a community, citizens of a country, and so on. The empirical study of networks has played a central role in social science, and many of the mathematical and statistical tools used for studying networks were first developed in sociology.

Establishing the Network

To create a network using the Twitter Trend Topics, I defined the following rules:

  • Each city is a vertex (i.e., node) in the network.
  • If there is at least one common trend topic between two cities, there is an edge (i.e., link) between those cities.
  • Each edge is weighted according to the number of trend topics in common between those two cities (i.e., the more trend topics two cities have in common, the heavier the weight that is attributed to the link between them).

For example, on October 26th, the cities of Fortaleza and Campinas had 11 trend topics in common, so the network for that day includes an edge between Fortaleza and Campinas with a weight of 11:

In addition, to aid the process of weighting the relationships between the cities, I also considered topics that were not related to the election itself (the premise being that cities that share other common priorities and interests may be more inclined to share ……continue reading…..

 

 

 

 

Thank you for reading my blog , the reference is taken from one of the article that was published by Elder Santos on Toptal. 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 )