Category PLSQL

PL/SQL (Procedural Language for SQL) is Oracle’s proprietary extension to standard SQL. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL allows developers to write complex database-centric applications by incorporating variables, conditions, loops, error handling, and modular programming constructs like procedures and functions. This language is tightly integrated with Oracle databases, enabling efficient data processing and transaction management.

DUO Two Factor Authentication Using PL/SQL

DUO Two Factor Authentication Using PL/SQL

This tutorial will teach you how to integrate DUO Two Factor Authentication using PL/SQL in the Oracle database. What is Two Factor Authentication? Two-factor authentication (2FA) is a method by which users can verify their identity by using two different…

Running an Oracle Stored procedure in background.

Running an Oracle Procedure in Background

In this Oracle tutorial, you will learn how to run a stored procedure in the background using DBMS_SCHEDULER. Running a procedure in the background means that the stored procedure will run asynchronously. You can run multiple procedures in the background,…

Oracle Pipelined Function Example

Oracle Pipelined Function Example

This Oracle tutorial shows how to create Pipelined function. Follow these steps: 1. Create a Type Object For Oracle pipelined function, we must have a type object through which we can send the rows. Create one as below: CREATE OR…

Get BLOB from the BFILE Column in Oracle

Get BLOB from the BFILE Column in Oracle

In Oracle, the BFILE column is a locator or reference for the external file. It has the directory and filename information. Here I am giving an example of PL/SQL code to get BLOB from the BFILE locator column in Oracle.…

APEX_ZIP Example

APEX_ZIP Example

In Oracle 12c onwards, on which the Oracle Apex is by default installed, you can use APEX_ZIP PL/SQL package to zip the files. So here I am giving a few Oracle APEX_ZIP examples: Oracle APEX_ZIP Example The following PL/SQL code…

Oracle PL/SQL: Check If BLOB or CLOB is Empty

Oracle PL/SQL: Check If BLOB or CLOB is Empty

In Oracle PL/SQL, to check if BLOB or CLOB is empty or not, use the dbms_lob.getlength() function or dbms_lob.compare() function. Here are the examples: 1. Using dbms_lob.getlength() Function declare vblob blob; Cursor c_blob is select content into vblob from employee_docs…

How to Convert Number to String in Oracle?

How to Convert Number to String in Oracle?

In Oracle, you can convert number to string using the TO_CHAR() function, however, you can simply assign a numeric value to a string variable without using any function in Oracle. Below are the examples. 1. Convert Number to String using…

Oracle Autonomous Transaction Example

Oracle Autonomous Transaction Example

In Oracle, an autonomous transaction can commit or rollback the data in the same session without committing or rolling back in the main transaction. PRAGMA (compiler directive) statement is used to define autonomous transaction in Oracle. The following is an…

How to Encrypt Password in Oracle?

How to Encrypt Password in Oracle?

Here I am giving an example to encrypt the password in Oracle using the DBMS_CRYPTO package. To demonstrate this, I have created the following table to store user ID (USER_ID) varchar2 data type and to store the encrypted password (ENC_PSW)…