MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for January, 2009

Add Image to Cell Comment

with one comment

How you add an image to a comment isn’t exactly what I’d call easy but it is straightforward. One of my students noted that a tip in Microsoft Office Excel 2007 Bible didn’t provide the magic to insert an image.

I’d have to say that it did but it didn’t. Basically, the general idea is there but a step of magic requires something special. You need to place your cursor on the highlighted edge of an editable comment before right clicking to launch the context menu. If your cursor is on the text portion of the comment, the context menu operates differently. The Format Comment … menu choice launches a different set of options dependent on your location in an editable comment.

Hoping to help my students and others learn the magic, here are some steps.

Written by maclochlainn

January 29th, 2009 at 1:04 pm

Delete an Access Denied File

with 7 comments

When my old Toshiba Tecra died because of a fried mother board, I salvaged my hard disk. I snagged a 2.5″ USB hard disk case but didn’t have time to clean it up. While cleaning up the old Windows XP files tonight, I ran into the following error:

E:\WINDOWS\Temp\hsperfdata_SYSTEM>attrib +R *.*
Access denied - E:\WINDOWS\Temp\hsperfdata_SYSTEM\2792

I thought the attrib utility might help me fix the error but it didn’t. I checked the file Property dialog for a quick fix and didn’t find one.

A little poking around on the web and I decided that disabling simple file sharing may provide additional options. You disable it by navigating to Start, My Computer, where you click on the Tools option in the menu and chose the Folder Options … to get the following dialog box. You clicked the View tab. In the tab, you scroll down in the Advanced setttings section until you see the Use simple file sharing (Recommened) option. You click the check box to turn off simple file sharing. Click the Apply button to make the change.

folderoptions

Open a separate Explorer session, and navigate to the directory where you’ve encountered a file that denies you access to delete it. Right click on the file. You should see two tabs, one labeled General and the other Security. If you see only the General tab, you may have forgot to check the Apply button earlier.

2792_properties2

Click the Security tab to see the following dialog. Click the Allow check box in the Permissions for Everyone section and then click the Apply button. You should now be able to delete the file in Windows Explorer.

Written by maclochlainn

January 27th, 2009 at 12:21 am

Posted in Microsoft XP

Reserved words in Oracle

with 17 comments

While discussing metadata today, or more specifically key and reserved words in the Oracle catalog, I found it helpful to yank a script that I wrote for Appendix I of the Oracle Database 11g PL/SQL Programming book (cited to make the acquisition editor happy) book. I thought it might be helpful if it was on the blog, so here it is. It finds all the key or reserved words, and prints them in an alphabetized list.

You should note that you must change the base type of the collection from a VARCHAR2(2000) to a CLOB datatype because the list of key words become quite long in Oracle Database 11g. There are two places where you’ll need to update the script.

/*
 * reserved_key_words.sql
 * Appendix I, Oracle Database 11g PL/SQL Programming
 * by Michael McLaughlin
 *
 * ALERTS:
 *
 * This script segments reserved and key words.
 */
 
-- Unremark for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
  -- Define and declare collection.
  TYPE alpha_key IS TABLE OF CHARACTER;  
  code ALPHA_KEY := alpha_key('A','B','C','D','E','F','G','H','I','J'
                             ,'K','L','M','N','O','P','Q','R','S','T'
                             ,'U','V','W','X','Y','Z');
 
  -- Define a single character indexed collection, change to a CLOB for Oracle 11g.  
  TYPE list IS TABLE OF VARCHAR2(2000)
    INDEX BY VARCHAR2(1);
 
  -- Define two collections.    
  reserved_word LIST;
  key_word      LIST;
 
  -- Define cursor.
  CURSOR c IS
    SELECT   keyword
    ,        reserved
    ,        res_type
    ,        res_attr
    ,        res_semi
    FROM     v$reserved_words
    ORDER BY keyword;
 
  FUNCTION format_list (list_in LIST) RETURN BOOLEAN IS
 
    -- Declare control variables.
    CURRENT VARCHAR2(1);
    element VARCHAR2(2000); -- Change to a CLOB for Oracle 11g
    status BOOLEAN := TRUE;
 
  BEGIN
    -- Read through an alphabetically indexed collection.
    FOR i IN 1..list_in.COUNT LOOP
      IF i = 1 THEN
        CURRENT := list_in.FIRST;
        element := list_in(CURRENT);
      ELSE
        IF list_in.NEXT(CURRENT) IS NOT NULL THEN
          CURRENT := list_in.NEXT(CURRENT);
          element := list_in(CURRENT);
        END IF;
      END IF;
      DBMS_OUTPUT.put_line('['||CURRENT||'] ['||element||']');
    END LOOP;
    RETURN status;
  END format_list;
 
BEGIN
 
  -- Initialize reserved and key word collections.
  FOR i IN 1..code.LAST LOOP
    FOR j IN c LOOP
      IF code(i) = UPPER(SUBSTR(j.keyword,1,1))
      AND (j.reserved = 'Y' OR j.res_type = 'Y' OR j.res_attr = 'Y' OR j.res_semi = 'Y') THEN
        IF reserved_word.EXISTS(code(i)) THEN
          reserved_word(code(i)) := reserved_word(code(i)) || ', ' || j.keyword;
        ELSE
          reserved_word(code(i)) := j.keyword;
        END IF;
      ELSIF code(i) = UPPER(SUBSTR(j.keyword,1,1)) AND j.reserved = 'N' THEN
        IF key_word.EXISTS(code(i)) THEN
          key_word(code(i)) := key_word(code(i)) || ', ' || j.keyword;
        ELSE
          key_word(code(i)) := j.keyword;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
 
  -- Print both lists.
  IF format_list(reserved_word) AND format_list(key_word) THEN
    NULL;
  END IF;
 
END;
/

I hoped this would help a few folks but in my discussion with Karsten, it seemed to cause confusion. It’s interesting to note that the HELP utility returns a difference set of values, and begs the question what role the V$RESERVED_WORDS has (it’s been around since 1998 from sql.bsq).

You can print the PL/SQL and SQL reserved lists from SQL*Plus by using the following command, which is case insensitive:

SQL> HELP RESERVED WORDS

