Category: PL/SQL

fncbased_indx

Understanding Oracle INSTEAD OF TRIGGER

INSTEAD OF triggers are essentially alternatives to DML triggers. They fire when inserts, updates, and deletes are about to occur;…

fncbased_indx

Log Sub-Program Parameter Values Using LOG4PL/SQL Framework In Oracle Database 19c

LOG4PLSQL is an open source tool that uses PL/SQL framework to log into PL/SQL code package,function,web application,procedure, trigger and various…

index_compression

Understanding Difference Between Table Function & PIPELINED Table Function In Oracle Database 19c

TABLE FUNCTION A table function is a function that can be called from within the FROM clause of a query,as…

fncbased_indx

Handling Duplicates in Collections In Oracle Database 19c

In our day to day life, sometimes we come across situations where we have to deal with duplicate values within…

fncbased_indx

Understanding PRAGMA SERIALLY_REUSABLE In Oracle 19c

In Oracle, Package data consists of variables and constants that are defined at the package level—that is, not within a…

fncbased_indx

Understanding DBMS_XMLGEN Package In Oracle 19c

In Oracle, the DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format. The package takes…

fncbased_indx

Understanding AUTHID Clause In Oracle

AUTHID CURRENT_USER It is used when we want a routine to be executed with the privileges of the invoker and…

autonomous_transaction_isolation

Understanding AUTONOMOUS TRANSACTION with ISOLATION LEVEL

The default behavior of AUTONOMOUS TRANSACTIONS is that once a COMMIT/ROLLBACK occurs in the AUTONOMOUS TRANSACTION, those changes are visible…

split_partition

WITH CLAUSE/SUBQUERY FACTORING OPTIMIZATION

If the content of the WITH clause is sufficiently complex, Oracle may decide to resolve the subquery into a global…

join_methods

Understanding SET Operations In NESTED TABLE

Out of the three collection types, NESTED TABLE collection type supports SET operations in Oracle. x MULTISET EXCEPT [DISTINCT] y…