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