It produces the following output:

 RESERVED WORDS (PL/SQL)
 -----------------------
 
 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").
 
 An asterisk (*) indicates words are also SQL Reserved Words.
 
 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION
 
 
 RESERVED WORDS (SQL)
 --------------------
 
 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".
 
 An asterisk (*) indicates words are also ANSI Reserved Words.
 
 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".
 
 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*

Alternative, you can qualify only a SQL or PL/SQL reserved word list by using one of these:

SQL

SQL> HELP RESERVED WORDS (SQL)

PL/SQL

SQL> HELP RESERVED WORDS (PL/SQL)

You can also use this to find other possible help topics by using this command:

SQL> HELP INDEX

It provides you with the following list in Oracle Database 11g, Release 1:

Enter Help [topic] for help.
 
 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW

There is no help option for KEY WORDS through Oracle Database 11g, Release 1.

If you only want the words from the database V$RESERVED_WORDS view for Oracle Database 11g, Release 1, they’re shown below by request of somebody who follows the blog. As qualified in the comments, I couldn’t find the reason for the difference between the HELP utility and the list. Are the words omitted in the view an oversight? Are the incremental words potentially new key or reserved words? Why there’s no help for KEY WORDS? These are open questions for somebody to enlighten us about in a comment.

Letter Reserved Word
A ACCESS, ADD, ALL, ALTER, AND, ANY, AS, ASC, AUDIT
B BETWEEN, BY
C CHAR, CHECK, CLUSTER, COLUMN, COLUMN_VALUE, COMMENT, COMPRESS, CONNECT, CREATE, CURRENT
D DATE, DECIMAL, DEFAULT, DELETE, DESC, DISTINCT, DROP
E ELSE, EXCLUSIVE, EXISTS
F FILE, FLOAT, FOR, FROM
G GRANT, GROUP
H HAVING
I IDENTIFIED, IMMEDIATE, IN, INCREMENT, INDEX, INITIAL, INSERT, INTEGER, INTERSECT, INTO, IS
L LEVEL, LIKE, LOCK, LONG
M MAXEXTENTS, MINUS, MLSLABEL, MODE, MODIFY
N NESTED_TABLE_ID, NOAUDIT, NOCOMPRESS, NOT, NOWAIT, NULL, NUMBER
O OF, OFFLINE, ON, ONLINE, OPTION, OR, ORDER
P PCTFREE, PRIOR, PRIVILEGES, PUBLIC
R RAW, RENAME, RESOURCE, REVOKE, ROW, ROWID, ROWNUM, ROWS
S SELECT, SESSION, SET, SHARE, SIZE, SMALLINT, START, SUCCESSFUL, SYNONYM, SYSDATE
T TABLE, THEN, TO, TRIGGER
U UID, UNION, UNIQUE, UPDATE, USER
V VALIDATE, VALUES, VARCHAR, VARCHAR2, VIEW
W WHENEVER, WHERE, WITH

The key word list changes more frequently than the reserved word list. It also changes between point releases, like 11gR1 to 11gR2. You should probably run the script to verify any changes as you move to new releases. The following is the list generated by querying the database, you should note that the Oracle Database 11g Release 1 is missing known key or reserved words. The separation into the two tables is based on the seeded data in the database, as qualified in the script.

