Rutvik Prajapati

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

In my last post , I explained how to create a report using dynamic SQL. In that post, I only focused on the functionality of how to create the dynamic report and totally overlooked the security risk that comes with it in APEX. Thanks to Alex for highlighting that security risk.  A dynamic report gives the user the flexibility to create the report at the run time based on the input provided by the user. However, it also invites a potential risk of SQL injection if proper care is not taken during the development. As a developer, we must develop applications by keeping security in mind. In this blog, I will explain SQL Injection, how incorrectly created dynamic reports can invite a SQL injection risk, and then explain the steps to prevent the SQL Injection attack.  What is SQL Injection? SQL Injection is a code injection technique in which a malicious user(attacker) inserts the SQL code into a text-based entry field to manipulate the syntax of the query that returns the uninten...