How does Instr work in SQL

simple SQL introduction for GTDS users

[Beginning] [next section]

[Part One]

Naming conventions
Entering SQL statements under Oracle in general
Syntax and structure of SQL statements
Create statements
SELECT statement
INSERT statement
UPDATE statement
DELETE statement
Transaction processing
Access rights for multiple users of an SQL database
A few sentences on data exchange under Oracle


Expression means
CAPITAL LETTER Keyword, must be written exactly as it is, only UPPER or lower case doesn't matter. Example:
<name> Is a placeholder for a proper name (table, column, ...) or an expression (e.g. the WHERE condition). In the real statement, it is replaced, for example, by the table name (see examples) or the WHERE condition. The angle characters <> must of course not be entered.
[BOLD PRINT] [] denotes optional parts of a statement. You can use these parts, but you can also leave them out. These can be keywords, but also placeholders for names, e.g. of tables or columns. Of course, the brackets [] must not be included.
ONE | OTHER The vertical line | separates alternatives. In this example, ONE or OTHER would have to be specified. (But not the bottom line | !)
<spaltenname,...> A comma followed by dots ... indicates that the relevant part can be repeated several times (but does not have to be). A typical example would be the table and column lists in the SELECT statement.

Naming conventions

In the following description there are often names that have to be used instead of placeholders, e.g. table names, column names, index names, etc. The following rules apply to these names in the SQL environment (not exactly the same everywhere):

  • Names can consist of uppercase letters, lowercase letters and the underscore _.
  • No distinction is made between uppercase and lowercase letters. In addition, digits may be used, but not as the first character of a name.
  • Other characters may not be used, e.g.:,; - * +. Umlauts (ÄÖÜäöü) and ß are also not possible (!!).
    Oracle allows the use of as well as in names, but this is better avoided.
  • Name length: The possible length of the names in databases strongly depends on the system used. Oracle (®) generally allows names of 30 characters in length.
  • important : Names cannot be so-called reserved words. That means: Tables, columns, etc. may not have names that are also used as keywords or command words in SQL. A table cannot be called CREATE or SELECT, etc. Also, e.g. ALTER may not be used as a column name (keywords are case-sensitive - so the German plausible word age corresponds to the keyword ALTER from ALTER TABLE).

Entering SQL statements under Oracle in general

You can enter SQL statements under Oracle using the tools or or. Your product set usually includes the more powerful one ([Operation]).

There are more user-friendly ways to the data, both from Oracle (e.g.) and from other, partly more or less free sources. However, SQLPLUS is widespread and available almost everywhere there is Oracle.

In a Unix (®) environment, as it still exists in many GTDS registers, some special features must be taken into account. A prerequisite for calling or are some correct environment variables to be set (errors at this point are not always immediately obvious: when calling SQL * Plus, messages such as: not found. Or Error during Connect etc. may appear). If in doubt, you should first call (Bourne or Korn-Shell) or (C-Shell). For more information, see system administrator notes. The database that you access is determined implicitly when both tools are called by the environment variable ORACLE_SID (must be set) and possibly TWO_TASK. Since there is usually only one database running on your computers, there can be no problems with the call. In networked environments with several accessible Oracle databases, there are extended call options to determine the target database.

Syntax and structure of SQL statements

Format of SQL statements

SQL is a format-free language, i.e. how the statements look is basically the same. Like sentences in a normal language, SQL statements consist of individual words separated by a space (so-called white space, i.e. spaces, tabs and line separators).


SELECT * FROM STEM; or select * from stem; or Select * FROM stem; always leads to the same result!

semicolon ; at the end :

SQL statements under Oracle are terminated individually with a semicolon or a slash on a line.


select * from PATIENT; or select * from PATIENT /

Comments :

Comments can be found in almost any place in SQL; under Oracle they must be enclosed in / * * / (as in C).


select * from STAMM / * gets all data records of the table stem * /;

Ansi-style comments are also possible. They extend from a - (two hyphens) to the end of the line. Important exception : In the queries in the report definition for SQL * ReportWriter are allowed absolutely none Comments are available, otherwise puzzling errors may occur!

Note: The examples below refer to the database of the GTDS tumor documentation system, as well as two example tables.

Create statements

Create database

Under Oracle, the process of generating a database is quite extensive, unless you use ready-made scripts and procedures. The following literature provides information on this:

- Oracle Database Administrator's Guide - Oracle Installation and User's Guide (IUG) for your system - System administrator instructions for the GTDS

In the meantime, graphic tools for this task are probably also available on PC platforms.

In Oracle means Database almost always an extensive collection of many tables that share a common name for the database (Database Instance Name or System Identifier, ORACLE_SID) and are stored in a few files in the operating system.

The tables within a database can belong to different logical users. A safe separation is ensured.

This terminology differs significantly from that of typical PC database systems, where there are often numerous small "data banks" with one or two tables.

Create new table:

That is responsible. The basic syntax for creating a table looks like this:

CREATE TABLE ( [NOT NULL], ....., );
For the following is to be used:
CHAR (n) for character strings (e.g. surname, first name, place of residence, ...). Under Oracle Version 6, CHAR is a synonym for VARCHAR, i.e. it is saved to save space. Under Oracle 7, however, a character string of the data type CHAR is always extended to the fixed length n with spaces and is also saved that way!
VARCHAR (n) like CHAR (), but is saved to save space. Oracle works optimally here: only the space actually used by the data is required. Therefore, if necessary, fields can be safely defined as VARCHAR (254) (maximum length). Under Oracle 7, VARCHAR is also called VARCHAR2, and the maximum length has increased to 2000.
LONG Special case of VARCHAR: a maximum of 64KB (Oracle7: 2GB) text, however, numerous SQL operations are later not possible with this data type.
DATE this data type should be used for dates (date of birth, follow-up appointment, ...). You could also save a date in CHAR (), but you can "calculate" it as DATE in SQL, e.g. determine all patients who have a follow-up appointment within a specified interval.
NUMBER Oracle data type for any number with or without decimal places with a maximum of approx. 38 valid digits. Here too, Oracle always saves with a variable length, thus saving space, so that NUMBER can always be used for a number.
NUMBER (m, n) Oracle data type for any number of the maximum total length m with a maximum of n decimal places. n and m must be less than approx. 38.
General SQL numeric data types
(can be used under Oracle, but are implemented internally in NUMBER)
INTEGER Large integer up to approximately 2,000,000,000
SMALLINT Smaller integer, often up to about 32,000
FLOAT Floating point number, possibly in exponential (scientific) notation.

