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 collections in PL/SQL code. The…

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 and C is character data….

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. In the parent table there…

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 as a whole or access…

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 (  Name varchar2(30), Address varchar2(30),…

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 limitations there is a concept…

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 module –        easy to understand…

UTL_FILE Package

UTL_FILE package is used to write data to operating system files such as text files, word files, excel files, etc. The data from the oracle table is transferred to the o.s. files. So we can generate a report of Oracle data in a non-oracle format.Members of UTL_FILE Package are as…

DBMS_SQL Package

DBMS_SQL Package What Is Dynamic SQL? Dynamic SQL enables you to write programs those reference SQL statements whose full text is not known until runtime. Before discussing dynamic SQL in detail, a clear definition of static SQL may provide a good starting point for understanding dynamic SQL. Static SQL statements…

DBMS_METADATA Package

Oracle supplied packages -DBMS_METADATA Package Example of DBMS_METADATA Package use DBMS_METADATA.GET_GRANTED_XML ( object_type          IN VARCHAR2, grantee              IN VARCHAR2 DEFAULT NULL, version              IN VARCHAR2 DEFAULT ‘COMPATIBLE’, model                IN VARCHAR2 DEFAULT ‘ORACLE’, transform            IN VARCHAR2 DEFAULT NULL, object_count         IN NUMBER   DEFAULT 10000) RETURN CLOB; DBMS_METADATA.GET_GRANTED_DDL ( object_type          IN VARCHAR2, grantee              IN VARCHAR2…