Letter Key Word
A A, ABORT, ABS, ACCESSED, ACCOUNT, ACOS, ACTIVATE, ACTIVE_COMPONENT, ACTIVE_FUNCTION, ACTIVE_TAG, ADD_MONTHS, ADJ_DATE, ADMIN, ADMINISTER, ADMINISTRATOR, ADVISE, ADVISOR, AFTER, ALIAS, ALLOCATE, ALLOW, ALL_ROWS, ALWAYS, ANALYZE, ANCILLARY, AND_EQUAL, ANTIJOIN, ANYSCHEMA, APPEND, APPENDCHILDXML, APPLY, ARCHIVE, ARCHIVELOG, ARRAY, ASCII, ASCIISTR, ASIN, ASSEMBLY, ASSOCIATE, ASYNC, AT, ATAN, ATAN2, ATTRIBUTE, ATTRIBUTES, AUTHENTICATED, AUTHENTICATION, AUTHID, AUTHORIZATION, AUTO, AUTOALLOCATE, AUTOEXTEND, AUTOMATIC, AVAILABILITY, AVG
B BACKUP, BASICFILE, BATCH, BECOME, BEFORE, BEGIN, BEGIN_OUTLINE_DATA, BEHALF, BFILE, BFILENAME, BIGFILE, BINARY, BINARY_DOUBLE, BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN, BINARY_FLOAT, BINARY_FLOAT_INFINITY, BINARY_FLOAT_NAN, BINDING, BIN_TO_NUM, BITAND, BITMAP, BITMAPS, BITMAP_TREE, BITS, BLOB, BLOCK, BLOCKS, BLOCKSIZE, BLOCK_RANGE, BODY, BOTH, BOUND, BRANCH, BROADCAST, BUFFER, BUFFER_CACHE, BUFFER_POOL, BUILD, BULK, BYPASS_RECURSIVE_CHECK, BYPASS_UJVC, BYTE
C CACHE, CACHE_CB, CACHE_INSTANCES, CACHE_TEMP_TABLE, CALL, CANCEL, CARDINALITY, CASCADE, CASE, CAST, CATEGORY, CEIL, CERTIFICATE, CFILE, CHAINED, CHANGE, CHARACTER, CHARTOROWID, CHAR_CS, CHECKPOINT, CHECK_ACL_REWRITE, CHILD, CHOOSE, CHR, CHUNK, CLASS, CLEAR, CLOB, CLONE, CLOSE, CLOSE_CACHED_OPEN_CURSORS, CLUSTERING_FACTOR, CLUSTER_ID, CLUSTER_PROBABILITY, CLUSTER_SET, COALESCE, COARSE, COLD, COLLECT, COLUMNS, COLUMN_STATS, COMMIT, COMMITTED, COMPACT, COMPATIBILITY, COMPILE, COMPLETE, COMPOSE, COMPOSITE, COMPOSITE_LIMIT, COMPOUND, COMPUTE, CONCAT, CONFORMING, CONNECT_BY_COMBINE_SW, CONNECT_BY_COST_BASED, CONNECT_BY_FILTERING, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, CONNECT_BY_ROOT, CONNECT_TIME, CONSIDER, CONSISTENT, CONST, CONSTANT, CONSTRAINT, CONSTRAINTS, CONTAINER, CONTENT, CONTENTS, CONTEXT, CONTINUE, CONTROLFILE, CONVERT, CORR, CORRUPTION, CORRUPT_XID, CORRUPT_XID_ALL, CORR_K, CORR_S, COS, COSH, COST, COST_XML_QUERY_REWRITE, COUNT, COVAR_POP, COVAR_SAMP, CO_AUTH_IND, CPU_COSTING, CPU_PER_CALL, CPU_PER_SESSION, CRASH, CREATE_STORED_OUTLINES, CROSS, CROSSEDITION, CSCONVERT, CUBE, CUBE_GB, CUME_DIST, CUME_DISTM, CURRENTV, CURRENT_DATE, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CURSOR_SHARING_EXACT,
CURSOR_SPECIFIC_SEGMENT, CV, CYCLE
D D, DANGLING, DATA, DATABASE, DATABASE_DEFAULT, DATAFILE, DATAFILES, DATAOBJNO, DATAOBJ_TO_PARTITION, DATE_MODE, DAY, DBA, DBA_RECYCLEBIN, DBMS_STATS, DBTIMEZONE, DB_ROLE_CHANGE, DB_VERSION, DDL, DEALLOCATE, DEBUG, DEBUGGER, DEC, DECLARE, DECODE, DECOMPOSE, DECR, DECREMENT, DECRYPT, DEDUPLICATE, DEFAULTS, DEFERRABLE, DEFERRED, DEFINED, DEFINER, DEGREE, DELAY, DELETEXML, DEMAND, DENSE_RANK, DENSE_RANKM, DEPENDENT, DEQUEUE, DEREF, DEREF_NO_REWRITE, DETACHED, DETERMINES, DICTIONARY, DIMENSION, DIRECTORY, DIRECT_LOAD, DISABLE, DISABLE_PRESET, DISABLE_RPKE, DISALLOW, DISASSOCIATE, DISCONNECT, DISK, DISKGROUP, DISKS, DISMOUNT, DISTINGUISHED, DISTRIBUTED, DML, DML_UPDATE, DOCUMENT, DOMAIN_INDEX_FILTER, DOMAIN_INDEX_NO_SORT, DOMAIN_INDEX_SORT, DOUBLE, DOWNGRADE, DRIVING_SITE, DUMP, DYNAMIC, DYNAMIC_SAMPLING, DYNAMIC_SAMPLING_EST_CDN
E E, EACH, EDITION, EDITIONING, EDITIONS, ELEMENT, ELIMINATE_JOIN, ELIMINATE_OBY, ELIMINATE_OUTER_JOIN, EMPTY, EMPTY_BLOB, EMPTY_CLOB, ENABLE, ENABLE_PRESET, ENCODING, ENCRYPT, ENCRYPTION, END, END_OUTLINE_DATA, ENFORCE, ENFORCED, ENQUEUE, ENTERPRISE, ENTITYESCAPING, ENTRY, ERROR, ERRORS, ERROR_ARGUMENT, ERROR_ON_OVERLAP_TIME, ESCAPE, ESTIMATE, EVALNAME, EVALUATION, EVENTS, EVERY, EXCEPT, EXCEPTIONS, EXCHANGE, EXCLUDE, EXCLUDING, EXECUTE, EXEMPT, EXISTSNODE, EXP, EXPAND_GSET_TO_UNION, EXPIRE, EXPLAIN, EXPLOSION, EXPORT, EXPR_CORR_CHECK, EXTENDS, EXTENT, EXTENTS, EXTERNAL, EXTERNALLY, EXTRA, EXTRACT, EXTRACTVALUE
F FACILITY, FACT, FAILED, FAILED_LOGIN_ATTEMPTS, FAILGROUP, FALSE, FAST, FBTSCAN, FEATURE_ID, FEATURE_SET, FEATURE_VALUE, FILESYSTEM_LIKE_LOGGING, FILTER, FINAL, FINE, FINISH, FIRST, FIRSTM, FIRST_ROWS, FIRST_VALUE, FLAGGER, FLASHBACK, FLOB, FLOOR, FLUSH, FOLDER, FOLLOWING, FOLLOWS, FORCE, FORCE_XML_QUERY_REWRITE, FOREIGN, FOREVER, FORWARD, FREELIST, FREELISTS, FREEPOOLS, FRESH, FROM_TZ, FULL, FUNCTION, FUNCTIONS
G G, GATHER_PLAN_STATISTICS, GBY_CONC_ROLLUP, GBY_PUSHDOWN, GENERATED, GLOBAL, GLOBALLY, GLOBAL_NAME, GLOBAL_TOPIC_ENABLED, GREATEST, GROUPING, GROUPING_ID, GROUPS, GROUP_BY, GROUP_ID, GUARANTEE, GUARANTEED,
GUARD
H H, HASH, HASHKEYS, HASH_AJ, HASH_SJ, HEADER, HEAP, HELP, HEXTORAW, HEXTOREF, HIDDEN, HIDE, HIERARCHY, HIGH, HINTSET_BEGIN, HINTSET_END,
HOT, HOUR, HWM_BROKERED
I ID, IDENTIFIER, IDENTITY, IDGENERATORS, IDLE_TIME, IF, IGNORE, IGNORE_OPTIM_EMBEDDED_HINTS, IGNORE_WHERE_CLAUSE, IMPACT, IMPORT, INCLUDE, INCLUDE_VERSION, INCLUDING, INCR, INCREMENTAL, INDENT, INDEXED, INDEXES, INDEXTYPE, INDEXTYPES, INDEX_ASC, INDEX_COMBINE, INDEX_DESC, INDEX_FFS, INDEX_FILTER, INDEX_JOIN, INDEX_ROWS, INDEX_RRS, INDEX_RS, INDEX_RS_ASC, INDEX_RS_DESC, INDEX_SCAN, INDEX_SKIP_SCAN, INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC, INDEX_STATS, INDICATOR, INFINITE, INFORMATIONAL, INHERITED, INITCAP, INITIALIZED, INITIALLY, INITRANS, INLINE, INLINE_XMLTYPE_NT, INNER, INSERTCHILDXML, INSERTCHILDXMLAFTER, INSERTCHILDXMLBEFORE, INSERTXMLBEFORE, INSTANCE, INSTANCES, INSTANTIABLE, INSTANTLY, INSTEAD, INSTR, INSTR2, INSTR4, INSTRB, INSTRC, INT, INTERMEDIATE, INTERNAL_CONVERT, INTERNAL_USE, INTERPRETED, INTERVAL, INVALIDATE, INVISIBLE, IN_MEMORY_METADATA, IN_XQUERY, ISOLATION, ISOLATION_LEVEL, ITERATE, ITERATION_NUMBER
J JAVA, JOB, JOIN
K K, KEEP, KEEP_DUPLICATES, KERBEROS, KEY, KEYS, KEYSIZE,
KEY_LENGTH, KILL
L LAG, LAST, LAST_DAY, LAST_VALUE, LATERAL, LAYER, LDAP_REGISTRATION, LDAP_REGISTRATION_ENABLED, LDAP_REG_SYNC_INTERVAL, LEAD, LEADING, LEAST, LEFT, LENGTH, LENGTH2, LENGTH4, LENGTHB, LENGTHC, LESS, LEVELS, LIBRARY, LIFE, LIFETIME, LIKE2, LIKE4, LIKEC, LIKE_EXPAND, LIMIT, LINK, LIST, LN, LNNVL, LOB, LOBNVL, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOCAL_INDEXES, LOCATION, LOCATOR, LOCKED, LOG, LOGFILE, LOGGING, LOGICAL, LOGICAL_READS_PER_CALL, LOGICAL_READS_PER_SESSION, LOGOFF, LOGON, LOW, LOWER, LPAD, LTRIM
M M, MAIN, MAKE_REF, MANAGE, MANAGED, MANAGEMENT, MANUAL, MAPPING, MASTER, MATCHED, MATERIALIZE, MATERIALIZED, MAX, MAXARCHLOGS, MAXDATAFILES, MAXIMIZE, MAXINSTANCES, MAXLOGFILES, MAXLOGHISTORY, MAXLOGMEMBERS, MAXSIZE, MAXTRANS, MAXVALUE, MEASURE, MEASURES, MEDIAN, MEDIUM, MEMBER, MEMORY, MERGE, MERGE$ACTIONS, MERGE_AJ, MERGE_CONST_ON, MERGE_SJ, METHOD, MIGRATE, MIGRATION, MIN, MINEXTENTS, MINIMIZE, MINIMUM, MINING, MINUS_NULL, MINUTE, MINVALUE, MIRROR, MIRRORCOLD, MIRRORHOT, MOD, MODEL, MODEL_COMPILE_SUBQUERY, MODEL_DONTVERIFY_UNIQUENESS, MODEL_DYNAMIC_SUBQUERY, MODEL_MIN_ANALYSIS, MODEL_NO_ANALYSIS, MODEL_PBY, MODEL_PUSH_REF, MONITOR, MONITORING, MONTH, MONTHS_BETWEEN, MOUNT, MOUNTPATH, MOVE, MOVEMENT, MULTISET, MV_MERGE
N NAME, NAMED, NAMESPACE, NAN, NANVL, NATIONAL, NATIVE, NATIVE_FULL_OUTER_JOIN, NATURAL, NAV, NCHAR, NCHAR_CS, NCHR, NCLOB, NEEDED, NESTED, NESTED_TABLE_FAST_INSERT, NESTED_TABLE_GET_REFS, NESTED_TABLE_SET_REFS, NESTED_TABLE_SET_SETID, NETWORK, NEVER, NEW, NEW_TIME, NEXT, NEXT_DAY, NLJ_BATCHING, NLJ_PREFETCH, NLSSORT, NLS_CALENDAR, NLS_CHARACTERSET, NLS_CHARSET_DECL_LEN, NLS_CHARSET_ID, NLS_CHARSET_NAME, NLS_COMP, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_INITCAP, NLS_ISO_CURRENCY, NLS_LANG, NLS_LANGUAGE, NLS_LENGTH_SEMANTICS, NLS_LOWER, NLS_NCHAR_CONV_EXCP, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_SPECIAL_CHARS, NLS_TERRITORY, NLS_UPPER, NL_AJ, NL_SJ, NO, NOAPPEND, NOARCHIVELOG, NOCACHE, NOCPU_COSTING, NOCYCLE, NODELAY, NOENTITYESCAPING, NOFORCE, NOGUARANTEE, NOLOCAL, NOLOGGING, NOMAPPING, NOMAXVALUE, NOMINIMIZE, NOMINVALUE, NOMONITORING, NONE, NONSCHEMA, NOORDER, NOOVERRIDE, NOPARALLEL, NOPARALLEL_INDEX, NORELY, NOREPAIR, NORESETLOGS, NOREVERSE, NOREWRITE, NORMAL, NOROWDEPENDENCIES, NOSCHEMACHECK, NOSEGMENT, NOSORT, NOSTRICT, NOSWITCH, NOTHING, NOTIFICATION, NOVALIDATE, NO_ACCESS, NO_BASETABLE_MULTIMV_REWRITE, NO_BUFFER, NO_CARTESIAN, NO_CHECK_ACL_REWRITE, NO_CONNECT_BY_COMBINE_SW, NO_CONNECT_BY_COST_BASED, NO_CONNECT_BY_FILTERING, NO_COST_XML_QUERY_REWRITE, NO_CPU_COSTING, NO_DOMAIN_INDEX_FILTER, NO_ELIMINATE_JOIN, NO_ELIMINATE_OBY, NO_ELIMINATE_OUTER_JOIN, NO_EXPAND, NO_EXPAND_GSET_TO_UNION, NO_FACT, NO_FILTERING, NO_GBY_PUSHDOWN, NO_INDEX, NO_INDEX_FFS, NO_INDEX_SS, NO_LOAD, NO_MERGE, NO_MODEL_PUSH_REF, NO_MONITOR,NO_MONITORING, NO_MULTIMV_REWRITE, NO_NATIVE_FULL_OUTER_JOIN, NO_NLJ_BATCHING,NO_NLJ_PREFETCH, NO_ORDER_ROLLUPS, NO_OUTER_JOIN_TO_INNER, NO_PARALLEL,NO_PARALLEL_INDEX, NO_PARTIAL_COMMIT, NO_PLACE_GROUP_BY, NO_PQ_MAP,NO_PRUNE_GSETS, NO_PULL_PRED, NO_PUSH_PRED, NO_PUSH_SUBQ, NO_PX_JOIN_FILTER,NO_QKN_BUFF, NO_QUERY_TRANSFORMATION, NO_REF_CASCADE, NO_RESULT_CACHE, NO_REWRITE, NO_SEMIJOIN, NO_SET_TO_JOIN, NO_SQL_TUNE, NO_STAR_TRANSFORMATION, NO_STATS_GSETS, NO_SUBQUERY_PRUNING, NO_SWAP_JOIN_INPUTS, NO_TEMP_TABLE, NO_UNNEST, NO_USE_HASH, NO_USE_HASH_AGGREGATION, NO_USE_MERGE, NO_USE_NL, NO_XMLINDEX_REWRITE, NO_XMLINDEX_REWRITE_IN_SELECT, NO_XML_DML_REWRITE, NO_XML_QUERY_REWRITE, NTILE, NULLIF, NULLS, NUMERIC, NUMTODSINTERVAL, NUMTOYMINTERVAL, NUM_INDEX_KEYS, NVARCHAR2, NVL, NVL2
O OBJECT, OBJECTTOXML, OBJNO, OBJNO_REUSE, OCCURENCES, OFF, OID, OIDINDEX, OLAP, OLD, OLD_PUSH_PRED, ONLY, OPAQUE, OPAQUE_TRANSFORM, OPAQUE_XCANONICAL, OPCODE, OPEN, OPERATIONS, OPERATOR, OPTIMAL, OPTIMIZER_FEATURES_ENABLE, OPTIMIZER_GOAL, OPT_ESTIMATE, OPT_PARAM, ORADEBUG, ORA_BRANCH, ORA_HASH, ORA_ROWSCN, ORA_ROWVERSION, ORA_TABVERSION, ORDERED, ORDERED_PREDICATES, ORDINALITY, ORGANIZATION, OR_EXPAND, OTHER, OUTER, OUTER_JOIN_TO_INNER, OUTLINE, OUTLINE_LEAF, OUT_OF_LINE, OVER, OVERFLOW, OVERFLOW_NOMOVE, OVERLAPS, OWN, OWNER, OWNERSHIP
P P, PACKAGE, PACKAGES, PARALLEL, PARALLEL_INDEX, PARAM, PARAMETERS, PARENT, PARITY, PARTIALLY, PARTITION, PARTITIONS, PARTITION_HASH, PARTITION_LIST, PARTITION_RANGE, PASSING, PASSWORD, PASSWORD_GRACE_TIME, PASSWORD_LIFE_TIME, PASSWORD_LOCK_TIME, PASSWORD_REUSE_MAX, PASSWORD_REUSE_TIME, PASSWORD_VERIFY_FUNCTION, PATH, PATHS, PBL_HS_BEGIN, PBL_HS_END, PCTINCREASE, PCTTHRESHOLD, PCTUSED, PCTVERSION, PENDING, PERCENT, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, PERCENT_RANKM, PERFORMANCE, PERMANENT, PERMISSION, PFILE, PHYSICAL, PIVOT, PIV_GB, PIV_SSF, PLACE_GROUP_BY, PLAN, PLSCOPE_SETTINGS, PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, POINT, POLICY, POST_TRANSACTION, POWER, POWERMULTISET, POWERMULTISET_BY_CARDINALITY, PQ_DISTRIBUTE, PQ_MAP, PQ_NOMAP, PREBUILT, PRECEDES, PRECEDING, PRECISION, PRECOMPUTE_SUBQUERY, PREDICTION, PREDICTION_BOUNDS, PREDICTION_COST, PREDICTION_DETAILS, PREDICTION_PROBABILITY, PREDICTION_SET, PREPARE, PRESENT, PRESENTNNV, PRESENTV, PRESERVE, PRESERVE_OID, PREVIOUS, PRIMARY, PRIVATE, PRIVATE_SGA, PRIVILEGE, PROCEDURAL, PROCEDURE, PROCESS, PROFILE, PROGRAM, PROJECT, PROPAGATE, PROTECTED, PROTECTION, PULL_PRED, PURGE, PUSH_PRED, PUSH_SUBQ, PX_GRANULE, PX_JOIN_FILTER
Q QB_NAME, QUERY, QUERY_BLOCK, QUEUE, QUEUE_CURR, QUEUE_ROWP, QUIESCE, QUOTA
R RANDOM, RANGE, RANK, RANKM, RAPIDLY, RATIO_TO_REPORT, RAWTOHEX, RAWTONHEX, RBA, RBO_OUTLINE, READ, READS, REAL, REBALANCE, REBUILD, RECORDS_PER_BLOCK, RECOVER, RECOVERABLE, RECOVERY, RECYCLE, RECYCLEBIN, REDO, REDUCED, REDUNDANCY, REF, REFERENCE, REFERENCED, REFERENCES, REFERENCING, REFRESH, REFTOHEX, REF_CASCADE_CURSOR, REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGISTER, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, REJECT, REKEY, RELATIONAL, RELY, REMAINDER, REMOTE_MAPPED, REMOVE, REPAIR, REPLACE, REPLICATION, REQUIRED, RESET, RESETLOGS, RESIZE, RESOLVE, RESOLVER, RESTORE, RESTORE_AS_INTERVALS, RESTRICT, RESTRICTED, RESTRICT_ALL_REF_CONS, RESULT_CACHE, RESUMABLE, RESUME, RETENTION, RETURN, RETURNING, REUSE, REVERSE, REWRITE, REWRITE_OR_ERROR, RIGHT, ROLE, ROLES, ROLLBACK, ROLLING, ROLLUP, ROUND, ROWDEPENDENCIES, ROWIDTOCHAR, ROWIDTONCHAR, ROW_LENGTH, ROW_NUMBER, RPAD, RTRIM, RULE, RULES
S SALT, SAMPLE, SAVEPOINT, SAVE_AS_INTERVALS, SB4, SCALE, SCALE_ROWS, SCAN, SCAN_INSTANCES, SCHEDULER, SCHEMA, SCHEMACHECK, SCN, SCN_ASCENDING, SCOPE, SD_ALL, SD_INHIBIT, SD_SHOW, SECOND, SECUREFILE, SECURITY, SEED, SEGMENT, SEG_BLOCK, SEG_FILE, SELECTIVITY, SEMIJOIN, SEMIJOIN_DRIVER, SEQUENCE, SEQUENCED, SEQUENTIAL, SERIALIZABLE, SERVERERROR, SESSIONS_PER_USER, SESSIONTIMEZONE, SESSIONTZNAME, SESSION_CACHED_CURSORS, SETS, SETTINGS, SET_TO_JOIN, SEVERE, SHARED, SHARED_POOL, SHOW, SHRINK, SHUTDOWN, SIBLINGS, SID, SIGN, SIGNAL_COMPONENT, SIGNAL_FUNCTION, SIMPLE, SIN, SINGLE, SINGLETASK, SINH, SKIP, SKIP_EXT_OPTIMIZER, SKIP_UNQ_UNUSABLE_IDX, SKIP_UNUSABLE_INDEXES, SMALLFILE, SNAPSHOT, SOME, SORT, SOUNDEX, SOURCE, SPACE, SPECIFICATION, SPFILE, SPLIT, SPREADSHEET, SQL, SQLLDR, SQL_TRACE, SQRT, STALE, STANDALONE, STANDBY, STAR, STARTUP, STAR_TRANSFORMATION, STATEMENT_ID, STATIC, STATISTICS, STATS_BINOMIAL_TEST, STATS_CROSSTAB, STATS_F_TEST, STATS_KS_TEST, STATS_MODE, STATS_MW_TEST, STATS_ONE_WAY_ANOVA, STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED, STATS_WSR_TEST, STDDEV, STDDEV_POP, STDDEV_SAMP, STOP, STORAGE, STORE, STREAMS, STRICT, STRING, STRIP, STRIPE_COLUMNS, STRIPE_WIDTH, STRUCTURE, SUBMULTISET, SUBPARTITION, SUBPARTITIONS, SUBPARTITION_REL, SUBQUERIES, SUBQUERY_PRUNING, SUBSTITUTABLE, SUBSTR, SUBSTR2, SUBSTR4, SUBSTRB, SUBSTRC, SUM, SUMMARY, SUPPLEMENTAL, SUSPEND, SWAP_JOIN_INPUTS, SWITCH, SWITCHOVER, SYNC, SYSASM, SYSAUX, SYSDBA, SYSOPER, SYSTEM, SYSTEM_DEFINED, SYSTIMESTAMP, SYS_AUDIT, SYS_CHECKACL, SYS_CONNECT_BY_PATH, SYS_CONTEXT, SYS_DBURIGEN, SYS_DL_CURSOR, SYS_DM_RXFORM_CHR, SYS_DM_RXFORM_NUM, SYS_DOM_COMPARE, SYS_ET_BFILE_TO_RAW, SYS_ET_BLOB_TO_IMAGE, SYS_ET_IMAGE_TO_BLOB, SYS_ET_RAW_TO_BFILE, SYS_EXTRACT_UTC, SYS_FBT_INSDEL, SYS_FILTER_ACLS, SYS_GET_ACLIDS, SYS_GET_PRIVILEGES, SYS_GUID, SYS_MAKEXML, SYS_MAKE_XMLNODEID, SYS_MKXMLATTR, SYS_OPTXICMP, SYS_OPTXQCASTASNQ, SYS_OP_ADT2BIN, SYS_OP_ADTCONS, SYS_OP_ALSCRVAL, SYS_OP_ATG, SYS_OP_BIN2ADT, SYS_OP_BITVEC, SYS_OP_BL2R, SYS_OP_BLOOM_FILTER, SYS_OP_C2C, SYS_OP_CAST, SYS_OP_CEG, SYS_OP_CL2C, SYS_OP_COMBINED_HASH, SYS_OP_COMP, SYS_OP_CONVERT, SYS_OP_COUNTCHG, SYS_OP_CSR, SYS_OP_CSX_PATCH, SYS_OP_DECOMP, SYS_OP_DESCEND, SYS_OP_DISTINCT, SYS_OP_DRA, SYS_OP_DUMP, SYS_OP_ENFORCE_NOT_NULL$, SYS_OP_EXTRACT, SYS_OP_GROUPING, SYS_OP_GUID, SYS_OP_IIX, SYS_OP_ITR, SYS_OP_LBID, SYS_OP_LOBLOC2BLOB, SYS_OP_LOBLOC2CLOB, SYS_OP_LOBLOC2ID, SYS_OP_LOBLOC2NCLOB, SYS_OP_LOBLOC2TYP, SYS_OP_LSVI, SYS_OP_LVL, SYS_OP_MAKEOID, SYS_OP_MAP_NONNULL, SYS_OP_MSR, SYS_OP_NICOMBINE, SYS_OP_NIEXTRACT, SYS_OP_NII, SYS_OP_NIX, SYS_OP_NOEXPAND, SYS_OP_NTCIMG$, SYS_OP_NUMTORAW, SYS_OP_OIDVALUE, SYS_OP_OPNSIZE, SYS_OP_PAR, SYS_OP_PARGID, SYS_OP_PARGID_1, SYS_OP_PAR_1, SYS_OP_PIVOT, SYS_OP_R2O, SYS_OP_RAWTONUM, SYS_OP_RDTM, SYS_OP_REF, SYS_OP_RMTD, SYS_OP_ROWIDTOOBJ, SYS_OP_RPB, SYS_OP_TOSETID, SYS_OP_TPR, SYS_OP_TRTB, SYS_OP_UNDESCEND, SYS_OP_VECAND, SYS_OP_VECBIT, SYS_OP_VECOR, SYS_OP_VECXOR, SYS_OP_VERSION, SYS_OP_VREF, SYS_OP_VVD, SYS_OP_XPTHATG, SYS_OP_XPTHIDX, SYS_OP_XPTHOP, SYS_OP_XTXT2SQLT, SYS_ORDERKEY_DEPTH, SYS_ORDERKEY_MAXCHILD, SYS_ORDERKEY_PARENT, SYS_PARALLEL_TXN, SYS_PATHID_IS_ATTR, SYS_PATHID_IS_NMSPC, SYS_PATHID_LASTNAME, SYS_PATHID_LASTNMSPC, SYS_PATH_REVERSE, SYS_PXQEXTRACT, SYS_RID_ORDER, SYS_ROW_DELTA, SYS_SYNRCIREDO, SYS_TYPEID, SYS_UMAKEXML, SYS_XMLCONTAINS, SYS_XMLCONV, SYS_XMLEXNSURI, SYS_XMLGEN, SYS_XMLI_LOC_ISNODE, SYS_XMLI_LOC_ISTEXT, SYS_XMLLOCATOR_GETSVAL, SYS_XMLNODEID, SYS_XMLNODEID_GETCID, SYS_XMLNODEID_GETLOCATOR, SYS_XMLNODEID_GETOKEY, SYS_XMLNODEID_GETPATHID, SYS_XMLNODEID_GETRID, SYS_XMLNODEID_GETSVAL, SYS_XMLNODEID_GETTID, SYS_XMLTRANSLATE, SYS_XMLTYPE2SQL, SYS_XMLT_2_SC, SYS_XQBASEURI, SYS_XQCASTABLEERRH, SYS_XQCODEP2STR, SYS_XQCODEPEQ, SYS_XQCON2SEQ, SYS_XQCONCAT, SYS_XQDFLTCOLATION, SYS_XQDOCURI, SYS_XQED4URI, SYS_XQENDSWITH, SYS_XQERR, SYS_XQERRH, SYS_XQESHTMLURI, SYS_XQEXSTWRP, SYS_XQEXTRACT, SYS_XQEXTRREF, SYS_XQEXVAL, SYS_XQFNBOOL, SYS_XQFNCMP, SYS_XQFNDATIM, SYS_XQFNLNAME, SYS_XQFNNM, SYS_XQFNNSURI, SYS_XQFNPREDTRUTH, SYS_XQFNQNM, SYS_XQFNROOT, SYS_XQFUNCR, SYS_XQGETCONTENT, SYS_XQINDXOF, SYS_XQINSPFX, SYS_XQIRI2URI, SYS_XQLLNMFRMQNM, SYS_XQMKNODEREF, SYS_XQNILLED, SYS_XQNODENAME, SYS_XQNORMSPACE, SYS_XQNORMUCODE, SYS_XQNSP4PFX, SYS_XQNSPFRMQNM, SYS_XQPFXFRMQNM, SYS_XQPOLYABS, SYS_XQPOLYADD, SYS_XQPOLYCEL, SYS_XQPOLYCST, SYS_XQPOLYCSTBL, SYS_XQPOLYDIV, SYS_XQPOLYFLR, SYS_XQPOLYMOD, SYS_XQPOLYMUL, SYS_XQPOLYRND, SYS_XQPOLYSQRT, SYS_XQPOLYSUB, SYS_XQPOLYUMUS, SYS_XQPOLYUPLS, SYS_XQPOLYVEQ, SYS_XQPOLYVGE, SYS_XQPOLYVGT, SYS_XQPOLYVLE, SYS_XQPOLYVLT, SYS_XQPOLYVNE, SYS_XQREF2VAL, SYS_XQRESVURI, SYS_XQRNDHALF2EVN, SYS_XQRSLVQNM, SYS_XQRYENVPGET, SYS_XQRYVARGET, SYS_XQRYWRP, SYS_XQSEQ2CON, SYS_XQSEQ2CON4XC, SYS_XQSEQDEEPEQ, SYS_XQSEQINSB, SYS_XQSEQRM, SYS_XQSEQRVS, SYS_XQSEQSUB, SYS_XQSEQTYPMATCH, SYS_XQSTARTSWITH, SYS_XQSTATBURI, SYS_XQSTR2CODEP, SYS_XQSTRJOIN, SYS_XQSUBSTRAFT, SYS_XQSUBSTRBEF, SYS_XQTREATAS, SYS_XQ_ASQLCNV, SYS_XQ_ATOMCNVCHK, SYS_XQ_NRNG, SYS_XQ_PKSQL2XML, SYS_XQ_UPKXML2SQL
T T, TABLES, TABLESPACE, TABLESPACE_NO, TABLE_STATS, TABNO, TAN, TANH, TBL$OR$IDX$PART$NUM, TEMPFILE, TEMPLATE, TEMPORARY, TEMP_TABLE, TEST, THAN, THE, THREAD, THROUGH, TIME, TIMEOUT, TIMES, TIMESTAMP, TIMEZONE_ABBR, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_OFFSET, TIMEZONE_REGION, TIME_ZONE, TIV_GB, TIV_SSF, TOPLEVEL, TO_BINARY_DOUBLE, TO_BINARY_FLOAT, TO_BLOB, TO_CHAR, TO_CLOB, TO_DATE, TO_DSINTERVAL, TO_LOB, TO_MULTI_BYTE, TO_NCHAR, TO_NCLOB, TO_NUMBER, TO_SINGLE_BYTE, TO_TIME, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_TIME_TZ, TO_YMINTERVAL, TRACE, TRACING, TRACKING, TRAILING, TRANSACTION, TRANSITION, TRANSITIONAL, TRANSLATE, TREAT, TRIGGERS, TRIM, TRUE, TRUNC, TRUNCATE, TRUSTED, TUNING, TX, TYPE, TYPES, TZ_OFFSET
U U, UB2, UBA, UNARCHIVED, UNBOUND, UNBOUNDED, UNDER, UNDO, UNDROP, UNIFORM, UNISTR, UNLIMITED, UNLOCK, UNNEST, UNPACKED, UNPIVOT, UNPROTECTED, UNQUIESCE, UNRECOVERABLE, UNRESTRICTED, UNTIL, UNUSABLE, UNUSED, UPDATABLE, UPDATED, UPDATEXML, UPD_INDEXES, UPD_JOININDEX, UPGRADE, UPPER, UPSERT, UROWID, USAGE, USE, USERENV, USERGROUP, USERS, USER_DEFINED, USER_RECYCLEBIN, USE_ANTI, USE_CONCAT, USE_HASH, USE_HASH_AGGREGATION, USE_INVISIBLE_INDEXES, USE_MERGE, USE_MERGE_CARTESIAN, USE_NL, USE_NL_WITH_INDEX, USE_PRIVATE_OUTLINES, USE_SEMI, USE_STORED_OUTLINES,
USE_TTT_FOR_GSETS, USE_WEAK_NAME_RESL, USING
V VALIDATION, VALUE, VARIANCE, VARRAY, VARYING, VAR_POP, VAR_SAMP, VECTOR_READ, VECTOR_READ_TRACE, VERIFY, VERSION, VERSIONING, VERSIONS, VERSIONS_ENDSCN, VERSIONS_ENDTIME, VERSIONS_OPERATION, VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_XID, VIRTUAL, VISIBLE, VOLUME, VSIZE
W WAIT, WALLET, WELLFORMED, WHEN, WHITESPACE, WIDTH_BUCKET, WITHIN, WITHOUT, WORK, WRAPPED, WRITE
X XID, XML, XMLATTRIBUTES, XMLCAST, XMLCDATA, XMLCOLATTVAL, XMLCOMMENT, XMLCONCAT, XMLDIFF, XMLELEMENT, XMLEXISTS, XMLEXISTS2, XMLFOREST, XMLINDEX_REWRITE, XMLINDEX_REWRITE_IN_SELECT, XMLISNODE, XMLISVALID, XMLNAMESPACES, XMLPARSE, XMLPATCH, XMLPI, XMLQUERY, XMLROOT, XMLSCHEMA, XMLSERIALIZE, XMLTABLE, XMLTOOBJECT, XMLTRANSFORM, XMLTRANSFORMBLOB, XMLTYPE, XML_DML_RWT_STMT, XPATHTABLE, XS_SYS_CONTEXT, X_DYN_PRUNE
Y YEAR, YES
Z ZONE