NOT NULL can optionally be specified for each column. Then it is not allowed to insert a row into this table in which this column has no value.

With the advancing spread of multimedia data, further special data types were introduced (e.g. - Binary Large OBject).


The GTDS patient table could result from:

In the course of constant logical extensions, this command has seen more and more additions (e.g. for CONSTRAINTS, in Oracle8 for nested tables, etc.). In addition, a number of additions can be specified with Oracle, where and in which memory structure the table is to be stored (TABLESPACE clause, STORAGE () clause). The SQL manuals provide information on this. Nevertheless, the above syntax is still sufficient.

Create index

CREATE INDEX ON ( [, , ....])


Creates an index over the pat_id column of the patient table. An index makes searches across the column used faster. It has an accelerating effect, especially for join operations (see below) or lessons. On the other hand, it takes up space on the hard drive. It is unnecessary to set up an index below a table size of 200 ... 1000 data records (rows). Syntactically, an index is not required for any other SQL statement.

SELECT statement

This is the most important SQL statement. It allows data from a database to be obtained, sorted, formatted, etc. in the most varied of ways. Ultimately, the extremely diverse possibilities can only be hinted at at this point.

An attempt is made to introduce some possibilities step by step:

1. Simple select


Returns all data records in the patient table, complete, in the form of a results table.
supplies the required columns from all data records / rows in the patient table.

After SELECT, either the required columns can be specified, separated by commas, or * for "all columns of the required table.

Returns only the patient with the number 10 from the patient table. This is the first example of a CONDITION in the search.

Possible CONDITIONS in the WHERE clause include: = {equality}! = {Not equal}> <> = <= in (, , ...) {match with a value} BETWEEN AND LIKE can contain wildcards:% for any number of unknown characters _ for one character

When specifying , character strings must be enclosed in single quotation marks (e.g. 'Smith'). In comparison to table and column names, a distinction is made between upper and lower case. Numbers (e.g. patient number), on the other hand, can be specified without any further precautions.

The IS NULL clause must be used to compare whether fields are empty, otherwise nothing will be found.

further examples :
select Name, First Name, Date of Birth from PATIENT where Name! = 'Smith'; select Name, First Name, Date of Birth from PATIENT where Date of Birth BETWEEN '11 -JAN-11 'AND '12 -DEC-12';

ATTENTION: Syntax and semantics in connection with the data type DATE are not uniformly regulated in databases. Under Oracle the principle applies that DATE fields can normally be used without conversion if the Oracle standard date format DD-MON-YY is used (as shown above). In any other case, the conversion functions to_date () or to_char () must be used. (Example from another database: in dBaseIV it must read: SELECT name, first name, date of birth FROM patient WHERE date of birth BETWEEN CTOD ('11 .11.1911 ') AND CTOD ('12 .12.1912');)


outputs the dates of birth formatted in German, and select Name, First Name from PATIENT where date of birth = TO_DATE ('11 .11.1911 ',' DD.MM.YYYY '); selects all patients born on 11/11/11. The general syntax is: - TO_CHAR (, '') or - TO_DATE (, '')

[further information on date representation under Oracle V.7 and 8]

Example for LIKE:
select Name, First Name, Date of Birth from PATIENT where Name like 'M% er'; Finds all names that start with 'M' and end with 'er', e.g. Meyer, Maier, Meier, ... but also Mellenburger!

Several conditions can be linked with the operators AND and OR, e.g .:

select Name, First Name, Date of Birth from PATIENT where Name = 'Smith' AND First Name = 'Hans'; Conditions like BETWEEN or LIKE can be reversed with the NOT operator.

SQL functions

In addition to the date functions already mentioned, Oracle-SQL has a wealth of useful functions that considerably expand the possibilities of finding and displaying data. [brief overview of functions in Oracle-SQL]
Few functions are available in some other databases.

ORDER BY clause

The results of a query can be sorted according to one or more columns in the results table. The syntax of the simple SELECT expands to:

select Name, First Name, Date of Birth from PATIENT where Name! = 'Schmidt' order by Name, First Name; Instead it is usually also possible: select name, first name, date of birth from PATIENT where name! = 'Schmidt' order BY 1,2; / * Columns numbered in the order above after SELECT * /

Subquery in the where clause

A where clause can refer to the result of another select statement.

