1 SET Changes the characteristics of SQL terminal sessions. You can control the: o Currency indicator to be displayed for output o Display format for date values, time values, or both o Default path name in the data dictionary o Digit separator to be displayed for output o Number of statements to be included in the editing buffer when you type EDIT * o Language to be used for month abbreviations, and so on, in date and time input and display o Length of lines to be displayed for output o Page length for HELP display o File in which the session is recorded o Number of rows output, the number of seconds allowed per query compilation and execution, or the amount of CPU time expended for each query compilation and execution o Character used to display the radix point in output o Display of statements from a command file o Display of warning messages about deprecated features o Display of warning messages about nonstandard syntax o Continue character 2 Environment You can use these SET statements in interactive SQL only. 2 Format SET +> CURRENCY SIGN currency-char ----------------------+-> +> set-date-format ----------------------------------+ +> CONTINUE CHARACTER continue-char -----------------+ |> DICTIONARY --> -----------------------+ +> DIGIT SEPARATOR digit-sep-char -------------------+ +> set-edit -----------------------------------------+ +> EXECUTE ------------------------------------------+ +> NOEXECUTE ----------------------------------------+ +> LANGUAGE language-name ---------------------------+ +> LINE LENGTH --> ------------------------------+ +> set-output ---------------------------------------+ +> PAGE LENGTH ----------------------------------+ +> RADIX POINT radix-char ---------------------------+ +> VERIFY -------------------------------------------+ +> NOVERIFY -----------------------------------------+ +> set-warning --------------------------------------+ +> set-flagger --------------------------------------+ +> sql-plus-options ---------------------------------+ set-date-format= ----> DATE FORMAT +> DATE +-----------------------++-> | +> , TIME +| +> TIME +-----------------------++ +> , DATE + set-edit= --> EDIT ---+--> KEEP ---> +--> +--> NOKEEP -------+ +--> PURGE --------+ set-output= --+--> LOGFILE --+------------------------+-----> quoted-filespec -+---> | +-> (logfile-options) ---+ | +--> OUTPUT ----+-----------------+------------------------------+ | +--> -+ | +--> NOLOGFILE --------------------------------------------------+ +--> NOOUTPUT ---------------------------------------------------+ logfile-options = --+-> ECHO ---------+--> +-> NOECHO --------+ set-warning= ----> WARNING -+-> DEPRECATE ---+-----> +-> NODEPRECATE -+ set-flagger = ---> FLAGGER --+-> ON ------------------------+-+-> +-> SQL89 --------+-+--------+-+ | +-> SQL92_ENTRY --+ +-> ON --+ | +-> MIA ----------+ +-> OFF -+ | +-> OFF -------------------------+ sql-plus-options = -+-> ECHO -----+-+-> ON --+------------+-> +-> HEADING -+ +-> OFF -+ | +-> TIMING --+ | +-> FEEDBACK -+-> ON ---------------+-+ | +-> OFF --------------+ | | +-> numeric-literal --+ | +-> LINESIZE -+-> numeric-literal ----+ +-> PAGESIZE -+ | +-> NULL -+------------------+--------+ +> literal-string -+ 2 Arguments 3 ALIAS For information on SET ALIAS, type HELP SET_ALIAS. 3 ALL_CONSTRAINTS For information on SET ALL CONSTRAINTS, type HELP SET_ALL_ CONSTRAINTS. 3 ANSI For information on SET ANSI, type HELP SET_ANSI. 3 CATALOG For information on SET CATALOG, type HELP SET_CATALOG. 3 CHARACTER_LENGTH For information on SET CHARACTER LENGTH, type HELP SET_CHARACTER_ LENGTH. 3 CONNECT For information on SET CONNECT, type HELP SET_CONNECT. 3 Control For information on SET Control, type HELP SET_Control. 3 CONTINUE_CHARACTER Defines the continuation character for interactive SQL. By selecting a seldom used character the database administrator can avoid problems with the minus sign to use a continuation character in scripts. 3 CURRENCY_SIGN Specifies the currency indicator to be displayed in output. (SQL produces currency indicators in output when you specify the dollar sign ($) edit string for the column. See the DATATRIEVE HELP topic for more information on edit strings.) If you do not specify an alternate character, the default is either the dollar sign ($) or the value specified by the logical name SYS$CURRENCY. 3 DATE_date_number Specifies the display format for date values. You must enter a number for the date-number argument. This number corresponds to numbers in the date format logical names listed in tables in the OpenVMS run-time library documentation. For example, LIB$DATE_FORMAT_006 is one of the logical names in the table. The logical name specifies the format in which the eighth day of May in the year 1957 would be displayed as 8 May 57. Note that the latter part of the logical name is the number 006. If you wanted to specify the 8 May 57 format using the SET DATE FORMAT statement, you would use the numeric part of the LIB$DATE_ FORMAT_006 logical name, 6. You do not have to enter any leading zeros that the number might have. If you do not specify a date format, the default is dd-mmm-yyyy. 3 DATE_FORMAT Specifies the display format for either date values, time values, or both. You must specify a numeric argument with the DATE and TIME portions of the SET DATE FORMAT statement. This numeric argument is the same as the numeric portion of certain OpenVMS Run-Time Library formats. The formats are documented in the OpenVMS run- time library documentation. (This statement only accepts numbers that reference OpenVMS format date and time logical names; it does not support the ANSI/ISO date and time data types.) The SET DATE FORMAT DATE and SET DATE FORMAT TIME statements change only the output for the date or time formats. If you want to change the input format, use the logical name LIB$DT_INPUT_ FORMAT. You must run the command procedure SYS$MANAGER:LIB$DT_ STARTUP.COM before using any of the run-time library date-time routines for input or output formats other than the default. The LIB$DT_STARTUP.COM procedure also defines spellings for date and time elements in languages other than English. See the OpenVMS run-time library documentation for more information on LIB$DT_ INPUT_FORMAT. 3 DEFAULT_CHARACTER_SET For information on SET DEFAULT CHARACTER SET, type HELP SET_ DEFAULT_CHARACTER_SET. 3 DEFAULT_DATE_FORMAT For information on SET DEFAULT DATE FORMAT, type HELP SET_ DEFAULT_DATE_FORMAT. 3 DIALECT For information on SET DIALECT, type HELP SET_DIALECT. 3 DICTIONARY Changes your default repository path name to the path name you specify. 3 DIGIT_SEPARATOR Changes the output displaying the digit separator to the specified character. The digit separator is the symbol that separates groups of three digits in values greater than 999. For example, the comma is the digit separator in the number 1,000. (SQL produces digit separators in output when you specify the comma (,) edit string for the column. See the DATATRIEVE HELP topic for more information on edit strings.) You must enclose the digit-sep-char argument within single quotation marks. If you do not specify an alternate character, the default is either the comma (,) or the value specified by the logical name SYS$DIGIT_SEP. 3 EDIT Controls the size of the editing buffer that you create when you use the EDIT statement with a wildcard as the argument. o SET EDIT KEEP n Tells SQL to save the previous n statements. For example, assume you have specified SET EDIT KEEP 5. When you type EDIT *, SQL places the previous five statements in the editing buffer. The number you specify with SET EDIT KEEP is the maximum number of statements you can recall with the EDIT statement. The default is 20. o SET EDIT NOKEEP This statement is equivalent to SET EDIT KEEP 0. If you use this form of the statement and you type EDIT or EDIT *, your editing buffer will be empty. This form of the statement saves system resources when you are running command files rather than an interactive process. o SET EDIT PURGE This statement retains the value of the KEEP parameter but purges all previous statements. As with SET EDIT NOKEEP, if you use the SET EDIT PURGE statement and then EDIT or EDIT *, your editing buffer will be empty. Unlike the SET EDIT NOKEEP statement, however, SET EDIT PURGE causes SQL to accumulate subsequent statements to place in the editing buffer when you issue EDIT statements later in the interactive session. 3 execute-clause Syntax options: EXECUTE NOEXECUTE NO EXECUTE Instructs SQL whether to execute the data manipulation statements you issue in an interactive SQL session. You can use the NOEXECUTE option in conjunction with the SET FLAGS to examine the estimated cost and access strategy associated with a query. If you specify SET NOEXECUTE, SQL displays the access strategies without executing the query. SQL also allows you to specify NO EXECUTE (as two words); this has the same meaning as NOEXECUTE. If you do not specify EXECUTE or NOEXECUTE, the default is EXECUTE. The SET TRANSACTION statement is not executed when SET NO EXECUTE is active. Start or declare a transaction prior to using SET NO EXECUTE. 3 FLAGGER_OFF Disables all previously set flaggers indicating nonstandard syntax. This is the default. 3 FLAGGER Syntax options: FLAGGER ON FLAGGER SQL89 FLAGGER SQL92_ENTRY FLAGGER MIA Controls the output of informational messages that indicate nonstandard syntax, that is, extensions to the ANSI/ISO standard syntax or the MIA standard syntax. If you specify SET FLAGGER ON, which is the same as specifying SET FLAGGER SQL92_ENTRY ON, SQL sends you an informational message if you issue a subsequent interactive SQL statement that contains syntax that is an extension to the ANSI/ISO standard. If you specify SET FLAGGER MIA ON, SQL sends you an informational message if you issue a subsequent interactive SQL statement that contains syntax that is an extension to the MIA standard. The flaggers are independent of each other and any combination of flaggers can be set at one time. The default is FLAGGER OFF if you do not explicitly set a flagger on. 3 FLAGS For information on SET FLAGS, type HELP SET_FLAGS. 3 HOLD_CURSORS For information on SET HOLD CURSORS, type HELP SET_HOLD_CURSORS. 3 IDENTIFIER_CHARACTER_SET For information on SET IDENTIFIER CHARACTER SET, type HELP SET_ IDENTIFIER_CHARACTER_SET. 3 KEYWORD_RULES For information on SET KEYWORD RULES, type HELP SET_KEYWORD_ RULES. 3 FEEDBACK SET FEEDBACK ON is a synonym for the SQL SET DISPLAY NO ROW COUNTER statement. SQL data manipulation statements such as SELECT, DELETE, UPDATE, and INSERT will display the number of affected rows. SET FEEDBACK n sets a limit value which turns on feedback only if more than 'n' rows are displayed. SET FEEDBACK 0 is synonymous with SET FEEDBACK ON. SET FEEDBACK OFF is a synonym for the SQL SET DISPLAY ROW COUNTER statement. SQL data manipulation statements no longer display the count of affected rows. SQL> set feedback 2 SQL> select * from work_status; STATUS_CODE STATUS_NAME STATUS_TYPE 0 INACTIVE RECORD EXPIRED 1 ACTIVE FULL TIME 2 ACTIVE PART TIME 3 rows selected SQL> set feedback 4 SQL> select * from work_status; STATUS_CODE STATUS_NAME STATUS_TYPE 0 INACTIVE RECORD EXPIRED 1 ACTIVE FULL TIME 2 ACTIVE PART TIME 3 LANGUAGE Specifies the language to be used for translation of month names and abbreviations in date and time input and display. The language-name argument also determines the translation of other language-dependent text, such as the translation for the date literals YESTERDAY, TODAY, and TOMORROW. If you do not specify a language, the default is the language specified by the logical name SYS$LANGUAGE. If you require different language spellings, you must define the logical name SYS$LANGUAGES in addition to SYS$LANGUAGE. You must run the command procedure SYS$MANAGER:LIB$DT_STARTUP.COM after defining SYS$LANGUAGES. For example: $ DEFINE SYS$LANGUAGES FRENCH, GERMAN, SPANISH $ RUN SYS$MANAGER:LIB$DT_STARTUP.COM $ SHOW LOGICAL SYS$LANGUAGES "SYS$LANGUAGES" = "FRENCH" (LNM$SYSTEM_TABLE) = "GERMAN" = "SPANISH" $ SHOW LOGICAL SYS$LANGUAGE "SYS$LANGUAGE" = "ENGLISH" (LNM$SYSTEM_TABLE) If you do not define SYS$LANGUAGES, all translation routines default to English. See the OpenVMS run-time library documentation for more information on LIB$DT_STARTUP.COM. The SET LANGUAGE statement does not affect the collating sequences used for sorting and comparing data. The CREATE COLLATING SEQUENCE statement specifies alternate collating sequences. 3 LINE_LENGTH_n___LINESIZE_n Specifies an alternate line length for SQL output. You must enter a number n to designate the line length. The number n can be any number up to 65535 octets. You can use the SET LINE LENGTH (or SET LINESIZE) statement to specify an alternate width for output that you are sending to a file or to an alternate output device. 3 LITERAL_CHARACTER_SET For information on SET LITERAL CHARACTER SET, type HELP SET_ LITERAL_CHARACTER_SET. 3 LOGFILE_quoted_filespec This statement allows the executing SQL script to save output to an OpenVMS file. Output from interactive SQL will be written to the file-spec specified. If the "ECHO" logfile-option is used, in addition to writing the output to the designated file, all commands and errors generated by interactive SQL are also written to SYS$OUTPUT. If the "NOECHO" logfile-option is used, output to SYS$OUTPUT is disabled. All commands and errors generated by interactive SQL are only written to the output file. The SET LOGFILE is functionally equivalent to the SET OUTPUT statement. A SET LOGFILE command that does not specify a file is equivalent to SET NOLOGFILE. 3 NAMES For information on SET NAMES, type HELP SET_NAMES. 3 NATIONAL_CHARACTER_SET For information on SET NATIONAL CHARACTER SET, type HELP SET_ NATIONAL_CHARACTER_SET. 3 NOLOGFILE Closes the current output file specified by a prior SET LOGFILE (or SET OUTPUT command). 3 NOOUTPUT Suspends writing to the output file. 3 NOVERIFY Does not display indirect command files. The default setting is the setting currently in effect for DCL commands. If you have not explicitly changed the DCL setting to VERIFY, the default is NOVERIFY. 3 OPTIMIZATION_LEVEL For information on SET OPTIMIZATION LEVEL, type HELP SET_ OPTIMIZATION_LEVEL. 3 OUTPUT_file_spec Names the target file for output. The default file extension is .lis. If you specify OUTPUT with a file name, SQL writes its output to a log file that you specify. The log file contains both statements and results. If you issue a SET OUTPUT statement, output is also written to standard output which is usually the terminal. If you specify OUTPUT without a file name, SQL suspends writing output to a log file, if any, and writes the output to the standard output. In other words, the SET OUTPUT statement without a file name is equivalent to the SET NOOUTPUT statement. SQL displays certain items (such as the headings produced by the SHOW statement) in boldface type on your terminal screen. In log files, however, the boldface items are surrounded by escape characters. You can ignore these escape characters, edit them out of your log file, or set your terminal so that SQL does not display characters in boldface type. If you disable boldface type using the following DCL command, your log file will not contain escape characters: $ SET TERM/NOANSI_CRT 3 PAGE_LENGTH_n___PAGESIZE_n Sets the size of a page in SQL help. The following notes apply to the PAGE LENGTH (or PAGESIZE) clause: o The integer value must be a value between 10 and 32767. o SET PAGE LENGTH (or SET PAGESIZE) can be used to effectively disable the paging performed by help by setting the length to a high value such as 32000. o The page length is automatically set upon entry to interactive SQL and is based on the OpenVMS terminal setting for this session. o The SHOW DISPLAY command can be used to view the currently defined page length. 3 QUOTING_RULES For information on SET QUOTING RULES, type HELP SET_QUOTING_ RULES. 3 RADIX_POINT Changes the output displaying the radix point to the specified character. The radix point is the symbol that separates units from decimal fractions. For example, in the number 98.6, the period is the radix point. You must enclose the radix-char argument within single quotation marks. If you do not specify an alternate character, the default is either the period (.) or the value specified by the logical name SYS$RADIX_POINT. 3 SCHEMA For information on SET SCHEMA, type HELP SET_SCHEMA. 3 sql-plus-options These statements are provided for use with SQL*Plus scripts that are run against Oracle Rdb. Table 1-5 Supported SQL*Plus SET statements SQL*Plus command Equivalent Oracle Rdb statement SET ECHO ON SET VERIFY SET ECHO OFF SET NOVERIFY SET HEADING ON SET DISPLAY QUERY HEADER SET HEADING OFF SET DISPLAY NO QUERY HEADER SET FEEDBACK ON SET DISPLAY ROW COUNTER SET FEEDBACK OFF SET DISPLAY NO ROW COUNTER SET NULL SET DISPLAY DEFAULT NULL STRING SET NULL 'literal' SET DISPLAY NULL STRING 'literal' 3 TIME_time_number Specifies the display format for time values. You must enter a number for the time-number argument. This number corresponds to numbers in the time-format logical names listed in tables in the OpenVMS run-time library documentation. For example, the table contains the logical name LIB$TIME_FORMAT_020. The logical name specifies the format in which the eighth hour, fourth minute, and thirty-second second of a day would be displayed as 8 h 4 min 32 s. Note that the latter part of the logical name is the number 020. If you wanted to specify the 8 h 4 min 32 s format for the SQL SET DATE FORMAT TIME statement, you would use the numeric part of the LIB$TIME_FORMAT_020 logical name, 20. You do not have to enter any leading zeros that the number might have. If you do not specify a time format, the default is hh:mm:ss.cc. 3 TIMING The SET TIMING statement enables a single line report of used CPU and Elapsed time for each successful SQL statement or command. SQL> start transaction; SQL> set timing on; SQL> select count(*) cont> from employees cont> inner join job_history using (employee_id) cont> inner join salary_history using (employee_id) cont> inner join departments using (department_code) cont> inner join jobs using (job_code) cont> left outer join resumes using (employee_id) cont> left outer join degrees using (employee_id) cont> left outer join colleges using (college_code) cont> cont> ; 3871 1 row selected Timing: Elapsed: 0 00:00:00.82 Cpu: 0 00:00:00.16 SQL> set timing off; SQL> commit; 3 TRANSACTION For information on SET TRANSACTION, type HELP SET_TRANSACTION. 3 VERIFY Displays indirect command files at your terminal as you run them. 3 VIEW_UPDATE_RULES For information on SET VIEW UPDATE RULES, type HELP SET_VIEW_ UPDATE_RULES. 3 WARNING Syntax options: WARNING DEPRECATE WARNING NODEPRECATE Specifies whether or not interactive SQL displays diagnostic messages when you issue statements containing obsolete SQL syntax. Deprecated or obsolete syntax is syntax that was allowed in previous versions of SQL but has been changed. Oracle Rdb recommends that you avoid using such syntax because it may not be supported in future versions. By default, SQL displays a warning message after any statement containing obsolete syntax (SET WARNING DEPRECATE). If you specify SET WARNING NODEPRECATE, SQL does not display any messages about obsolete syntax. 2 Examples Example 1: Using the SET statement to set up terminal session characteristics Using the SET statement as follows, you can set up the characteristics of your terminal session: SQL> -- SQL> -- You can put the SET statements in your sqlini file, which sets up SQL> -- your SQL session. SQL> -- SQL> SET OUTPUT 'LOG.LIS' SQL> SET DICTIONARY 'CDD$TOP.DEPT3' SQL> SET EDIT KEEP 10 SQL> -- SQL> ATTACH 'ALIAS PERS FILENAME personnel'; SQL> SHOW ALIAS Alias PERS: Rdb database in file personnel SQL> EXIT In the preceding example, the statements set up the characteristics, as follows: o The SET OUTPUT statement opens a file called LOG.LIS in the current default path name. From this point on, all the input and output, including error messages, appear in this file. The following example shows what is written to the log file LOG.LIS: SET DICTIONARY 'CDD$TOP.DEPT3' SET EDIT KEEP 10 -- ATTACH 'ALIAS PERS FILENAME personnel'; SHOW ALIAS Alias PERS: Rdb database in file personnel EXIT o The SET DICTIONARY statement changes the default repository path name. o The SET EDIT KEEP statement specifies that you get the 10 previous statements in the editing buffer when you type EDIT *. o The ATTACH statement attaches to the personnel database and declares the alias PERS for that database. o The SHOW ALIAS statements tell the user which alias is declared. Example 2: SET CURRENCY SIGN and SET DIGIT SEPARATOR statements The following example uses the SET DIGIT SEPARATOR statement to show the behavior of the SET CURRENCY SIGN and SET DIGIT SEPARATOR statements when used with edit strings: SQL> -- SQL> -- This example shows the edit string 'ZZZ,ZZZ', SQL> -- which specifies the comma as the default digit separator. SQL> -- SQL> ALTER TABLE SALARY_HISTORY - cont> ALTER SALARY_AMOUNT EDIT STRING 'ZZZ,ZZZ'; SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY; SALARY_AMOUNT 26,291 51,712 26,291 50,000 . . . SQL> -- SQL> -- Now use the SET DIGIT SEPARATOR statement to specify that SQL> -- the period will be the digit separator instead of SQL> -- the comma. SQL> -- SQL> SET DIGIT SEPARATOR '.' SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY; SALARY_AMOUNT 26.291 51.712 26.291 50.000 . . . Example 3: Using the internationalization features of the SET statement The following example shows how to use the various SET statements to internationalize your applications: SQL> -- SQL> -- This first statement specifies the dollar sign SQL> -- as the currency indicator. It does this by using SQL> -- the edit string '$(9).99'. SQL> -- SQL> ALTER TABLE SALARY_HISTORY - cont> ALTER SALARY_AMOUNT EDIT STRING '$(9).99'; cont> SELECT SALARY_AMOUNT FROM SALARY_HISTORY; SALARY_AMOUNT $26291.00 $51712.00 $26291.00 $50000.00 . . . SQL> -- SQL> -- The SET CURRENCY statement now changes the currency SQL> -- indicator to the British pound sign, £. Notice SQL> -- the changed output. SQL> -- SQL> SET CURRENCY SIGN '£' SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY; SALARY_AMOUNT £26291.00 £51712.00 £26291.00 £50000.00 £11676.00 . . . SQL> -- SQL> -- The next examples show the SET DATE FORMAT statement. SQL> -- SQL> -- The SET DATE FORMAT statement will not override input SQL> -- and output formats that you have specified with an edit SQL> -- string. The following SET DATE FORMAT examples use the SQL> -- SALARY_START and SALARY_END columns. The SALARY_START SQL> -- and SALARY_END columns are defined by the domain SQL> -- DATE_DOM, which uses the edit string 'DD-MMM-YYY'. SQL> -- Thus, to test the SET DATE FORMAT statement, you must SQL> -- first remove the edit string from the DATE_DOM domain SQL> -- using the following ALTER DOMAIN statement: SQL> -- SQL> ALTER DOMAIN DATE_DOM NO EDIT STRING; SQL> -- SQL> -- The next statement inserts a row with time information. SQL> -- The subsequent SET DATE FORMAT statements will use this row: SQL> -- SQL> INSERT INTO SALARY_HISTORY cont> -- list of columns: cont> (EMPLOYEE_ID, cont> SALARY_AMOUNT, cont> SALARY_START, cont> SALARY_END) cont> VALUES cont> -- list of values: cont> ('88339', cont> '22550', cont> '14-NOV-1967 08:30:00.00', cont> '25-NOV-1988 16:30:00.00') cont> ; 1 row inserted SQL> -- SQL> -- Using the row that was just inserted, the following statement SQL> -- shows the default date and time output: SQL> -- SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY- cont> WHERE EMPLOYEE_ID = '88339'; SALARY_START SALARY_END 14-NOV-1967 08:30:00.00 25-NOV-1988 16:30:00.00 1 row selected SQL> -- SQL> -- The SET DATE FORMAT DATE statement customizes the SQL> -- output of the date format. SQL> -- SQL> -- The output will appear in the form SQL> -- 14 NOV 67, as specified by the date-number argument 6. SQL> -- SQL> SET DATE FORMAT DATE 6; SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY- cont> WHERE EMPLOYEE_ID = '88339'; SALARY_START SALARY_END 14 NOV 67 25 NOV 88 1 row selected SQL> -- SQL> -- The SET DATE FORMAT TIME statement customizes SQL> -- the output of the time format. The output will appear SQL> -- in the form 16 h 30 min 0 s, as specified by the SQL> -- time-number argument 20. SQL> -- SQL> SET DATE FORMAT TIME 20; SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY- cont> WHERE EMPLOYEE_ID = '88339'; SALARY_START SALARY_END 8 h 30 min 0 s 16 h 30 min 0 s 1 row selected SQL> -- SQL> -- Note that the previous date example has deleted SQL> -- the time output, and the previous time example has SQL> -- deleted the date output. SQL> -- SQL> -- If you want the display to continue to show SQL> -- BOTH date and time, you must specify SQL> -- both arguments with the SET DATE statement. SQL> -- SQL> SET DATE FORMAT DATE 6, TIME 20; SQL> SELECT SALARY_START, SALARY_END FROM SALARY_HISTORY- cont> WHERE EMPLOYEE_ID = '88339'; SALARY_START SALARY_END 14 NOV 67 8 h 30 min 0 s 25 NOV 88 16 h 30 min 0 s 1 row selected SQL> -- SQL> -- The next example changes the digit separator to a period and SQL> -- the radix point to a comma: SQL> -- SQL> ALTER TABLE SALARY_HISTORY - cont> ALTER SALARY_AMOUNT EDIT STRING 'ZZZ,ZZZ.ZZ'; SQL> -- SQL> SET RADIX POINT ',' SQL> SET DIGIT SEPARATOR '.' SQL> SELECT SALARY_AMOUNT FROM SALARY_HISTORY; SALARY_AMOUNT 26.291,00 51.712,00 26.291,00 50.000,00 . . . SQL> -- SQL> -- This example shows how you can use the SET LANGUAGE SQL> -- statement to change the output of dates to a particular SQL> -- language. This example shows the default English first, SQL> -- followed by French. SQL> -- SQL> -- Note that the time format is still based on SQL> -- the SET DATE FORMAT TIME statement SQL> -- previously executed in this example. SQL> -- SQL> SELECT SALARY_START FROM SALARY_HISTORY; SALARY_START 5 JUL 80 0 h 0 min 0 s 14 JAN 83 0 h 0 min 0 s 2 MAR 81 0 h 0 min 0 s 21 SEP 81 0 h 0 min 0 s 3 NOV 81 0 h 0 min 0 s 1 JUL 82 0 h 0 min 0 s 27 JAN 81 0 h 0 min 0 s 1 JUL 75 0 h 0 min 0 s 29 DEC 78 0 h 0 min 0 s 2 FEB 80 0 h 0 min 0 s 8 APR 79 0 h 0 min 0 s 19 AUG 77 0 h 0 min 0 s . . . SQL> -- SQL> SET LANGUAGE FRENCH SQL> SELECT SALARY_START FROM SALARY_HISTORY; SALARY_START 5 jul 80 0 h 0 min 0 s 14 jan 83 0 h 0 min 0 s 2 mar 81 0 h 0 min 0 s 21 sep 81 0 h 0 min 0 s 3 nov 81 0 h 0 min 0 s 1 jul 82 0 h 0 min 0 s 27 jan 81 0 h 0 min 0 s 1 jul 75 0 h 0 min 0 s 29 déc 78 0 h 0 min 0 s 2 fév 80 0 h 0 min 0 s 8 avr 79 0 h 0 min 0 s 19 aoû 77 0 h 0 min 0 s . . . SQL> -- Example 4: Using the SET statement to receive messages about syntax that contains extensions to the ANSI/ISO SQL or MIA standards This example shows the output when flagging is turned on, first for SQL92_ENTRY and then for MIA. SQL> -- Flagging is off by default. When you enter a statement that SQL> -- uses the data type VARCHAR, SQL does not issue a message. SQL> -- SQL> SHOW FLAGGER MODE; The flagger mode is OFF SQL> CREATE TABLE TEST1 (TEXT_COL VARCHAR (100)); SQL> -- SQL> -- When you set the flagger to SQL92_ENTRY, SQL generates an SQL> -- error message because VARCHAR is an extension to the standard. SQL> -- SQL> SET FLAGGER SQL92_ENTRY ON SQL> CREATE TABLE TEST2 (TEXT_COL VARCHAR (100)); %SQL-I-NONSTADTP, Nonstandard data type SQL> -- SQL> -- With the flagger set to SQL92_ENTRY, SQL does not generate an SQL> -- error message for the data type CHAR because it is an ANSI/ISO SQL> -- standard data type. SQL> -- SQL> CREATE TABLE TEST3 (TEXT_COL CHAR); SQL> -- SQL> -- However, when you set the flagger to MIA, SQL generates two SQL> -- error messages because data definition is not part of the MIA SQL> -- standard. The first error message is caused by the CREATE SQL> -- keyword; the second is caused by trying to create a table. SQL> -- SQL> -- (Note that the SET FLAGGER statement itself is nonstandard.) SQL> -- SQL> SET FLAGGER MIA ON %SQL-I-NONSTASYN, Nonstandard syntax SQL> CREATE TABLE TEST3 (TEXT_COL CHAR); %SQL-I-NONSTASYN, Nonstandard syntax %SQL-I-NONSTASYN, Nonstandard syntax SQL> Example 5: Using the SET statement to check for obsolete syntax This example shows the output from an obsolete SQL statement when the user specifies WARNING DEPRECATE, and the output from the same statement when the user specifies WARNING NODEPRECATE. SQL> -- SQL> -- By default, SQL sends warning messages when you use obsolete syntax. SQL> -- SQL> DECLARE SCHEMA FILENAME personnel; %SQL-I-DEPR_FEATURE, Deprecated Feature: SCHEMA (meaning ALIAS) SQL> DISCONNECT ALL; SQL> -- SQL> -- When you specify SET WARNING NODEPRECATE, SQL does not display warning SQL> -- messages. SQL> -- SQL> SET WARNING NODEPRECATE; SQL> DECLARE SCHEMA FILENAME personnel; SQL> DISCONNECT ALL; Example 6: Setting page length The following example uses the SET PAGE LENGTH command to change the pagination length of HELP. SQL> set page length 40; SQL> show display Output of the query header is enabled Output of the row counter is enabled Output using edit strings is enabled Page length is set to 40 lines Line length is set to 80 bytes Display NULL values using "NULL" Example 7: Saving the output from a script The following example shows the use of SET LOGFILE to save the output from a script without echoing the results. 1. The script being executed. set verify; start transaction read only; set logfile (noecho) 'saved_date.log'; select rdb$flags from rdb$database; set nologfile; show alias; rollback; 2. The output as seen during the Interactive SQL session. SQL> start transaction read only; SQL> SQL> set logfile (noecho) 'saved_date.log'; SQL> SQL> show alias; Default alias: Oracle Rdb database in file SQL$DATABASE SQL> rollback; 3. The output saved in the log file. SQL> SQL> select rdb$flags from rdb$database; RDB$FLAGS 0 1 row selected SQL> SQL> set nologfile;