Category: PL/SQL

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…

duplicate_in_collection

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…

duplicate_in_collection

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…

duplicate_in_collection

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…

duplicate_in_collection

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…

Accessible By

Understanding Oracle 12c ACCESSIBLE BY CLAUSE

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to…

duplicate_in_collection

Understanding DBMS_OUTPUT Package

Oracle provides the DBMS_OUTPUT package to enable you to send information from your programs to a buffer. This buffer can…