Written by maclochlainn

January 22nd, 2009 at 7:55 pm

Posted in Oracle,pl/sql,sql

Overriding SQL*Plus ed

with 6 comments

I was looking for a cool post to point my students to about overriding the ed tool in SQL*Plus but couldn’t find one. A number of posts showed how to set vi as the default editor in Linux or Unix but none showed how to replace Microsoft Notepad with something else. Instructions for both operating environments are here.

Linux or Unix:

This is simple because all you need to do is open a terminal session and type the following command:

# which -a vi

vi is typically a symbolic link to /usr/bin/vi, and it points to /usr/bin/vim in many cases, like Linux or Mac OS X. You can now add that to your SQL*Plus session interactively by typing:

SQL> define _editor=vi

You can set this in your Oracle Database 10g or 11g home, or in the Oracle Database Instant Client. It is found in the $ORACLE_HOME/sqlplus/admin/glogin.sql file, and example is noted at the end of this blog.

Windows:

This is actually quite easy but different releases of Windows provide different behaviors. Some of those behaviors provide alternatives that don’t work in all Windows releases. The off-beat approaches let you launch the alternate editor but they don’t always edit the active buffer. The ones I’ve chosen to show you should work in all Windows releases, but let me know if they don’t in your environment.

1. Install the editor(s) you want to use. I’ve installed and tested GVIM (a vi editor) and Notepad++ on Windows XP and Vista with Oracle Database 10g and 11g.

