Archive for the ‘sql’ Category
Basic SQL Query
Somebody suggested that I post a simple quick view of how a basic query works. The following illustrates how the FROM is read first. Aliases assigned in the FROM clause, like the i, replace the full name of the table or view. Column references don’t require prepending with the table or view alias but doing so adds clarity in the query. You do need prepend table aliases or names when two or more columns returned by the query have the same names. This happens when you’re joining two or more tables because the SQL parser finds that they’re ambiguously defined otherwise.
While the column aliases use the optional AS keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH statement – blogged on it here.
Selection comes in two phases, identifying the sources or tables in the FROM clause, and then filtering the sources based on comparisons in the WHERE clause. Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM clause as part of a ON or USING subclause when queries use key words like JOIN et cetera. You find join statements in the WHERE clause when the queries list tables as comma separated elements in a FROM clause.
Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT clause.
While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.
Aggregation typically happens after selection but before projection. While columns in the SELECT clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY clause.
Sorting by a column is done through the ORDER BY clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY clause.
Oracle ENUM Framework
I went back and edited that post about MySQL nuances not in Oracle from last week about the MySQL ENUM data type. You can find a framework suggestion that lets you not have to change existing string conditioning application code during a migration from MySQL to Oracle.
You’ll see this in the middle of the page, just click it to unfold the details. Yes, JQuery has arrived on my blog.
As with everything else on this blog, let me know if you see an opportunity for improvement.
MySQL nuances not in Oracle
I use Alan Beaulieu’s book in my entry level SQL class because it is simple and short. The problem is that we focus on Oracle products as an Oracle Academic Partner. Three items that come up frequently are the MySQL ENUM and SET data types, and how to perform multiple row inserts.
MySQL’s ENUM data type
The ENUM data type lets you enter a list of possible string values. It acts like a check constraint in an Oracle database. As such, it restricts what you enter in the column to a value found in the list, or a NULL value provided you’ve not added a not null column constraint.
You could define a table that contains video store item types, like the following:
CREATE TABLE item_type ( id INT , text ENUM ('VHS','DVD','Blu-ray') ); |
You should note that the case sensitivity for display is set by how you define them in the ENUM data type when you create the table. They may be entered in mixed, lowercase, or uppercase in an INSERT statement because they’re actually stored as a number. The numbers correlate to their order in an internal list of values, and that list start with the number one.
An alternative syntax with a VARCHAR data type is:
CREATE TABLE item_type ( id INT , TYPE VARCHAR(20) CHECK ( TYPE IN ('DVD','VHS','Blu-ray') )); |
In MySQL, this syntax is exactly equivalent in behavior to an ENUM data type. The same isn’t true in an Oracle database. While the equivalent check constraint statement is simple, it isn’t alike behavior. The comparable statement for Oracle names the constraint. You can’t name constraints in MySQL.
CREATE TABLE item_type ( id NUMBER , TYPE VARCHAR2(20) CONSTRAINT it_type CHECK ( TYPE IN ('DVD','VHS','Blu-ray') )); |
The difference between an ENUM type and a check constraint in MySQL is that the data may not display in a uniform way. MySQL check constraints don’t impose case sensitive validation on input strings, and they also store the data however it is input. Whereas, Oracle does impose case sensitive check constraints and rejects non-conforming strings.
Both databases support single and multiple row INSERT statements. The syntax for single row INSERT statements is very much alike. Multiple row INSERT statement syntax differs between the implementations, as shown later in the blog post.
Oracle Framework to mimic ENUM data type ↓
This framework is predicated on two assumptions. You are migrating from a MySQL to Oracle database, and your external code doesn’t enforce case sensitivity on inputs because it once relied on the MySQL ENUM data type. Oracle check constraints enforce case sensitive inputs, which may be a solution in itself during migration. At least, it is a solution when you handle case sensitive errors gracefully in your external code. Assuming you want an alternative to changing your external application code and you want to avoid throwing errors, here are the steps to do so.
Create testing environment
Create a testing environment that uses a VARCHAR2 column without a database level constraint in conjunction with a database trigger. You can do this because a before insert or update trigger will enforce the equivalent of a database constraint. The trigger lets you define the behavior of the constraint.
Here’s the setup code, which relies on a small item_type lookup table:
Setup code ↓
-- Conditionally drop table and sequence before attempting to create them. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME IN (UPPER('item_type') ,UPPER('item'))) LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN (UPPER('item_type_s1') ,UPPER('item_s1'))) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create item_type table, index on the natural key, and sequence; then seed data. CREATE TABLE item_type ( item_type_id NUMBER CONSTRAINT pk_item_type PRIMARY KEY , TABLE_NAME VARCHAR2(30) CONSTRAINT nn_item_type_1 NOT NULL , column_name VARCHAR2(30) CONSTRAINT nn_item_type_2 NOT NULL , type_name VARCHAR2(30) CONSTRAINT nn_item_type_3 NOT NULL , code VARCHAR2(5) , meaning VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL); CREATE UNIQUE INDEX item_type_u1 ON item_type(TABLE_NAME,column_name,type_name); CREATE TABLE item ( item_id NUMBER CONSTRAINT pk_item PRIMARY KEY , item_type NUMBER CONSTRAINT nn_item_1 NOT NULL , item_title VARCHAR2(60) CONSTRAINT nn_item_2 NOT NULL , item_rating_id NUMBER CONSTRAINT nn_item_3 NOT NULL); CREATE SEQUENCE item_type_s1 START WITH 1001; CREATE SEQUENCE item_s1 START WITH 1001; INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','DVD',NULL,'DVD'); INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','VHS',NULL,'VHS'); INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY'); |
As you may have noticed, the seeded data contains case sensitive strings. The absence of a database-level constraint on a column of a table requires a database trigger. The trigger acts as an alternative to a check constraint and lets you enforce a filtered insertion rule.
Oracle lets you write logic directly into a database trigger, but it is better to write the logic into a stored function or procedure and call it from the trigger’s execution block.
Create a stored function
The stored function takes a string, looks it up in the item_type table, and returns the correct case-sensitive string. The logic is implemented in this function:
CREATE OR REPLACE FUNCTION proper_item_type ( item_type VARCHAR2 ) RETURN VARCHAR2 IS CURSOR c (type_name_in VARCHAR2) IS SELECT type_name FROM item_type WHERE UPPER(type_name) = UPPER(type_name_in); BEGIN FOR i IN c (item_type) LOOP RETURN i.type_name; END LOOP; END; / |
Create a database trigger
After you’ve defined the logic for the trigger, you need to define the database trigger. The following creates a before insert or update database trigger on the item table. The trigger ensures that INSERT statements to the item table store case sensitive copies from the list of possible values found in the item_type lookup table.
CREATE OR REPLACE TRIGGER proper_item_type_t1 BEFORE INSERT OR UPDATE ON item FOR EACH ROW BEGIN :NEW.item_type := proper_item_type(:new_item_type); END; / |
The trigger takes a non-compliant or compliant string and always return a compliant string before anything is put in the item table.
Test the framework
You can test the framework by inserting an uppercase string BLU-RAY and evaluating the actual results. The following INSERT statement tests the outcome of inserting a non-compliant string.
INSERT INTO item VALUES (item_s1.nextval, 'BLU-RAY', 'Star Wars I', 1001); |
You’ll see that a compliant string was actually inserted because the trigger called the function, and the function changed the value from a non-compliant to compliant string. This query let’s you see the result:
SELECT * FROM item; |
It inserted Blu-ray in the item_type column.
MySQL’s SET data type
The SET data type lets you enter a list of possible string values but differs from the ENUM data type only in how the values are indexed. Values in the SET are stored as bit values. You may store up to 64 members in a set.
You create a table the same way as you did with ENUM data type, except that you use the SET key word.
CREATE TABLE item_type ( id INT , TYPE SET ('DVD','VHS','Blu-ray')); |
All insert patterns shown for the ENUM data type work with the SET data type.
Multiple row INSERT statements
The most common multiple row insert statement in MySQL is a comma delimited set of parenthetical values. Each parenthetical set of values maps to a row in the INSERT statement.
INSERT INTO item_type VALUES ( 1, 'DVD' ), ( 2, 'Blu-ray'), ( 3, 'VHS' ); |
You can’t use the foregoing syntax in Oracle. Oralce only supports a multiple row insert with a subquery.
You can write a subquery in MySQL that fabricates an aggregate table by using numeric and string literals, like the following statement.
INSERT INTO item_type SELECT 1, 'DVD' UNION ALL SELECT 2, 'Blu-ray' UNION ALL SELECT 3, 'VHS' ; |
Another way to perform multiple row inserts in MySQL is to select from an existing table or filtered result set, like this:
INSERT INTO item_type SELECT some_int, some_varchar FROM some_table; |
You raise an error when you use parentheses in a multiple INSERT statement in MySQL. It’s important to note because the Oracle allows you to write the statement either way.
If you’re coming from MySQL to Oracle, you should note that you may use parentheses to enclose a subquery in Oracle. The Oracle SQL parser works with or without them in an INSERT statement.
The MySQL subquery example is the closest to the Oracle syntax for a multiple row insert. The difference is that Oracle give you the option to enclose a subquery in parentheses when using them inside INSERT statements. Oracle requires that you use the FROM dual clause. I actually wish Oracle would adopt the shorter syntax and maintain backward compatibility to the dual pseudo table.
INSERT INTO item_type ( SELECT 1, 'DVD' FROM dual UNION ALL SELECT 2, 'Blu-ray' FROM dual UNION ALL SELECT 3, 'VHS' FROM dual); |
Another way to perform a multiple row insert is to select from an existing table, like this:
INSERT INTO item_type (SELECT some_int, some_varchar FROM some_table); |
Other resources:
You should check Oracle’s document that qualifies differences between MySQL and Oracle. It is the Oracle® Database SQL Developer Supplementary Information for MySQL Migrations.
Data normalization notes
I’m trying to eliminate the textbook from my database class, and wrote a data normalization blog page for my students; however, it is only done through 3NF so far. The post tries to remove the discrete math and provide clear examples. The students are checking it out for opportunities to make it clearer, if you’ve thoughts let me know.
Also, I’ve substantially update my initial blog page on SQL joins, and will put one out on set operators probably this Monday. If you’ve time to review it, I would appreciate suggestions for improvement.
The length of these blog pages has compelled me to move to a more friendly editor. I’ve opted for Blogo as my Mac blog editor. It comes from Brainjuice. My son’s experience with their customer service and product convinced me. You can find his blog post discussing customer service here.
Blogo prompts you, but beat the rush. Go to your Site Admin, select Writing under Settings in WordPress, and enable the remote XML-RPC publishing protocol. More or less like this prompt.
Notes on SQL Joins
It’s almost funny reflecting on when I first learned how to write SQL in 1985. It was using SQL/DS 1.1 (Structured Query Language/Data System) on a VMS/CMS operating system at the IBM center in the Los Angeles, California. That was in the Crocker Bank Building when I worked for First Interstate Bank. The building is still there and so are databases but both banks are long gone.
After 24 years of SQL a quick primer on joins seems like a good idea. I have a simple example that should help illustrate various joins and SQL semantics. You’ll find it as a blog page here …
Reserved words in Oracle
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 |
Overriding SQL*Plus ed
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.
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.
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.
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.
When is a nested table column null?
Somebody posted a comment that asked how you could test whether a nested table column is null. The NVL didn’t work because it doesn’t support the SQL data type.
I added how you could do it to an earlier blog post on joining non-collection row data to collection columns. The added entry shows you how to check whether a nested table column is null, empty, or populated. It’s a quick example that uses the same scalar collection of strings previously covered in that example.
Unsupported use of WITH clause
While helping out in the conversion of some MySQL SQL, I tried the WITH clause inside a subquery for a multiple row INSERT statement. I got a nasty surprise, it’s not supported. I got the following error:
FROM dual ) * ERROR at line 16: ORA-32034: unsupported USE OF WITH clause |
Consistent with how I’m updating old blog pages and posts, you can find the full explanation in the updated blog post on the WITH clause. As Dominic commented, I got the syntax wrong and he’s got it for a single row subquery in the comment too. The blog page is updated with both a single and multiple row subquery.
Migration was straightforward but …
I attempted to have a forward from the old WordPress.com site. That was a painful mistake! It took down both blogs with a circular referencing that was humorous I suppose. The DNS entries appear to all be corrected and forwarding straightened out. I apologize for any inconvenience.
As I update or expand entries, I’m entering a note in the old blog posts. I think the code segments are much improved, you can find SQL, PL/SQL, PHP, and Java examples in the following blog entries that are now perfect for cutting and pasting. Also, as noted the iPhone view is GREAT!
1. SQL example
2. PL/SQL example
3. PHP example
4. Java example
5. Shell example, DOS and Bash
Subscribe via RSS