Category: Allgemein

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.…

Actually, taking a cold backup in Oracle is quite simple. If you choose to undertake a cold backup by copying tables, it consists of 5 steps. Step 1 is identifying the location of the data files. We have 3 commands for that: select name from v$datafile;select member from v$logfile;select name from v$controlfile; We must remember…

Let us suppose, we have a table that has a granularity in two dimensions. Think of a customer and a product dimension. So each record has a combination of customer identification and product identification that is unique. Let us also assume that we would decrease that level of granularity into one dimension. Think of customer…

Oracle has nice features to export data as an XML file. Let us discuss a few of them. The first one is to use SQL Developer. We could already export a table in SQL developer as an XML file. The second one is to use the DBMS_XMLGEN package. This allows to use queries in an…

External tables allow someone to store data in a file while these files are shown as tables within Oracle. So, we have a two sided object – on one hand it can be shown as a file that is human readable, on the other hand it can be seen as a table. Let us see…