MacLochlainns Weblog

CREATE OR REPLACE VIEW schema_tables AS
SELECT   o.object_type AS object_type
,        c.table_name AS TABLE_NAME
,        c.column_id AS column_id
,        c.column_name AS column_name
,        DECODE(c.nullable,'N','NOT NULL','') AS NULLABLE
,        DECODE(c.data_type
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,c.data_type
,          'BINARY_DOUBLE',c.data_type
,          'BLOB'         ,c.data_type
,          'CLOB'         ,c.data_type
,          'CHAR'         ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'DATE'         ,c.data_type
,          'FLOAT'        ,c.data_type
,          'LONG RAW'     ,c.data_type
,          'NCHAR'        ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'NVARCHAR2'    ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'NUMBER'       ,DECODE(NVL(c.data_precision||c.data_scale,0)
,        0,c.data_type
,        DECODE(NVL(c.data_scale,0),0
,        c.data_type||'('||c.data_precision||')'
,        c.data_type||'('||c.data_precision||','|| c.data_scale||')'))
,          'RAW'          ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'VARCHAR'      ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'VARCHAR2'     ,DECODE(NVL(c.data_length,0),0,c.data_type
,        c.data_type||'('||c.data_length||')')
,          'TIMESTAMP'     , c.data_type,c.data_type) AS data_type
,        CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default
FROM     user_tab_columns c,user_objects o
WHERE    o.object_name = c.table_name
ORDER BY c.table_name, c.column_id;

<?php
  // Declare input variables.
  (isset($_GET['type'])) ? $bind = $_GET['type']."%" : $bind = "%";
 
  // Control variables.
  $first_row = true;
  $table_name = '';
 
  // Connect to the database, change UID, password and TNS alias.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    $s = oci_parse($c,"SELECT DECODE(st.object_type,'TABLE','Table Name: '
                     , 'VIEW' ,'View Name: ')
                     ||       st.table_name
                     ,        st.column_name
                     ,        st.nullable
                     ,        st.data_type
                     ,        st.data_default
                     FROM     schema_tables st
                     WHERE    st.object_type LIKE UPPER(:input)
                     ORDER BY st.table_name
                     ,        st.column_id");
 
    // Bind PHP variables to the OCI input or in mode variables.
    oci_bind_by_name($s,':input',$bind,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 data.
    while (oci_fetch($s))
    {
      if (($first_row) || (oci_result($s,1) != $table_name))
      {
        $first_row = false;
 
        // Read fetched headers.
        print '<tr>';
        print '<td colspan="4" class="e">'.oci_result($s,1).'</td>';
        print '</tr>';
        print '<tr>';
        for ($i = 2;$i <= oci_num_fields($s);$i++)
          print '<td class="e">'.oci_field_name($s,$i).'</td>';
        print '</tr>';
        $table_name = oci_result($s,1);
      }
 
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 2;$i <= oci_num_fields($s);$i++)
      {
        print '<td class="v">';
        if (!oci_field_is_null($s,$i))
          print oci_result($s,$i);
        else
        print ' ';
        print '</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'])."&lt;br /&gt;";
  }
?>

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 [TABLE | VIEW ] unless you want both: "
 
SET HEADING ON
TTITLE LEFT o1 o2 ' >' SKIP 1 -
'--------------------------------------------------------' SKIP 1
 
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON c2 SKIP PAGE
 
COL c1 NEW_VALUE o1 NOPRINT
COL c2 NEW_VALUE o2 NOPRINT
COL c3 FORMAT A32 HEADING "Name"
COL c4 FORMAT A8 HEADING "Null?"
COL c5 FORMAT A33 HEADING "Type"
COL c6 FORMAT A1 HEADING "Default?"
 
SELECT   DECODE(st.object_type,'TABLE','Table Name: < '
,                              'VIEW' ,'View Name: < ') c1
,        st.table_name c2
,        st.column_name c3
,        st.nullable c4
,        st.data_type c5
,        st.data_default c6
FROM     schema_tables st
WHERE    st.table_name LIKE UPPER('&input')||'%'
ORDER BY st.table_name
,        st.column_id;