select * from PATIENT where Pat_ID in (select Fk_PatientPat_id from TUMOR where change date> = '01 -JAN-94 ') Lists the master data of all patients whose diagnostic data (initial survey) has been changed since 1.1.94. Possible solutions for SQL queries with subquery are: SELECT FROM WHERE [NOT] in (
WHERE [AND , ...]

This type of select is known as a join. A join across several tables is even possible. If you want all the columns of a table, you can specify them in the form table-name. *.

select PATIENT.Name, Tumor_ID, diagnosis text, DOCTOR.Name from PATIENT, TUMOR, DOCTOR where PATIENT.Pat_ID = TUMOR.Fk_PatientPat_ID AND TUMOR.Fk_ArztArzt_ID = DOCTOR.Arzt_ID order by PATIENT.Name;

Provides the patients, their tumors and the doctors who provided this data.

A particular problem of join select becomes clear here: a doctor is not given as the source for all documents. The entire document is then suppressed in the form shown above. In order to be able to display the rest of the data record in the event that only one sub-table of a join does not return a value, there is the outer join . It is requested in Oracle by means of a (+) behind the column that may not return a value. If the associated table does not have a suitable value, nothing is displayed for any of its columns in the resulting data record.

select PATIENT.Name, Tumor_ID, diagnosis text, DOCTOR.Name from PATIENT, TUMOR, DOCTOR where PATIENT.Pat_ID = TUMOR.Fk_PatientPat_ID AND TUMOR.Fk_ArztArzt_ID = DOCTOR.Arzt_ID (+) / * <------ * / order by PATIENT.Name;

Now this example shows all patients and their tumors, not just those known to a doctor as the source of the data.

As soon as several tables appear in an SQL statement, columns from different tables involved could have the same name (e.g. here column in the tables and). Then it is necessary to clarify which column comes from which table. To do this, put the name of the table in front of the column name, separated by a period (see e.g.). This can be annoying with longer table names. Oracle therefore allows short Aliases and then to use this within the statement instead of the table name. The above statement could then be written like this:

select P.Name, T.Tumor_ID, T.Diagnosetext, A.Name from PATIENT P, TUMOR T, DOCTOR A where P.Pat_ID = T.Fk_PatientPat_ID AND T.Fk_ArztArzt_ID = A.Arzt_ID (+) / * <--- --- * / order by P.Name;

In such a case it is easy to provide additional clarity by giving all columns in the select list a table prefix.

3. Group formation and functions


A number of sum and group functions can be used in the Select. The simplest is COUNT (*). She counts the lines that apply.

select COUNT (*) from PATIENT; counts the rows in the patient table. select gender, COUNT (*) from PATIENT group by gender;

gives the number per gender in the patient table. further group functions are available:

COUNT (DISTINCT ) Number of rows with different values ​​of a column MAX () Highest value of a column MIN () Lowest value of a column AVG () Average value of a column SUM () Sum over one column
select MAX (date of birth) from PATIENT; determines the youngest patient select AVG (weight), localization from ERST / * no GTDS table * / group by localization; determines the average weight of the patient at the first survey, grouped according to tumor location. When using such functions in conjunction with GROUP BY, the following basic rule must be observed: All column names that appear outside of the counter and status functions in the must also be in the GROUP BY clause. The HAVING clause also requires that only groups are displayed that satisfy . This topic may be dealt with in more detail in class.

Generate a VIEW - "Saved Query"


create View AS

whereas the select statement can in principle be any valid select statement, a query can be "saved", so to speak, and is then available like a table.
Example: after

create View SHORT DIAGNOSES AS select P.Name, T.DiagnoseText from PATIENT P, TUMOR T where P.Pat_ID = T.Fk_PatientPat_ID; it is possible to list the two columns of the view with, and with you get a list of name and diagnostic text. Oracle has only permanently saved the query for the view and retrieves the data from the original tables for each query on the view.

INSERT statement

This statement is used to insert data into a table.

[()] VALUES () You do not need to specify column names if a value is specified for each column in the table.
insert into HAUSARZT / * no GTDS table * / Values ​​('Mümmel', 'Fritz', 'Heinertstr. 1', 8765, 'Olm', 99); insert into ARZT (Doctor_ID, Name) Values ​​(999, 'Jumping Jack');

Most database systems today have a so-called mask generator, so that the input of individual data records can be done much more easily via screen masks. Under Oracle, SQL * Forms is the mask generator.

Syntax (format 2)
SET = , [ = , ...] [WHERE ] The WHERE clause is to be understood as with SELECT.

This allows you to change individual columns of a table in the rows specified by the where clause. If the where condition is missing, all rows of the table are changed . As far as this only concerns individual lines, here too, as with Insert (see above), a screen mask that can be quickly provided with a mask generator will be used. In contrast, the update statement makes sense if a larger number of columns are in the same direction should be changed. The most sensible example of this comes from the commercial area. A price reduction in an article table could be caused by:

update article SET price = (price * 0.95); This would reduce all prices by 5%!

DELETE statement

[WHERE ] The WHERE clause is to be understood as with SELECT. The lines that meet the WHERE condition are deleted, i.e. that would be displayed in a SELECT with the same WHERE clause. ATTENTION: If the WHERE condition is missing, all data in a table will be deleted!

Transaction processing

The database is changed directly with INSERT, UPDATE and especially DELETE statements. After execution, however, the results and the new, changed content are still in a "not final" status. The changed content is already visible to the person who executed it (e.g. with a SELECT), while others who have rights to the same table still receive the old content. First with

COMMIT [WORK]; the changes are finally released and stored in the database. (The keyword WORK is optional under Oracle, does not need to be entered). Alternatively, you could change your mind and use ROLLBACK [WORK]; Undo all changes (INSERT, UPDATE and DELETE) since the last COMMIT or the call of or. This option is particularly important in the case of changes or statements that you are not entirely sure of at first, or if several processes may only be carried out together or not at all (classic example: booking). If you exit without an explicit COMMIT, what is known as an implicit COMMIT is carried out automatically. All data definition statements, i.e. all CREATE, DROP, ALTER and a few other statements also lead to an implicit COMMIT. In this case, the last changes cannot be rolled back.

Access rights for multiple users of an SQL database

Rights are granted with GRANT and revoked with REVOKE. The access options within a database depend on

  • the global rights of the individual users
  • the rights that have been assigned to the individual objects

Global user rights under Oracle 6

When an Oracle user is set up, he can be assigned three levels of rights:
May log in to the database, access objects (tables, views, etc.) to which he has access (SELECT etc.), but not create any new tables or other objects.
May log in to the database, access objects (tables, views, etc.) to which he has access (SELECT etc.), and create new tables or other objects within the space allotted to him.
Database administrator privilege. May access all objects, create new ones, delete existing ones (by any user!) Or change them. The DBA right should therefore only be granted to a few users.

A user with DBA privilege is required to create a new Oracle user. This is done in its most elementary form by e.g.


Assigns the RESOURCE right to the user OPS $ TUMSYS. Since it is an OPS $ account, there are then the following options for access:

  • from almost any Unix user ID.
  • if you are logged in as a Unix user tumsys.

If a new user is created as of Oracle7, the options for differentiated assignment of rights should be used. The enclosed example user_acc7c.sql shows how this could look for the GTDS administrator OPS $ TUMSYS

User rights for individual objects

These rights are granted with GRANT in the form GRANT ON TO e.g. GRANT SELECT, INSERT, UPDATE ON TUMOR TO OPS $ HANS; If a right is to be granted to everyone, the pseudo-user PUBLIC can be used: GRANT SELECT ON LOCATION TABLE TO PUBLIC; allows all read (SELECT) access to the location table. Rights granted with GRANT can be revoked with REVOKE. Further information on user rights can be found in the sections on GRANT and REVOKE in the Oracle SQL reference manual.

A few sentences on data exchange under Oracle

If you want to output data from the Oracle database for other systems (e.g. dBase), you have to manually generate the appropriate format using SQL * Plus or SQL * ReportWriter using suitable SQL statements (somewhat more difficult than the simple UNLOAD statement from Informix ).

The SQL * Loader is available for loading text files. This tool, which is not necessarily easy to use, is described in PartII of the RDBMS Utilities User's Guide. A description file with commands for the loading process must be created. However, if you have Oracle RDBMS for PC and want to load dBase files, this task is made easier by the DB3PREP tool. The export and import tools described in the RDBMS Utilities User's Guide are available for data transfer between two Oracle databases, even on completely different computer systems. It is very easy to use (see below). However, only pure data transfer (including certain character set conversions, for example from EBCDIC to ASCII) is possible. The external format of these tools is not meaningfully readable by other programs. Their use is also limited as archiving tools, since later versions of Oracle may no longer be able to read old export files. An example session follows:
Export of all tables from OPS $ TUMSYS provided that you are logged in as tumsys under Unix or Novell:

exp userid = / file = tumsys grants = y
All table definitions and contents are written to a file tumsys.dmp. Its basic content can be with
imp / SHOW = Y file = tumsys
be looked at. Rereading would also be done with imp. With imp help = y or exp help = y you get syntax help for the command lines.

[Part 2]

[Beginning] [previous section] [next section]

One function is what the coffee machine does for good coffee - you pour powder and water in at the top and the coffee comes out at the bottom. In the same way, a function takes on one or more values, and something different arises from the processing within the function.

First now an overview:

The arguments of the functions are represented symbolically. There means

a general string. This can either be a literal such as 'Dooof' or the name of a column.
a date, again either a string in the correct format or a database column
correspondingly a number
Please pay particular attention to the conventions in the main script with regard to repetitions and optional arguments. Accordingly, values ​​are enclosed in angle brackets (<>), optional parts in square brackets ([]).

Functions for individual data sets

Typical string functions
Outputs characters from position in . The position is counted from 1. If length is omitted, everything from position is shown. If is greater than the length of , NULL is returned.
Returns the length of in characters.
Outputs converted to UPPER CASE.
Outputs converted to lowercase letters.
Converts the first letter of each word in to capital letters.
or. Adds or spaces (if omitted) to on the left (LPAD) or right (RPAD) side until is reached.
or. If no second argument is given, blanks are removed from on the left (LTRIM) or right (RTRIM) side. Otherwise Oracle tries to remove from the end of the relevant page until it encounters a character string that does not correspond to .
Outputs in a fully visible format. If 16, a "hexdump" is created. Clears the contents of database columns if they suspect invisible characters.
CHR () and ASCII () returns the ASCII character number (function CHR ()) or the ASCII number of the first character in (function ASCII ())
Search function - Returns the position of within . For example, INSTR ('DOOF', 'OO') would find 2 (position of 'OO' in 'DOOF'). If is specified, the search is only carried out from there (-1 means searching backwards from the end). The last parameter allows the 2nd, 3rd etc. occurrence of to be found.
Outputs , where all occurrences of are replaced by . If is ​​missing, all occurrences of in are deleted.
In contrast to REPLACE () character by character Replacement. As a result, every letter that occurs in is replaced by the corresponding letter in . If contains fewer letters than , the letters from will be deleted without a correspondence in . must contain at least one letter, otherwise the result is always NULL.
DECODE (, , , , , ...., ) This extraordinarily powerful SQL function can only be found with Oracle. It allows individual values ​​from the database to be replaced by others and a default value to be named.
GREATEST (, [, ] ...) Returns the largest of the multiple values ​​(similarly, LEAST () returns the smallest). Works with character or numeric columns, with date values ​​only when converted with TO_DATE ().
Replacement of NULL values
If has a content (SQL-wise:), is used, if not then . can be of the type CHAR, VARCHAR2, NUMBER, DATE etc.; the substitute value must have a suitable data type.
Conversion of data types
or. Converts the date into a (displayable) character string according to the format or vice versa. Covered in detail in the article on date formats.
or. Converts a number into a displayable character string or vice versa. Since Oracle carries out this conversion automatically in many cases, these functions and in particular the format (about which manuals or online help provide information) are less essential than the date functions. TO_CHAR () looks the same as the corresponding date routine; it determines from its arguments which conversion is required.
Numeric functions (in a broader sense)
and These functions are used to round or cut off numbers to decimal places. However, you can also click Date values The time (which is always present) is then set to the beginning of the day (TRUNC ()) or midday 12:00 (ROUND ()). There is even the possibility to use date formats instead of and to round / anonymize dates of birth to whole years.
Of the common math functions at least the following are available under Oracle:
The accuracy is up to 36 digits. The four basic arithmetic operations can be used directly in SQL statements (shows the result 4)

Sum and group functions

refer either to all rows of a table or to groups that are formed by the GROUP BY clause. With a few exceptions (MIN MAX, for example), they are only useful for numeric columns.

important sum and group functions
MIN () and MAX () returns the smallest or largest value of the relevant column in the selected rows. Works with character, numeric, and date columns.
COUNT (*) returns the number of affected lines without restriction
COUNT () returns the number of affected lines in which the value of is not empty (NULL).
COUNT (DISTINCT ) returns the number of affected lines with different, non-empty values ​​(number of different values).
AVG () Returns the arithmetic mean for all lines where is not empty (NULL).
SUM () Returns the total for all lines where is not empty (NULL).
VARIANCE () or STDDEV () Provide statistical values ​​for all lines where is not empty (NULL).

[Part 3]

[Beginning] [previous section] [next section]
  1. "classic" default format
  2. Date formatting using functions
  3. Change default format with Oracle7

While the external representation can look very different, Oracle7 saves time information, i.e. date and time, internally in table columns of the data type DATE always in the same format. This format comprises 7 bytes, it always contains the date and the time, accurate to the second, and a maximum of times between approx. 4712 BC. and AD 4712. being represented.

If a table column of the data type DATE is to be displayed (e.g. for a select statement in SQLPLUS), it is converted into an external format for display. There is a default format and, on the other hand, the option of choosing other formats.

classic default format

[ back]

In older Oracle versions, the default format was common DD-MON-YY :

1 select Name, Date of Birth from PATIENT 2 where Date of Birth IS NOT NULL 3 * AND Rownum <10 SQL> / NAME DATE OF BIRTH -------------------------- ---- --------- Testele 12-MAY-67 G2Testcc 12-JUN-67 Schoenholz 11-NOV-11 Testele 12-JUN-57 Altmann 01-FEB-58 Janssen 10-OCT-07 Schmidt 11-FEB-96 Schmidt 11-NOV-70 Kirschberger 11-FEB-68 9 rows selected.

This format should be well known to all Oracle users.

If a date is to be used in a where clause, it must have the default format if the appropriate functions are not used:

1 select name, date of birth from PATIENT 2 where date of birth = '11 -NOV-11 '3 * AND Rownum <10 SQL> / no rows selected

The default format used by Oracle for a long time consists of a two-digit day, then the (usually English) month abbreviation with three letters and the two-digit year, which is assumed in the current century. Accordingly, 11-NOV-11 currently means November 11, 2011.

Date formatting using functions

[ back]

However, using the Oracle functions TO_CHAR () and TO_DATE () it is possible to generate or accept almost any other format. The basic syntax is

- TO_CHAR (, '') or - TO_DATE (, '')

Some typical formats would be

Oracle date formats
formatcomment example "real" date
DD-MON-YYold default format 25-FEB-98 25.2.2098
04-SEP-00 4.9.2000
DD.MM.YYGerman format 2-digit. Year, on course PC25.02.9825.2.2098
DD.MM.YYYYGerman format 4-digit. year25.02.199825.2.1998
DD.MM.YYYY HH24: MI: SS4-digit Year and time: course start4.9.2000 10:15:004.9.2000 10:15:00
YYYYMMDDHH24MI4-digit Year and time: courses start immediately one after the other according to size2000090410154.9.2000 10:15
MM / YYYYonly month and year9/20001.9.2000
at the turn of the millennium: "relative" formats
To make it easier to enter dates before and after the turn of the millennium, "relative" formats were created. For the year it is now possible to use "RR" instead of "YY". Then years are counted from 51-99 in the past century, 0-49 in the current one. The "RRRR" format (from Oracle V. 7.2) treats two-digit years like "RR", four-digit years like "YYYY".
DD-MON-RRold default format improved25-FEB-98 25.2.1998
04-SEP-00 4.9.2000
DD.MM.RRGerman format 2-digit. Year improved25.02.9825.2.1998
DD.MM.RRRRGerman format 4-digit. Year "relative"25.02.199825.2.1998
two-digit year is understood from the four-digit year format "RRRR" 25.02.9825.2.1998
Years before 1950 can now only be entered in a four-digit format ("YYYY" or "RRRR"). 11.11.191111.11.1911

Countless other formats and combinations are possible. For this, reference must be made to the original SQL manuals and good secondary literature.

An example shows what comes out of some date formats, first the output

SQL> l 1 select Name, TO_CHAR (date of birth, 'DD-MON-YY') DD_MON_YY, 2 TO_CHAR (date of birth, 'DD.MM.YY') DD_MM_YY, 3 TO_CHAR (date of birth, 'DD.MM.YYYY') DD_MM_YYYY , 4 TO_CHAR (date of birth, 'DD.MM.RRRR') DD_MM_RRRR 5 from PATIENT 6 where date of birth IS NOT NULL 7 * AND RowNum <20 SQL> / NAME DD_MON_YY DD_MM_YY DD_MM_YYYY DD_MM_RRRR ------------ ------------------ --------- -------- ---------- ----- ----- Testele 12-MAY-67 12.05.67 12.05.1967 12.05.1967 G2Testcc 12-JUN-67 12.06.67 12.06.1967 12.06.1967 Schoenholz 11-NOV-11 11.11.11 11.11.1911 11.11.1911 Testelen 12-JUN-57 12.06.57 12.06.1957 12.06.1957 Altmann 01-FEB-58 01.02.58 01.02.1958 01.02.1958 Janssen 10-OCT-07 10.10.07 10.10.1907 10.10.1907 Schmidt 11-FEB- 96 02/11/96 02/11/1996 02/11/1996 Schmidt 11-NOV-70 11/11. 70 11.11.1970 11.11.1970 Kirschberger 11-FEB-68 11.02.68 11.02.1968 11.02.1968 Janssen 04-APR-50 04.04.50 04.04.1950 04.04.1950 A 1st name 01-JAN-01 01.01.01 01.01 .1901 01.01.1901 NAME DD_MON_YY DD_MM_YY DD_MM_YYYY DD_MM_RRRR ------------------------------ --------- - ------- ---------- ---------- A 3rd name 03-MAR-03 03.03.03 03.03.1903 03.03.1903 Kxxx 30-AUG- 36 08/30/36 08/30/1936 08/30/1936 Wxxxxxx 02-OCT-57 02.10.57 02.10.1957 02.10.1957 Brxxxyz 21-OCT-56 21.10.56 21.10.1956 21.10.1956 Dr Karlmann 11-NOV-45 11.11. 45 11/11/1945 11/11/1945 .... etc. ......

As you can easily see, DD.MM.YYYY and DD.MM.RRRR do not differ in the output.

If date values ​​are to be used in the comparison or input, either the default format must again be used or the date value must be changed appropriately for Oracle using TO_DATE (). Example - we are looking for people born on 11/11/1911

SQL> select Name, TO_CHAR (date of birth, 'DD.MM.YYYY') DD_MM_YYYY 2 from PATIENT 3 where date of birth = '11.11.1911 '4 / ERROR: ORA-01843: not a valid month no rows selected

When entering dates in SQLPLUS, like character strings, they are enclosed in apostrophes. In this example you can see that Oracle does not easily recognize the German format. The second attempt is made with the default format

SQL> l 1 select Name, TO_CHAR (date of birth, 'DD.MM.YYYY') DD_MM_YYYY 2 from PATIENT 3 * where date of birth = '11 -NOV-11 'SQL> / no rows selected SQL>

We were looking for the patients who were born on November 11, 1911. According to the format table above, Oracle understands 11-NOV-11 as 11.11.2011 - correct, that it has not found anything. Only the use of TO_DATE () delivers the correct result

SQL> l 1 select Name, TO_CHAR (date of birth, 'DD.MM.YYYY') DD_MM_YYYY 2 from PATIENT 3 * where date of birth = TO_DATE ('11 .11.1911 ',' DD.MM.YYYY ') SQL> / NAME DD_MM_YYYY - ----------------------------- ---------- Schoenholz 11.11.1911 B97test 11.11.1911 nonsense 11.11. 1911 Missman 11.11.1911 Wert 11.11.1911 Möllemann 11.11.1911 Mistermann 11.11.1911 mussel 11.11.1911 Mistermann 11.11.1911 Bender 11.11.1911 test woman 11.11.1911 NAME DD_MM_YYYY --------------- --------------- ---------- Test man 11/11/1911 Test patient 11/11/1911 Pißmal 11/11/1911 Melana 11/11/1911 15 rows selected.

In comparison to the lists above, it should be noted that these have been shortened

This is the correct result.

Additional note - the time component of date fields was neglected in the examples. If date values ​​are entered into an application such as GTDS via mask fields, the associated time (which is always saved) is normally set to 00:00. In other cases, date fields could be handled with the TRUNC () or ROUND () functions.

Change default format with Oracle7

[ back]

Oracle7 allows you to change the default date format. A distinction must be made between two levels of attitude

1) Parameters for the whole database

