Some typical formats would be
|Oracle date formats|
|format||comment ||example ||"real" date |
|DD-MON-YY||old default format ||25-FEB-98 ||25.2.2098 |
|04-SEP-00 ||4.9.2000 |
|DD.MM.YY||German format 2-digit. Year, on course PC||25.02.98||25.2.2098|
|DD.MM.YYYY||German format 4-digit. year||25.02.1998||25.2.1998|
|DD.MM.YYYY HH24: MI: SS||4-digit Year and time: course start||4.9.2000 10:15:00||4.9.2000 10:15:00|
|YYYYMMDDHH24MI||4-digit Year and time: courses start immediately one after the other according to size||200009041015||4.9.2000 10:15|
|MM / YYYY||only month and year||9/2000||1.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-RR||old default format improved||25-FEB-98 ||25.2.1998|
|04-SEP-00 ||4.9.2000 |
|DD.MM.RR||German format 2-digit. Year improved||25.02.98||25.2.1998|
|DD.MM.RRRR||German format 4-digit. Year "relative"||25.02.1998||25.2.1998|
|two-digit year is understood from the four-digit year format "RRRR" ||25.02.98||25.2.1998|
|Years before 1950 can now only be entered in a four-digit format ("YYYY" or "RRRR"). ||11.11.1911||11.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: SQL> select * from NLS_DATABASE_PARAMETERS 2 / PARAMETER VALUE ------------------------------ ---------- -------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS., NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICECAN NLSARSET_GARACTERS. 0 11 rows selected.
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 1 * select * from NLS_SESSION_PARAMETERS SQL> / PARAMETER VALUE ------------------------------ --------- --------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS., NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICAN BINARY 9 rows NLSORT_COLORED GRES_CAN NLS.
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): SQL> desc PATIENT name null? Type ------------------------------- -------- ---- PAT_ID NUMBER (10) NAME VARCHAR2 (30) FIRST NAME VARCHAR2 (30) TITLE VARCHAR2 (30) DATE OF BIRTH DATE GENDER VARCHAR2 (1) DATE DATE DATE DATE_DATUM_EXAKT VARCHAR2 (1) NATIONALITAET VARCHAR2) (3) VARCHAR2) (3) VARCHAR2 (VARCHAR2) VARCHAR2 (3) STRASS2 (30) ... etc. ...
List your own objects
With the simple SQL> select * from CAT 2 / TABLE_NAME TABLE_TYPE ------------------------------ ---------- - DEPARTMENT TABLE ADDRESSES TABLE SURGERY SYNONYM DOOFRUF TABLE GTDS_ZENTRUM TABLE ENT SYNONYM LONGTEST TABLE PLAN_TABLE TABLE TEST TABLE TESTE VIEW TEXT FILES TABLE 11 rows selected.
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: SQL & gt select * from ALL_CATALOG order by Owner, Table_Name 2 / OWNER TABLE_NAME TABLE_TYPE ------------------------------ ----- ------------------------- ----------- OPS $ DOOF TEST TABLE OPS $ DOOF TESTE VIEW OPS $ DOOF TEXT FILES TABLE OPS $ TUMSYS AA_DIAGNOSESICHERUNG TABLE OPS $ TUMSYS BILLING AGENCY TABLE OPS $ TUMSYS ABSCHLUSS TABLE OPS $ TUMSYS ABSCHLUSS_PROTOKOLL TABLE OPS $ TUMSYS ABSCHLUSS_VIEW VIEW OPS $ TUMSYS DEPARTMENT ABTEILUNG TABLE OPS ... .........
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
PROMPT "USER_ACC " select TABLESPACE_NAME from sys.dba_tablespaces / CREATE USER & 1 IDENTIFIED BY & 2 / GRANT CREATE SESSION TO & 1 / GRANT CREATE VIEW TO & 1 / ALTER USER & 1 DEFAULT & TABLESPACE & 3 USER & 1 QUOTA UNLIMITED ON & 3 / GRANT CREATE CLUSTER TO & 1 / GRANT CREATE ANY INDEX TO & 1 / GRANT CREATE PROCEDURE TO & 1 / GRANT ALTER SESSION TO & 1 / GRANT CREATE SEQUENCE TO & 1 / GRANT CREATE SYNONYM TO & 1 / GRANT CREATE TABLE TO & 1 / GRANT CREATE TRIGGER TO & 1 / [start] [back]
- the new Oracle username
- the initial password
- the TABLESPACE (part of the database), where the new user should first create his objects.