2. Add the directory path to these products to your system path. This takes four steps. First, you open your System Properties dialog box. Click the Environment Variables button to set an environment variable.

systemproperties

In the Environment Variables dialog box, you should select the PATH variable from the System variables list. Click the Edit button to change the PATH variable.

systemenvironmentvariables

Add the following in the Edit System Variable dialog box. You should note that you use a semi-colon to separate path elements in Windows (not a colon like Linux or Unix). After you add the editor path, click the OK button. You can append any number of editors if you’ve got a bunch that you like to use.

systempathedit

You should now click the OK button on the Environment Variables and System Properties dialog boxes in turn. Now you can open any command prompt and type the executable name to run the program, like gvim72.exe.

3. The Oracle Database 10g and 11g expect the executable for the default or override ed (editor) utility exists in the %SystemRoot%\System32 directory, which is the C:\WINDOWS\System32 directory. Copy only the executable, like GVIM.EXE, to the C:\WINDOWS\System32 directory.

4. You can now interactively type the following each time you log into the database at the SQL command prompt:

SQL> define _editor=gvim

Alternatively, you can place that command in the following file:

%ORACLE_HOME%\sqlplus\admin\glogin.sql

It is run each time you login to the database. The file would look like this if you wanted to run gvim as your override editor, which means when you type ed to change the SQL buffer file. The SQL buffer file contains the last SQL statement executed. That file is named afiedt.buf, which stands for A File Eidtor Buffer (debunked by Niall in the comment, the AFI stands for AFI Advanced Friendly Interface). As pointed to by Laurent’s comment, you should change the file extension to take advantage of GeSHi (Generic Syntax Highlighter) for your code.