A number of settings can be made via the INIT.ORA parameter file, which is read when the database is started. These values ​​can be easily viewed using SQL:


The two parameters NLS_DATE_FORMAT and NLS_DATE_LANGUAGE determine how date values ​​are output if no functions such as TO_CHAR () are used. NLS_DATE_LANGUAGE defines the language for day and month names. Although it makes sense to set an inexpensive, German date format at this point, this method is not necessarily recommended, as unexpected side effects can occur when using a large number of tools and programs. The usual default has therefore been left in our test database.

2) Parameters for the current session

The above parameters and thus also the date format can also be set for the current session, the current connection to the database. It is sufficient to display them first


The date format for the current session can then be changed with, the instruction also takes effect immediately. A few examples show the principle:

SQL> select name, date of birth from PATIENT 2 where date of birth is not NULL 3 AND RowNUM <4 4 / NAME OF BIRTHDAY --------------------------- --- --------- Testele 12-MAY-67 G2Testcc 12-JUN-67 Schoenholz 11-NOV-11 SQL> alter session set NLS_DATE_FORMAT = 'DD.MM.RRRR'; Session altered. SQL> select * from NLS_SESSION_PARAMETERS 2 where PARAMETER LIKE '% DATE%' 3 / PARAMETER VALUE ------------------------------ - ----------------------------- NLS_DATE_FORMAT DD.MM.RRRR NLS_DATE_LANGUAGE AMERICAN SQL> 1 select name, date of birth from PATIENT 2 where date of birth is not NULL 3 * AND RowNUM <4 SQL> / NAME BIRTHDATE ------------------------------ ------- --- Testele 05/12/1967 G2Testcc 06/12/1967 Schoenholz 11/11/1911 SQL> select Name, Date of Birth 2 from PATIENT 3 where Date of Birth> '1.1.65' 4 / NAME DATE OF BIRTH ------------- ----------------- ---------- Testele 05/12/1967 G2Testcc 06/12/1967 Schmidt 02/11/1996 Schmidt 11/11/1970 Kirschberger 02/11/1968 testiboy 02/11/1968 Meyer 07/07/1977 Bdt test patient 03/17/1987 Ali 11/16/1966 Beckenbauer 02/11/1968 Zimmer 01/01/1977 NAME DATE OF BIRTH ----------------------- ------- ---------- Klein 08.08.1988 Testkind 15.10.1977 Musterli 15.03.1967 Dick 10.03.1965 15 rows selected.

