DBMS_JOB Package

Oracle supplied packages -DBMS_JOB Package Enables the scheduling and execution of PL/SQL programs. Submit is the procedure of DBMS_JOB package. The parameters of Submit procedure are — JOB – Unique identifier of the job (Out parameter) WHAT – PL/SQL code to execute as a job NEXT_DATE – Next execution date…

DBMS_DDL Package

Oracle supplied packages -DBMS_DDL Package Below is self explanatory example Scenario of recompilation for a procedure – drop table tx1; create table tx1(a number); insert into tx1 values(1); create or replace procedure px1 is s number; begin select a into s from tx1; end; select status from user_objects where object_name=’PX1′;…

DBMS_SESSION Package

DBMS_SESSION Package The global variable’s value can be re initialized. create or replace package p as g number := 0; end; / create or replace procedure pr1 is begin p.g := p.g 1; dbms_output.put_line(‘Value of g is ‘ || p.g); end; / exec pr1; exec pr1; exec pr1; After executing…

DBMS_PIPE Package

DBMS_PIPE Package DBMS_PIPE is a package supplied to allow two sessions to communicate with each other. It is an inter-process communication device. One session can write a message on a pipe, and another session can read this message. Members of DBMS_PIPE: Sender session Pack_message procedure Send_message procedure       Receiver Session…

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 Oracle supplied packages -DBMS_SQL Oracle…

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 with RETURN clause Body begins…

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 if definition changes Provide Modularity…

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 handlers are written There are…

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. 3) They can have only one…

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 cases, control its processing Explicit…