Archive for the ‘pl/sql’ Category
Easier way than NDS
Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.
The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.
Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.
CREATE OR REPLACE FUNCTION proper_item_type ( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS -- Define a weakly typed system reference cursor. item_cursor SYS_REFCURSOR; -- Define a target variable for the query result. item_type_out VARCHAR2(30); -- Create NDS statement, with a bind or placeholder variable. stmt VARCHAR2(2000) := 'SELECT type_name ' || 'FROM item_type ' || 'WHERE UPPER(type_name) = UPPER(:type_name_in)'; BEGIN -- Open the cursor and dynamically assign the function actual parameter. OPEN item_cursor FOR stmt USING item_type_in; -- Fetch the first row return and return the value. FETCH item_cursor INTO item_type_out; -- CLose the cursor. CLOSE item_cursor; -- Return the value. RETURN item_type_out; END; / |
This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.
CREATE OR REPLACE FUNCTION proper_item_type ( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS -- Define a dynamic cursor. CURSOR c (item_type_name VARCHAR2) IS SELECT type_name FROM item_type WHERE UPPER(type_name) = UPPER(item_type_name); BEGIN -- Open the cursor and dynamically assign the function actual parameter. FOR i IN c(item_type_in) LOOP RETURN i.type_name; END LOOP; END; / |
An even more primitive approach relies on implicit assignment, like the following:
CREATE OR REPLACE FUNCTION proper_item_type ( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS BEGIN -- Open the cursor and rely on implicit assignment within the cursor. FOR i IN (SELECT type_name FROM item_type WHERE UPPER(type_name) = UPPER(item_type_in)) LOOP RETURN i.type_name; END LOOP; END; / |
I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.
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'); |
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.
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 |
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
Quick review of PL/SQL formal parameter modes
My students wanted a supplement on how variable modes work in PL/SQL, so I figured it would fit nicely in a quick blog entry. If you’re interested, read on …
PL/SQL supports three patterns of variable modes in functions and procedures. The easiest supports a pass-by-value function or procedure, and it is the IN mode of operation. The other two are related but different, and support a pass-by-reference function or procedure. The differences between a function and procedure are straightforward: (1) A function can return a value as an output, which is known as an expression; (2) A function can be used as a right operand; (3) A procedure can’t return a value because it more or less returns a void (borrowing from the lexicon of C, C++, C#, or Java and many other languages), (4) A procedure can be used as a statement by itself in a PL/SQL program while a function can’t. The variables you define in a function or procedure signature (or prototype) are the formal paramters, while the values or variables assigned when calling a function or procedure are the actual parameters.
IN mode:
An IN mode variable is really a copy of the variable, but you can ask to pass a reference. PL/SQL typically obliges when using the IN mode of operation. The following defines a pass-by-value PL/SQL function (other than the return type, you could do the same in a procedure too):
You can test the values of the actual parameter before and after the function call while also testing it inside the function. You can also assign a literal number or string as the actual parameter because the IN mode only requires a value because it discard the variable reference and value when it completes.
There is an exception data type for this IN mode operation, and it is the PL/SQL system reference data type (more on this type can be found in the following post). A PL/SQL reference cursor can only be passed when it is already opened, and it actually passes a reference to the cursor work area in the Private Global Area (PGA).
You can’t assign a value to a formal parameter inside a function when the variable has the default (or IN) mode of operation. Any attempt to do so raises a PLS-00363 with a warning that expression (formal parameter) can’t be used as an assignment target. A test of the function follows:
This seems to be the preferred way to implement functions for beginning programmers.
IN OUT mode:
An IN OUT mode variable is typically a reference to a copy of the actual variable for a couple reasons. If the function or procedure fails the original values are unchanged (this is a departure from the behavior of collections passed as actual parameters in Java). You can assign values to the formal parameter at runtime when using an IN OUT mode variable.
At the conclusion of the function or procedure the internal variable’s reference is passed to the calling program scope and replaces the original reference to the actual parameter. Here’s an example of an IN OUT mode variable in a function.
As you can see the external value is changed inside the function and at completion of the function the external variable passed as an actual parameter is changed:
This seems to be used more frequently in procedures than functions in PL/SQL. However, you can use the approach in either. I’d recommend it for functions that you call through the OCI or Java.
OUT mode:
An OUT mode variable is very much like an IN OUT mode variable with one exception. There is no initial value in it. You must assign a value to an OUT mode variable because it has no value otherwise. If the function or procedure fails, the external variable is unchanged. At the successful conclusion of the function or procedure, the reference for the internal variable replaces the reference to the external scoped variable.
The following shows you the test case:
The OUT mode also has an exception, which relates to CLOB and BLOB datatypes. You can find more about large objects in this presentation made at the Utah Oracle Users Group – Oracle LOBs.
This should be pretty straightforward but if you have suggestions to improve it let me know.
The FOR UPDATE and WHERE CURRENT OF statements
The FOR UPDATE clause has been part of Oracle SQL for years. As part of SQLJ, they introduced the WHERE CURRENT OF clause in Oracle 10g. The WHERE CURRENT OF clause allows you to join on ROWIDs the cursor and an UPDATE or DELETE statement in a loop.
For example, you can do something like this:
Alternatively, you can wrap this in a bulk operation (that doesn’t do anything really because it’s the same table), like this:
I have to ask why you don’t simply write a correlated UPDATE or DELETE statement, like this:
UPDATE item i1 SET last_updated_by = 3 , last_update_date = TRUNC(SYSDATE) WHERE EXISTS (SELECT NULL FROM item i2 WHERE i2.item_id BETWEEN 1031 AND 1040 AND i1.ROWID = i2.ROWID); |
There hasn’t been time to run any tuning diagnostics on this but perhaps it should go into the queue of what-ifs. Any thoughts are welcome …
Subscribe via RSS