Category Archives: Oracle

eleting a category does not delete the posts in that category. Instead, posts that were only assigned to the deleted category are set to the category Uncategorized.

PL/SQL Collection Data Types

A collection is an ordered group of elements, all of the same type. Each element is identified by a unique subscript that represents its position in the collection PL/SQL provides three collection types − Index-by tables or Associative array Nested table Variable-size array or Varray   VARRAY Varrays hold a fixed-sized sequential collection of elements .. All varrays… Read More »

What is PL/SQL Records Data Types

A PL/SQL record is a composite data structure that is a group of related data stored in fields.  Records are composed of a group of fields, similar to the columns in a row . The %ROWTYPE attribute lets you declare a PL/SQL recordthat represents a row in a database table, without listing all the columns. PLSQL handle three type of Records 1)User- Define… Read More »

Bulk Collect and Bulk Bind

Retrieve many row in single fetch and improve the speed of data retrieval by less context switch. Oracle server used two engine to run PL/SQL block and subprogram. 1) The PL/SQL   runtime engine , which  run  all procedural line of code but passes the SQL statement to the SQL engine. 2)The SQL engine parse and executes the SQL… Read More »

Using Ref Cursors

The example below uses a ref cursor to return a subset of the records in the EMP table. The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened. CREATE OR REPLACE PROCEDURE… Read More »

Oracle Deferred Constraints

During large transactions involving multiple dependancies it is often difficult to process data efficiently due to the restrictions imposed by the constraints. An example of this would be the update of a primary key (PK) which is referenced by foreign keys (FK). The primary key columns cannot be updated as this would orphan the dependant tables, and the… Read More »