Procedure

Procedure

Procedure
Procedures can be used for Insert, Update and Delete operations. As their structures aren’t suitable, they aren’t used that often for Select operations.

It is possible to specify the action to apply such as a drop or purge, on a user basis, when they want to delete a table by saving it into a procedure and enforcing it.

This is decided upon by the database administrator.

Can alternatively be run as:

This is a bug in Oracle.

A workaround is to replace the lowercase i’s

The following calculates the statistics of the tables and the indexes of the users who run it.

The code can also be run with: exec calculate_table_statistics

Alternatively, the procedure can be scheduled in the toad application’s jobs tab. This is shown how in a command line environment with the following code:

Note: If a table hs been created using “create table xxxxx as select * from yyy”, then its constraints cant be migrated.

The indexes get the table record count from user_tables.

Insert Stress

This procedure is to compare the row counts between the tables in a schema.

Note: ‘is’ and ‘as’ are both supported. Writing ‘end procedure’ is also optional.

in & out

The syntax below can also be run with SQLPlus

The advantage of using a Procedure over a Function: A function can only retrieve a single value, e.g. only name, only surname, single token name_surname but not two separate values.

A function can’t, for example, retrieve a name and assign it to a variable. However this is possible with a procedure.

Functions can either be a varchar2 or a number type. Procedures are able to simultaneously output multiple variables and types.

Both functions and procedures are considered to be program units. Only execute privileges are granted to these program units.

Errors that could be caused by creating functions and procedures.

Compile/Recompile

Note: With 10G, the dbms attempts to recompile called program routines that have become invalid.

This code can be viewed in toad. If toad hasnt been installed, it can be viewed using user_source view/table.