In this last example you can see that the new NLS_DATE_FORMAT takes effect immediately and applies to all date fields of the current session - both for output (which would otherwise always have to be formatted individually with TO_CHAR ()) and for input (where else TO_DATE ( ) would be necessary if the date is not in the default format). The WHERE clause would not be accepted with the default format DD-MON-YY, with a date format with a two-digit year 65 would be interpreted as 2065 and no patients would be found. But after we have set the format with the command, 65 is understood as 1965 "relative".

Use of old SQL scripts

Many users still have older SQL scripts that use the classic date format DD-MON-YY. Before the turn of the millennium, a query for the diagnoses was like this

SQL> select Fk_PatientPat_ID, Tumor_ID, diagnosis text from TUMOR where diagnosis date BETWEEN '01 -JAN-95 'AND '01 -JAN-99'; possible and delivered the diagnoses between 1.1.1995 and 1.1.1999. In 2000, surprisingly, it was found that such queries suddenly no longer returned any data records! The reason for this is that in the default format DD-MON-YY the year is always added to the current century. After the turn of the millennium, the same Query diagnoses between 01/01/2095 and 01/01/2099 searched and not found ...

A remedy is easily possible, as you can see in the table above, there is the modification DD-MON-RR for the default format. Then years between 51 and 99 will be considered from the past Century interpreted, and on the other hand the representation remains unchanged, so that you do not have to change laboriously created SQL programs. To

