Maclochlainn's Weblog

The view is fairly straightforward and written to let you deploy it in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.

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;

The following programs let you display the formatted output from a web page or the SQL> prompt. You can get a web view of tables and views like this:

By running this PHP program:

<?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'])."<br />";
}
?>

Alternatively, you can run this script from the SQL> prompt:

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.object_type LIKE UPPER('&input')||'%'
ORDER BY st.table_name
,        st.column_id;

Perhaps a future release will include a standard component that does more.