5. After you’ve done all that. If you’d like to include your USER name and TNS alias, you can run the following command interactively or put it in your glogin.sql script. Caution, this only works for Oracle 10g forward.

SQL> SET sqlprompt _user"@"_connect_identifier>

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

STUDENT@orcl>

The rules for setting the SQLPROMPT aren’t intuitive. You can only use one set of double quotes. In the preceding example, the quotes surround the @ symbol between two SQL*Plus macros, which are the _USER and _CONNECT_IDENTIFIER. There’s no magic in that symbol and you can replace it with another. When you want text before, in between, and after a macro or two, you surround the whole thing with double quotes, and allow a white space before macros or use single quotes around string literals.

The white space example works like this:

SQL> SET sqlprompt "SQL: _user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL: STUDENT at orcl>

The nested single quotes example works like this:

SQL> SET sqlprompt "'SQL:'_user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL:STUDENT at orcl>

The single quotes around the SQL: lets you remove the space between the colon and user name. I’ve never seen a way to control case for the macro return values but there may be one. Perhaps somebody will add a comment about it. If you put more than two double quotes in the descriptor passed to SQLPROMPT environment variable, SQL*Plus raises an SP2-0735 error.

6. Here is a sample of the glogin.sql file:

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
-- Define the override or default editor.
define _editor=gvim
-- Set the edit file to allow GeSHI highlighting.
SET editfile=afiedt.sql
-- Set the SQL*Plus prompt to show user and TNS Alias.
SET sqlprompt "'SQL:'_user at _connect_identifier>"

Hope this helps a few folks stuck with Windows as the operating system for Oracle.

Written by maclochlainn

January 19th, 2009 at 1:08 am