Rutvik Prajapati

Recently, a new requirement came up to implement One Time Password (OTP) based authentication in Oracle APEX. In this, the user will enter his mobile number on the login page and will receive an OTP via SMS. The user will then enter the OTP on the login page and be able to authenticate himself in the APEX app.  When I started working on this requirement, first I identified all the steps that I need to implement for the solution.  Below are the steps that I followed: 1. Generate the OTP and temporarily store it in a database table. 2. Send the generated OTP to the user by integrating REST API to send the SMS 3. Validate the user entered OTP against the OTP that is stored in the table and authenticate the user. 4. Create a Custom Authentication Scheme. 5. Modify the login page. Let's understand each step in more detail. Step 1:  Generate the OTP and temporarily store it in a database table.  First, create a table to temporarily store the OTP for the validation. Here...

This post is part of a series of blog posts on basic CRUD operations in Oracle Database using python. CRUD operations in Oracle Database using Python How to Create Records into Oracle Database Table using Python? How to Read Records from Oracle Database Table using Python? How to Update and Delete Records from Oracle Database Table using Python? This is the fourth and final post of this series. In this post, let's understand the 'U' and 'D' of the CRUD operations, which is how to Update and Delete records from a database table. Update Records We already know how to insert and fetch the records from the table and now updating the records is easy for us. Let's I want to update the salary and commission of an employee Martin into our EMP_TEST table. #Update statement sql_stmt = """ UPDATE EMP_TEST SET SAL =:1, COMM = :2 WHERE EMPNO = :3 ...

This post is part of a series of blog posts on basic CRUD operations in Oracle Database using python. CRUD operations in Oracle Database using Python How to Create Records into Oracle Database Table using Python? How to Read Records from Oracle Database Table using Python? How to Update and Delete Records from Oracle Database Table using Python? This is the third post of this series. In the second post, we have already covered how to create records in the Oracle database table. In this post, let's understand the 'R' of the CRUD operations, which is how to read records from a database table. To read the records from the database, we use the Select query. After executing the Select statement, we have to fetch the records from the database. For this operation, we have three methods:  cursor.fetchone()         : Used to fetch only one-row cursor.fetchall()           : Used fetch all rows cursor.fetchmany(n)    : Used to fet...

This post is part of a series of blog posts on basic CRUD operations in Oracle Database using python. CRUD operations in Oracle Database using Python How to Create Records into Oracle Database Table using Python? How to Read Records from Oracle Database Table using Python? How to Update and Delete Records from Oracle Database Table using Python? This is the second post of this series. In the first post, I have explained the steps that we need to follow to connect with the Oracle database using cx_Oracle and also explained the basic methods that we will use to perform database operations. Now we know the basic steps that we need to follow, let's understand the 'C' of the CRUD operations, which is how to create records into a table. First, we need to create the connection as mentioned earlier.  import cx_Oracle as cx import db_config as db try: #create connection conn = cx.connect(db.username,db.password,db.connect_string) except Exception as err: print('...

In my previous post , I explained how to connect to Oracle Database using Python. In this blog series, I will explain how to perform CRUD Operations using Python. CRUD operations in Oracle Database using Python How to Create Records into Oracle Database Table using Python? How to Read Records from Oracle Database Table using Python? How to Update and Delete Records from Oracle Database Table using Python? This is the first blog of the series, where I explain the basics of the cx_Oracle module.  I'll use the emp_test table for all the examples in this series. Create the table using the below script. CREATE TABLE EMP_TEST(EMPNO NUMBER(4,0) PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2,0)); To perform any acti...

In this post, we will take a look at the most frequently asked and interviewers' favorite question "How to delete duplicate rows from a table?"  Creating Database Table The following scripts can be used to create a sample table that holds the duplicate records. CREATE TABLE EMP_DUP (EMPNO NUMBER(4,0) NOT NULL ENABLE, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2,0), CREATED_AT DATE ); / INSERT INTO EMP_DUP VALUES (7839,'KING','PRESIDENT',null,to_date('17-11-81','DD-MM-RR'),5000,null,10,to_date('25-06-21','DD-MM-RR')); INSERT INTO EMP_DUP VALUES (7698,'BLAKE','MANAGER',7839,to_date('01-05-81','DD-MM-RR'),2850,null,30,to_date('25-06-21','DD-MM-RR')); INSERT INTO EMP_DUP VALUES (7782,'CLARK','MANAGER',7839,to_date('09-06-81','DD-MM-RR'),2450,null,10,to...

In APEX, the sorting option for a classic report is divided into two aspects:  How records will be sorted when the report first renders? Whether the end-user can change the sort order interactively by clicking the column header. A classic report can be developed using either the static query or the dynamic query and the sorting behavior is different for both options. First, we will see the sorting behavior for the report based on the static query.  The below classic report is based on the static query and displays all the records of the EMP_DUP table. For this blog, I created EMP_DUP which is the same as the EMP table but contains duplicate records. The sorting aspects for this report are determined by the properties in each column's  sorting  section. The below image shows the  sorting  section for  JOB. The Default Sequence  property determines the initial sort order. Each column that participates in the initial sort order will have a unique s...