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.
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…
Recently I got the task to build a kind of mail client system in Oracle Apex. The requirement was to show the messages of mail Inbox on a page for a particular account so that the user can view the…
In Oracle, you can convert a 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…
Send a mail with Attachment using Oracle UTL_SMTP and Oracle Wallet Authentication example. The following is the example of Oracle Wallet configuration information: Wallet Path: 'file:/usr1/oracle/wallets/mywallet' Wallet Password: YourPasword Send Mail with Attachment using Oracle UTL_SMTP and Oracle Wallet PL/SQL…
In this tutorial, I am giving an example to merge/combine multiple PDF files into one PDF file in Oracle using PLPDF_TOOLKIT PL/SQL package. Assume you have a table for employees having a BLOB field and for each employee this table…
In this tutorial, I am giving an Oracle PL/SQL program example to demonstrate Dynamic SQL using EXECUTE IMMEDIATE statement. Oracle PL/SQL EXECUTE IMMEDIATE Dynamic SQL Example The following PL/SQL procedure will update the record in the Employees table for an…
In this tutorial, I am giving an Oracle dynamic SQL example to insert a record using the DBMS_SQL package. Through dynamic SQL, you can parse any DML and DDL statement using PL/SQL to execute to perform a particular task in…
Below I am giving an example of Oracle stored function to convert currency amount in words using PL For example, the amount of $123.45 would translate as One Hundred Twenty-Three Dollar and Forty-Five Cents. Also, you can change the currency…
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…
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)…
In my previous posts, I have given examples for Bulk Collect operations in Oracle. Here is an example of Bulk Collect with Save Exceptions to handle errors during bulk collect processing. PL/SQL Bulk Collect With Save Exceptions Example In the…
Here I am giving an example to print 1 to 10 without using the loop in PL You can print up to any number by changing the value 10 to any number, for example, 100. PL/SQL Program to Print 1…
Here I am giving an example of PL/SQL stored function to validate the Credit Card number and identify its type, whether it is a Visa, MasterCard or American Express Credit Card. I am checking the Credit Card number using the…
In Oracle, TRUNCATE command is a DDL statement, so you can not execute it directly in Oracle Procedure. To execute DDL commands in Oracle procedure, use EXECUTE IMMEDIATE statement. The below is an example of truncating a table in Oracle procedure…
In this blog post, I am sharing a function with which you can check if a file is an image file or not using PL The program will check the file by reading its header not the extension name of…
Here I am giving an example to save BLOB data as a file in PL BLOB data you can get it from the table having BLOB column, or you can get it from a file on the disk. PL/SQL Function…
Below I am giving a function example to get the BLOB from a file in PL PL/SQL Function to Get BLOB from a File The following function will read a file and will return the BLOB data type of the…
You have a PL/SQL program, and you want to log information in a text file of each step of your code. Here is an example to create a log file in Oracle using PL Create Log File in Oracle Using…