PL/SQL to Select row count per table in schema

Posted Posted in Oracle, PL/SQL

Function to calculate count using dynamic SQL, for an input table name

USER_TABLES has all the table statistics for the same schema. How to invoke above functions:

© 2015, All rights reserved.

Introduction to PL/SQL

Posted Posted in PL/SQL

What is PL/SQL? PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL. The PL/SQL Engine: Oracle uses a PL/SQL engine to processes the PL/SQL statements. A […]

Bulk Processing with BULK COLLECT and FORALL

Posted Posted in PL/SQL

When we write any program in Oracle, it includes both PL/SQL statements and SQL statements. PL/SQL statements are run by the PL/SQL statement executor and SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine sees a SQL statement, it stops and passes the SQL statement over to the SQL engine. […]

Oracle Ref Cursor

Posted Posted in PL/SQL

Ref Cursor is a data type of Cursor variable.  It is not limited to one query. You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query. You can assign a value to it. You can use it in an expression. It can be […]

PL/SQL Dynamic SQL

Posted Posted in PL/SQL

Dynamic SQL is a programming methodology for generating and running SQL at runtime. This is mostly useful to perform DDL operations at runtime. There are 2 ways to write Dynamic SQL. Native Dynamic SQL PL/SQL language for building,running Dynamic SQL DBMS_SQL package A package for building,running Dynamic SQL When to write Dynamic SQL? SQL that […]

Pragma Autonomous Transaction in Oracle

Posted Posted in PL/SQL

PRAGMA is a compiler directive. AUTONOMOUS_TRANSACTION is a transaction where PL/SQL suspends the current transaction temporarily and performs a fully independent transaction. A subprogram marked with PRAGMA can do SQL operations and commit/rollback these operations without rolling back the main transaction. There are scenarios when you might want to have an transaction to commit or […]

Oracle Packages

Posted Posted in PL/SQL

Packages are schema objects that groups Pl/sql types,variables and subprograms logically. Package has 2 parts. Package specification Package body Oracle package specification can be created without body but Package body can’t be created without specification. Package Specification: It contains all the information about content of the package. It DECLARES the types, variables, constants, exceptions, cursors, […]