SQL> alter session set NLS_DATE_FORMAT = 'DD-MON-RR'; the above query delivers the correct data again in 2000.

Specify the date format in start files for SQLPLUS

The setting of the date format remains in effect for the current session. If you exit SQLPLUS and call it up again, this setting is reset. However, you can write or append this SQL statement to a file in the current directory (where you call SQLPLUS). Then it will be executed every time SQLPLUS is started.

Note for alphanumeric GTDS users - in the typical GTDS directory under Unix (®), only the DD-MON-RR format is allowed, others can lead to unpleasant surprises with the old tools.

[Part 4]

[Beginning] [previous section] [next section]

When creating queries and reports, additional information, e.g. about the existing tables, indexes, etc., is often required first. In most cases, Oracle provides this information in the form of Tables or views are available that can be easily queried with SELECT.

Who am I ?

Sometimes it is unclear as which Oracle user you are currently connected to the database. Creates clarity

SQL> select USER from DUAL 2 / USER ------------------------------ OPS $ DOOF

The current system time of the database can also be determined with

SQL> select TO_CHAR (SysDate, 'DD.MM.YYYY HH24: MI: SS') 2 from DUAL 3 / TO_CHAR (SYSDATE, 'DD.MM.YYYYHH24: MI: SS') --------- -------------------------------------------------- ---------------- 01.09.2000 11:42:30

