The following is an Oracle BEFORE INSERT OR UPDATE Trigger example to perform validations while inserting or updating the records in EMP table. Oracle BEFORE INSERT OR UPDATE Trigger Example The below trigger will do the following two checks on…

In Oracle, global temporary tables known as session tables and we use global temporary tables to keep the data temporarily in it while processing. The below is an example. Create a Global Temporary Table Here we will create a global…

In this article, you will learn how to use WHEN clause in Oracle trigger to make the trigger fire on the specified condition. Below is an example. You can test this trigger example by creating the following table and trigger…

The following is an example of Oracle stored procedure, in which table type collection is used to process the data. Process Data Using Table Type in Oracle Stored Procedure Example Create the following tables and insert the data to test…

In this tutorial, I am giving an example to compare two database table objects of different schemas using the DBMS_COMPARISON utility package in Oracle. Steps to Compare Two Table Objects in Oracle Using DBMS_COMPARISON Step-1 Create the comparison using DBMS_COMPARISON. In…

The following are the examples to get the current date in PL Examples To Get The Current Date in PL/SQL 1. Using Sysdate Function SET SERVEROUTPUT ON; DECLARE d_current_date DATE; BEGIN d_current_date := SYSDATE; DBMS_OUTPUT.put_line ('The Date today is: '…

The reason for the PLS-00323 error in Oracle is a mismatch in procedure or function declaration in the package specification and the package body. To resolve this take the following actions. Resolve PLS-00323 Error in Oracle Check the package specification…

To create a function in PL/SQL, use CREATE OR REPLACE FUNCTION statement. Below are the syntax details and an example. Syntax CREATE [OR REPLACE] FUNCTION function_name [(parameters)]  Return data_type is /* declare variables here */ Begin /* write program logic…

In this article, I am giving an example of a function which I mostly use to split a string in PL Especially, when I am writing a program to import a delimited file. Here are that function and an example…

In this article, I am giving an example to export data into JSON file in Oracle 11g using PL Follow These Steps to Export Data into JSON File in Oracle 11g First, download the Alexandria PL/SQL utility package from GITHUB…

In this tutorial, I am giving an example to implement Java code in Oracle Database using the stored function. We can create Java programs in the Oracle database by using CREATE OR REPLACE AND COMPILE JAVA SOURCE statement. After that, we can…

In this tutorial, I am giving an example to return resultset from a stored procedure in Oracle using sys_refcursor. Follow these steps to return resultset from a stored procedure in Oracle. 1. Create a Stored Procedure in Oracle The following…

In this tutorial, I am giving an Oracle FOR LOOP SELECT statement example. You can use SELECT statement inside FOR LOOP in Oracle to loop through SELECT results using PL FOR LOOP IN SELECT Syntax FOR cursor_variable IN (select_statement) LOOP…

In Oracle PL/SQL, IF condition is used to perform a logical check on certain values. If the condition is TRUE or FALSE then executes the statements followed by that condition. In this blog post, I am giving an Oracle IF…

In Oracle PL/SQL, WHILE LOOP statement executes the code written between WHILE LOOP and END LOOP until the condition is true. Below I am giving some examples for Oracle WHILE LOOP statement. Syntax WHILE logical_condition LOOP -- some PL/SQL code END…

In Oracle PL/SQL, FOR LOOP with REVERSE clause is used to repeat loop iteration in reverse order. The following are the syntax and examples for REVERSE FOR LOOP. Syntax FOR n IN REVERSE start_number .. end_number LOOP -- statement to execute…

Below is an example of PL/SQL program to compile all invalid package specifications and all invalid package bodies for the current user (SCHEMA) in Oracle. Also, if any of the invalid packages are not successfully compiled then it will print…

In this tutorial, you will learn how to delete the records from a table in Oracle using PL In Oracle PL/SQL, we can delete the records using DELETE DML Statement and be using TRUNCATE DDL command. I am giving below…