MacLochlainns Weblog

CREATE OR REPLACE VIEW schema_types AS
SELECT   ut.type_name AS type_name
,        uta.attr_no AS position_id
,        uta.attr_name AS attribute_name
,        DECODE(uta.attr_type_name
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,uta.attr_type_name
,          'BINARY_DOUBLE',uta.attr_type_name
,          'BLOB'         ,uta.attr_type_name
,          'CLOB'         ,uta.attr_type_name
,          'CHAR'         ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'DATE'         ,uta.attr_type_name
,          'FLOAT'        ,uta.attr_type_name
,          'LONG RAW'     ,uta.attr_type_name
,          'NCHAR'        ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NVARCHAR2'    ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NUMBER'       ,DECODE(NVL(uta.precision||uta.scale,0)
,          0,uta.attr_type_name
,        DECODE(NVL(uta.scale,0),0
,        uta.attr_type_name||'('||uta.precision||')'
,        uta.attr_type_name||'('||uta.precision||','|| uta.scale||')'))
,          'RAW'          ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR'      ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR2'     ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'TIMESTAMP'     , uta.attr_type_name,uta.attr_type_name) AS attr_type_name
FROM     user_types ut, user_type_attrs uta
WHERE    ut.type_name = uta.type_name
ORDER BY ut.type_name, uta.attr_no;

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
TTITLE OFF
 
SET ECHO ON
SET FEEDBACK OFF
SET NULL ''
SET PAGESIZE 999
SET PAUSE OFF
SET TERM ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
 
ACCEPT INPUT PROMPT "Enter type name: "
 
SET HEADING ON
TTITLE LEFT o1 SKIP 1 -
'--------------------------------------------------------' SKIP 1
 
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON c1 SKIP PAGE
 
COL c1 NEW_VALUE o1 NOPRINT
COL c2 FORMAT 999 HEADING "ID"
COL c3 FORMAT A32 HEADING "Attribute Name"
COL c4 FORMAT A33 HEADING "Attribute Type"
 
SELECT   st.type_name c1
,        st.position_id c2
,        st.attribute_name c3
,        st.attr_type_name c4
FROM     schema_types st
WHERE    st.type_name LIKE UPPER('&input')||'%'
ORDER BY st.type_name
,        st.position_id;

COMMON_LOOKUP_OBJECT
--------------------------------------------------------
  ID Attribute Name                   Attribute TYPE
---- -------------------------------- ------------------
   1 COMMON_LOOKUP_ID                 NUMBER
   2 COMMON_LOOKUP_TYPE               VARCHAR2(30)
   3 COMMON_LOOKUP_MEANING            VARCHAR2(255)

<?php
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE'))");
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

http://mclaughlin11g/GetCommonLookup.php?table=ITEM&column=ITEM_TYPE

<?php
  // Declare input variables.
  (isset($_GET['table'])) ? $table = $_GET['table'] : $table = "ITEM";
  (isset($_GET['column'])) ? $column = $_GET['column'] : $column = 'ITEM_TYPE';
 
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    // Don't use table and column because they're undocumented reserved words in the OCI8.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table(:itable,:icolumn))");
 
    // Bind a variable into the resource statement.
    oci_bind_by_name($s,":itable",$table,-1,SQLT_CHR);
    oci_bind_by_name($s,":icolumn",$column,-1,SQLT_CHR);
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

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;
/

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;
/

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;
/

/*
 * 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;
/

SQL> HELP RESERVED WORDS

 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*

SQL> HELP RESERVED WORDS (SQL)

SQL> HELP RESERVED WORDS (PL/SQL)

SQL> HELP INDEX

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

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);