List of columns for known objects

If the name of a table or a view is known, the columns and, if necessary, further information can be read out with the SQLPLUS command (can be abbreviated to):


List your own objects

With the simple


you will receive a list of your own tables. CAT is an abbreviation (synonym) for USER_CATALOG. You then receive the columns of the listed objects using DESC (see above), the content using SELECT statements.

List all accessible objects

In addition to your own objects, there can be others that you have access to. In addition to the typical information tables of the system (such as USER_CATALOG), this also includes tables from other users who have granted rights for them. GTDS makes extensive use of this - the tables belong to OPS $ TUMSYS, which, however, grants the users of the GTDS appropriate rights to these tables (GRANT). By querying ALL_CATALOG, you can see the objects that are accessible to you:


Only a section can be reproduced here, the complete output comprises almost 2000 lines.

Further information tables

A great deal of additional information about the database and its objects is made available by Oracle in the form of tables, although the options vary depending on the user rights. You will receive an overview of all such tables with

SQL> l 1 * select * from dictionary SQL> / TABLE_NAME COMMENTS ---------------- -------------------- ------------------------------------------ ALL_ARGUMENTS Arguments in object accessible to the user ALL_CATALOG All tables, views, synonyms, sequences accessible to the user ALL_CLUSTERS Description of clusters accessible to the user ALL_CLUSTER_HASH Hash functions for all accessible clusters _EXPRESSIONS ALL_COL_COMMENTS Comments on columns of accessible tables and views ALL_COL_PRIVS Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee ................. etc. ..............

Those tables and views whose names begin with USER_ always refer to their own objects, those whose names begin with ALL_ to all accessible objects.

For GTDS users, there are a number of ready-made scripts for specific tasks (such as listing all indexes for a table). With the alnum GTDS they are in the directory; graphic users can have them separately on request.

Table information in the GTDS

The documentation for the graphical GTDS contains the file (in the subdirectory of the graphical GTDS directory) with a list of the tables and their columns in the GTDS. In both the alnum and the graphic GTDS, table information can be called up via screen masks.

[Part 5]

[Beginning] [previous section]

The SQL * Plus tool supports the execution, entry and formatting of SQL statements under Oracle. Some special features are to be shown below:

Calling SQL * Plus:

SQL * Plus is created with the sqlplus user / password command

called. If you have an OPS $ account under Oracle, the call can be in the form

sqlplus /

happened (e.g. as logged in under Unix, Oracle user name).

If the username and / or password are not specified, SQL * Plus will ask for them.

Here in the course you can possibly simply call up SQL * Plus from the menu or from the GTDS.

For Oracle (®) installations under Windows95 / 98 / NT (®) there is usually an icon on the desktop (large yellow plus sign above disk stack) or a point in the start menu under "Oracle Products for Windows" or similar.
However, if you want to start SQLPLUS from the command line, you get the purely line-oriented version with or (Oracle8), the Windows version with or (Oracle8).

Both versions first ask for the Oracle user name, password and the database name. The latter refers to the name of a database alias (SQL * Net Alias) which was previously defined, advantageously using SQL * Net Easy Config.

Exit from SQL * Plus

with the command or or input of Control-D at the beginning of a line, SQL * Plus is exited.

Entering SQL statements:

At the input prompt (or similar) you enter your SQL statements. If they are too long for one line, just enter and continue writing on the next line. SQL statements are terminated with (semicolon) and passed for execution. Instead, you can also enter (Slash) at the beginning of a line. If you only enter at the beginning of a line, SQL * Plus repeats the last SQL statement entered without further input, since it stores SQL statements in a buffer. With Control-C you can cancel the current action.

SQL * Plus commands

The following will be variable Italicized, for this you then have to insert a real value, e.g. a real file name for file or a number for NNN.

A number of useful commands that are executed by SQL * Plus outside of SQL are available (upper or lower case is arbitrary, as is the case with SQL itself). These commands do not have to be terminated with a semicolon. For representation see conventions in the SQL script. Parts in [] are optional (usually the command can be abbreviated, e.g. instead of). SQL * Plus commands are not stored in a buffer, i.e. they have to be entered again and again for interactive work. However, they can be in a command file (see below).

l [is]
Lists the statement that is currently still saved. This SQL statement could be executed again by simply entering a slash ().
Fetches the current SQL statement in the text editor. This editor can be specified with the command define _editor = , e.g. define _editor = vi. You can enter the latter command directly or save it in the login.sql file in the current directory, which SQL * Plus reads in this directory each time it is called.

For the course environment, see the appendix: Editing statements under SQL * Plus. For practical reasons, there is a common login.sql file in the course.

ed file
Get the file file.sql in the text editor.

begin file
@ file
Leads file or. file.sql (if no extension is given). This file is treated as a SQL * Plus command file. Calling SQL * Plus from the command line can also be done with a command file in the form file . If you want to exit SQL * Plus immediately afterwards, the command file must contain EXIT as the last command.

Save file
saves the current SQL statement (only that) in file. If the specified name has no ending, it is appended.
get file
loads file according to SQL * Plus. Only makes sense if file Contains only one SQL statement and no SQL * Plus commands such as COL or BREAK. This could then be carried out immediately with / (slash) and return.
ho [st]
Executes the specified operating system command, e.g. under Unix or under MSDOS . Instead, under Unix, as in some other programs, (exclamation mark) can be used. If the argument is missing, the command interpreter of the operating system is called (so you get into a shell under Unix and under MSDOS in COMMAND.COM). He can go with exit to be left again.
desc [ribe] table

gives an overview of the columns, data types etc. of table
spo [ol] file
writes everything that SQL * Plus now outputs in file.lst. This output is ended with SPOOL OFF. Example: SPOOL list writes all the following output to the file SPOOL OFF terminates this output and closes the file.


