Oracle supplied packages

There are multiple packages supplied in oracle. Please use links below to read more about each package.   Oracle Supplied Packages – DBMS_PIPE Package Oracle supplied packages – DBMS_SESSION Package Oracle supplied packages -DBMS_DDL Package Oracle supplied packages -DBMS_JOB Package Oracle supplied packages -DBMS_METADATA Package…

PL/SQL Functions

Function in Oracle Subprogram that returns a value Have a RETURN clause Stored in database and can be invoked or called by any anonymous block Generally take parameters Datatype specifier in parameter declaration must be unconstrained Has two parts Specifiction begins with keyword FUNCTION, ends…

PL/SQL Procedures

Procedures Advantages of Subprograms Provide Extensibility –        PL/SQL language can be tailored to suit the needs of the application Promote reusability and maintainability –        Once validated, they can be used with confidence in any number of applications –        Simplifies maintenance/enhancement, as subprogram is only affected…

PL/SQL Exceptions

EXCEPTIONS Introduction to Exceptions An error condition is called an Exception When an error occurs, an exception is raised i.e. normal execution stops and control transfers to the exception handling part of the PL/SQL block or subprogram To handle raised exceptions, separate routines called exception…

PL/SQL Tables

PL/SQL Tables Features of PL/SQL tables are as follows – 1) It is a composite data type. 2) They are modeled as similar to database tables, but they are not permanent tables. So they can be created and manipulated only in a PL SQL block….

Cursors in PL/SQL

Cursors To process a SQL statement, PL/SQL opens a work area called a context area. PL/SQL uses this area to execute SQL statements and store processing information A PL/SQL construct called ‘Cursor’ allows you to name a context area, access its information and in some…

PL/SQL Records

Objects of type RECORD are called PL/SQL records PL/SQL records have uniquely named fields, which can belong to different datatypes Define a RECORD type TYPE IS RECORD (fieldname1 : fieldnameN ; (%TYPE and %ROWTYPE can be used to specify ] Example 1 of Record Type…

LOOPS

The ability to repeat or skip sections of a block can be achieved with the usage of LOOP or GOTO statements There are three forms of the LOOP statement – LOOP – WHILE-LOOP – FOR-LOOP LOOP Statement LOOP repeats a sequence of statements Statements to…

Nested Blocks

declare x number; begin x := 80; dbms_output.put_line(‘abc’); declare y number; begin y := 90; dbms_output.put_line(‘Inner Block variable value ‘ || y); end; dbms_output.put_line(‘Outer Block variable value ‘ || x); end; Scope of variables A variable declared in the outer block is accessible in the…

Conditional Statements – IF

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on the condition There are three forms of statements IF-THEN IF-THEN-ELSE IF-THEN-ELSIF Sequence of statements is executed only if the condition evaluates to TRUE If condition evaluates to FALSE…