Author: tom

This note provides an example on a trigger. I like this example as it shows how additional business logic can be implemented. It is an example of a statement trigger: whenever a statement is issued, the trigger fires once, no matter how many records are affected. CREATE OR REPLACE TRIGGER security_time_check BEFORE DELETE OR UPDATE…

Oracle has a package that allows you to write to a file. Oracle uses the concept of a directory (here “External”) that acts as a logical description of a directory. One of the properties of such directory is the physical location. That file can be opened, written to and closed. Subsequent programme provides an example.…

This programme shows you we may retrieve the results from a query. It works with a cursor that contains another cursor. The example below is a cursor that retrieves data from employee table, along with data from a cursor that is linked to the employee table. Doing so, we may add data to the employee…

This programme contains a package along with a calling programma that uses this package. Let me first provide the package itself. The code: CREATE OR REPLACE PACKAGE employee_data AS TYPE employee_cv_type IS REF CURSOR RETURN employee%ROWTYPE; PROCEDURE open_employee_data ( employee_cv IN OUT employee_cv_type, x_dno IN employee.dno%TYPE); PROCEDURE fetch_employee_data ( employee_cv IN employee_cv_type, employee_output OUT VARCHAR2);…

The idea is as follows. We want to create a simple function that calculates a code from a string. This code is the total ASCII value of the characters in a string. I used this code to act as a foreign key to which other tables may refer to. I use an Oracle function that…

This programme uses PLSQL to select a set of 1000 records per item. Each item is calculated from a cursor that generates the items for which a set of records is calculated. So the programme can be written in pseudo code as: For i:=1 to NumberItem calculate set end Loop The programme looks like:

Let us see how we might provide a value to the primary key in Oracle. There are many ways to provide a value for the primary key. One is to create a trigger (see http://van-maanen.com/index.php/2011/02/02/create-a-surrogate-key-in-oracle/ ). Another is to declare a field as an identity field (id NUMBER GENERATED ALWAYS AS IDENTITY,) . A third…

The situation is simple. We have to create a table that contain data on a certain variable outcome. Normally, one would like to create a field in the table and add a bunch of records that relate to that outcome. However, the request is to create a new table for that. Examples might be a…

It is possible to make a hot backup in Oracle. This implies that we do not have to shutdown the database before creating a backup. The steps are as follows: First, we check if the system is in an archive mode: archive log list This command is given in sqlplus when logged in as sysdba.…