Triggers

Triggers It is a stored PL/SQL program unit associated with a specific database table Oracle executes (fires) the trigger automatically whenever a given SQL operation affects the table They are invoked implicitly They are useful for customizing a database They should be used only when…

PL/SQL Wrapper

PL/SQL Wrapper To hide the source code from the user. Ed c:\details.sql  (Creating a .sql file) create or replace procedure details as begin dbms_output.put_line(‘The password of the account is XYDFZ’); end; SQL> @ c:\details.sql  select text from user_source where name = ‘DETAILS’; Type the wrap…

Know Depandencies

Dependencies Displaying direct and Indirect dependencies Step 1 – Run the script utldtree.sql that creates the objects that enable you to display the direct and indirect dependencies. (This script is in ORACLE_HOME/rdbms/admin folder) Scenario – Table is emp EMP table has two direct dependent objects…

Bulk Binding

Bulk Binding This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of…

Bfile and LOBs

Bfile – Binary file (text, sound, image or video) is stored outside the database. The locator to that file is stored in the database. Bfile data is read only for oracle. LOB – BLOB and CLOB are the tow types of LOBs. B is binary…

Nested Tables

Nested Tables Table within a table A table is represented as a column within another table There is no limit to the number of rows in the nested table for each row in the main table. Basically used for mapping master-detail relationships between tables. i.e….

Varrays

VARRAY     Also known as varying arrays It is an aggregation of values stored in a single column A single column in the database would point to a group of elements Can associate a single identifier with an entire collection Can reference the entire collection…

Types in Oracle PL/SQL

Types in Oracle TYPES    Users can create their own data types by combining previously defined data types Also called Object types Object types made up of attributes and members Creating type Info which holds  the attributes – Create or replace type Info as OBJECT…

PL/SQL Ref Cursors

PL/SQL  –  REF CURSORS Limitations of a normal cursors are — 1) A PL/SQL program cannot pass a cursor as a parameter to another program. 2)  A PL/SQL program can only open the cursor and process the information within the program itself. To overcome these…

Packages

Database objects that group logically related PL/SQL types, objects and subprograms They cannot be called, passed parameters to or nested There are two parts –        Specification –        Body Advantages of Packages Modularity –        allows encapsulation of related types, objects and subprograms in a named PL/SQL…

1 2 3