Prabhat Sahu

Create tables and generate data dynamically using DYNAMIC SQL

This article will be helpful for multiple database users like Oracle / PostgreSQL / EPAS (EDB Postgres Advanced Server) users to CREATE random tables, INSERT random data into those tables, and DROP those tables at one go, using few stored procedures and Dynamic SQL within. Oracle Database: — Create N number of tables with X …

Create tables and generate data dynamically using DYNAMIC SQL Read More »

Collections in oracle pl sql with examples

With this article, we will know how to create and use PL/SQL collection and record variables. All these composite variables have elements that we can treat as individual variables. We can pass composite variables to subprograms as a parameter.To create a collection or record variable, first we need to define a collection or record type, …

Collections in oracle pl sql with examples Read More »

Nested SubProcedure

Nested SubProcedure Purpose of Nested SubProcedure/Function: A Nested function/procedure definitions are a form of information hiding and are useful for dividing procedural tasks into subtasks which are only meaningful locally.  This avoids confusion with other parts of the program with functions and variables that are unrelated to those specific parts. After go through the below …

Nested SubProcedure Read More »

SQL Cheat sheet

SQL Cheat sheet: After following this SQL Cheat sheet we will be in a position to understand quickly and use SQL statement as per requirements.  Below are the SQL commands Cheat sheet mentioned which perform different kinds of operations. Various SQL types: DRL, DDL, DML, TCL, DCL  DESCRIPTION select syntax: DRL: DATA RETRIEVAL LANGUAGE COMMANDS …

SQL Cheat sheet Read More »

oracle performance tuning 12c

oracle performance tuning 12c SQL Tuning The main objective of “Oracle performance tuning”, “SQL tuning” is to avoid performing unnecessary work to access rows that do not affect the result. Scenarios: If a table has huge data, and some SQL queries are taking so much time to execute, what could be the reason? How to tune that query? 1. …

oracle performance tuning 12c Read More »


SQL*Loader: SQL Loader is an Oracle database utility, used for data loading from flat-files into the database table. Files used in SQL* Loder are Data_file, Control file, Bad_file, Doscard_file, Log_file.    Data Source: It can be a flat file(data_file) / stdin(standard input) -> file redirect in command prompt while executing sqlldr.    Control file: It gives …

SQL*Loader Read More »

Oracle Data Redaction

Oracle Data Redaction Oracle Database 12c introduced a new feature related to Advanced Security option which enables the protection of data shown to the user in real-time, without requiring changes to the application. The feature is called Data-redaction. During Data redaction the stored data remain unchanged, while the data to be displayed is transformed on-the-fly before …

Oracle Data Redaction Read More »