requests help, e.g. help select . To do this, the SQL * Plus help tables must have been loaded during the installation (this is of course the case here).
set pagesize nnn

sets the page size (in lines per page) for SQL * Plus
set linesize nnn

defines the line length for SQL * Plus, so that it also determines the point at which lines are broken. If the output is in a file (SPOOL, see above), all lines up to this length are padded with spaces!
set spa [ce] nnn

Specifies the spacing (number of spaces) between the individual columns when SQL * Plus represents the results of an SQL query. The default is 1 (one space between the columns). To set space 0 the columns are simply written one behind the other without any space (sometimes useful for data transfer to other systems). for SQL * Plus
set pause on

If pause is switched on, SQL * Plus first waits for the input after the start of a select statement, then pauses again after each lines and waits for (or Control-C to abort). This mode can be used with set pause off be switched off again.

Column headings and formatting

SQL * Plus presents the results of SQL queries in a standard format, with column headings. An abundance of commands and directives allows this to be modified and in this way makes SQL * Plus a simple report generator. At this point only a few possibilities can be indicated:

- switches the column headings on or off.
col [umn] column head heading

Outputs the specified heading above the corresponding column from the select statement. If it does not consist of one word, it must be enclosed in apostrophes '...'. In this case, you can also add a line feed to the heading.
col [umn] column format] format

Allows columns to be formatted for the output of Select, in particular to determine their length (important for VARCHAR columns, the length of which is determined by the maximum possible content).
format is used for CHAR and VARCHAR columns in the form aNNN specified, NNN stands for the length as a whole number, e.g. the name is then wrapped if it is longer than 20 characters. However, this behavior can be modified in various ways.
There are different formats for numeric columns. The most important are the formats 999990 (the number of 9 and 0 determines the number of digits) and 9.99999EEEE (scientific notation, there must be exactly four E in the format). Examples: for four-digit display of the Pat_id or for display of the weight with one decimal place.
Date fields are formatted appropriately with the capabilities of for date fields (see SQL script and SQL reference manual).

Extended capabilities of SQL * Plus : The capabilities of this tool go far beyond the scope of this presentation. For example, the BREAK and COMPUTE commands can be used to process group changes, form subtotals and totals, etc. Furthermore, PL / SQL, the database programming language from Oracle, can be entered and executed from SQL * Plus. Another important possibility is the use of SQL * Plus as a code generator for yourself: by means of meaningful SQL statements, SQL statements and statements for SQL * Plus can in turn be generated. If you transfer these outputs to a file, this can then be executed again with start. This technique can be used, for example, if a user wants to grant another user rights for all of his tables with GRANT.

Simple example report with SQL * Plus

This report uses the elementary functions of SQL * Plus. The (somewhat confused) goal is to produce a list of patients, sorted and formatted for each health insurance company. At the same time, the average age of the patients should be determined.

Such a report is created with an editor and saved in a file, e.g.. It can then be called from SQL * Plus with. Before doing this, you can enter and test parts step by step in interactive work with SQL * Plus. The editor can be called from SQL * Plus at any time using and the improved report definition can then be restarted.

The example report uses tables as they actually exist in the GTDS.

rem header and footer for the pages ttitle 'patient list | sorted by health insurance companies' btitle '(extract from the current database of the register)' rem column formatting column service provider format a20 word_wrapped column name format a20 word_wrapped column first name format a15 truncated rem to_char gives otherwise Length of approx. 100 column born format a10 truncated heading 'Geb. am 'column age format 90 heading' age | (years) 'rem group change and age calculation rem to break an order by must always match the same column break on service provider skip 2 compute avg of age on service provider rem page dimensions, newpage 0 sends form feed to Page change set linesize 79 set pagesize 60 set newpage 0 spool kassenliste select service provider, surname, first name, TO_CHAR (date of birth, 'DD.MM.YYYY') Born, (NVL (date of death, SysDate) - date of birth) / 365 age from LEISTUNGSTRAEGER, PATIENT where Institutionskennze = Fk_Leistungstraeins order by service provider, surname, first name / spool off

APPENDIX: Editing statements under SQL * Plus

The command line input under SQL * Plus is unfortunately a bit old-fashioned and simple. You cannot use the cursor keys when entering data at the prompt, not to mention Home, End, Image, etc. If you have made a mistake, you normally have to delete the error with backspace and then rewrite from there. Retrieve previous lines with SQL * Plus -Commands are not possible. Neither is there a completion mechanism as you might know from the Kermit command line. The input comfort corresponds to old shell command lines (!).

Only SQL (not SQL * Plus) commands are stored in a buffer. An SQL command (i.e. Select, Insert, Update, etc.) can be repeated with just one line. Within SQL * Plus, only a very limited editor is available for SQL statements only. Basically, only a few commands are useful:

l [is]
lists the current SQL statement with line numbers.
makes the line with the specified number the current line.
c / text / replacement

replaces in the current line Text \ f1 by \ f2Replacement \ f1. In \ f2Text works like a * in the shell, i.e. stands for any text.
I [nput]

Adds lines after the current one. See examples in the SQL * Plus User's Guide and Reference, Chapter 6 p. 45.
A [ppend]

appends text to the current line (enter two or more spaces after append if the text should be separated from the existing part of the line by spaces). See examples in the SQL * Plus User's Guide and Reference, Chapter 6 p. 8.

If you want to make more complex changes, edit SQL * Plus statements or entire reports, you should always use the editor. Individual ones currently being accessed SQL -Statements can simply be fetched into the editor with at the SQL * Plus prompt, whereby the statement is then edited in a buffer with the name. For more complex things, a separate file is always required, which should have the extension .sql for execution with SQL * Plus. The editor is simply called with such a command file, e.g. the sample report (see above) from SQL * Plus (see above). Of course, you can also create such a file completely outside of SQL * Plus.

A freely available program is used as the editor in the course, which in principle is like the well-known text program WordStar is working. You can switch on a help window with and exit the editor with.

Under Windows can use SQL statements with the Notepad can be edited, and many options of SQL * Plus are set in an interactive, graphical user interface.

[Beginning] [previous section]

For example, this is set up in the GTDS. The script takes three arguments for

  1. the new Oracle username
  2. the initial password
  3. the TABLESPACE (part of the database), where the new user should first create his objects.