Oracle Supplied Packages

Oracle Supplied Packages

Oracle Supplied Package

- Are provided with the Oracle Server
- Extend the functionality of the database
- Enables access to certain SQL features normally restricted for PL/SQL.

Using Native Dynamic SQL
- Is a SQL statement that contains variables that can change during runtime
- Is a SQL staement with placeholders abd is stored as a character string.
- Enables general-purpose code to be written
- Enables data-defination, data_control or session-control statements to be written and executed from PL/SQL.
- Is written using either DBMS_SQL or native dynamic SQL.

Using the DBMS_SQL Package

The DBMS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package includes:

- OPEN_CURSOR
- PARSE
- BIND_VARIABLE
- EXECUTE
- FETCH_ROWS
- CLOSE_CURSOR

Componets of the DBMS_SQL Package

Function or Porcedure

Description

OPEN_CURSOR

Opnes a new cursor and assigns a cursor ID number

PARSE

Parses the DDL or DML ststements: that is, checks the statements’s syntax and associates it with the opned cursor (DDL statement are immediately executed when parsed)

BIND_VARIABLE

Binds the given value to the variable identified by its name in the parsed statement in the given cursor

EXECUTE

Executes the SQL statement and returns the number of rows processed

FETCH_ROWS

Retrives a row for the specified cursor (for multiple rows, call in a loop)

CLOSE_CURSOR

Closes the specified cursor



create or replace procedure delete_all_rows
(p_tab_name in varchar2, p_rows_del out number)
is
cursor_name integer;
begin
cursor_name := DBMS_SQL.open_cursor ;
DBMS_SQL.PARSE(cursor_name,'DELETE From '||P_tab_name,dbms_sql.native) ;
p_rows_del := DBMS_sql.EXECUTE(cursor_name) ;
DBMS_SQL.close_cursor(cursor_name) ;
end;

Dynamic SQL Using EXECUTE IMMEDIATE

Create procedure del_rows
(p_table_name in varchar2,
p_rows_deld out number)
IS
Begin
Execute immediate ‘delete from ‘||p_table_name ;
P_rows_deld := SQL%ROWCOUNT ;
End ;

SQL> variable a number
SQL> execute del_rows ('EMP1', :a)

SQL> print a

Using DBMS_JOB for Scheduling

DBMS_JOB enables the scheduling and execution of PL/SQL programs:

- Submitting jobs
- Executing jobs
- Changing execution parameters of jobs
- Remove jobs
- Suspending Jobs