SQL.sql (uses the included v_t.sql, too, but doesn't need to--see included comments) writes a SQL query that can be used as-is to display only rowids and non-null columns from a table, or can be easily edited to display whatever
nullable columns are also desired. (Nullable columns are marked as such for developers to refer to later, but by default do not display.) Basically, this is a SQL*Plus method of duplicating the 'browse table' command found in a lot of SQL utilities, with the added benefit that by default this shows only non-null columns.

--This is a modification of his CREATE_CONTROL.SQL, which creates a SQL*Loader Control file.

PROMPT First let's find a table to query...
@v_t
--This is another of my toolbox queries, used to find table like ?
--and owner like ? when I am too lazy to look up the names.
--If you don't already have it, or something like it, you can take out these
lines.

SET echo OFF -
 heading OFF -
 verify OFF -
 feedback OFF -
 show OFF -
 trim OFF -
 pages 1 -
 lines 200 -
 concat ON -
 trimspool ON

TTITLE OFF
BTITLE OFF

--This is where I would just cut and paste table and/or owner from the output
of v_t.sql, above.
ACCEPT v_tbl PROMPT "Enter TABLE NAME for which you want a SQL query:     "
ACCEPT v_own PROMPT "Enter the OWNER of the table name:                   "

ACCEPT v_syn PROMPT "Enter a SYNONYM for the table name you just entered: "
--This will be the name of the file you are now creating ( '.sql' will be added
automatically).
SPOOL &&v_syn..sql

COLUMN where_when NOPRINT  NEW_VALUE my_title
TTITLE LEFT my_title
SELECT
 'PROMPT --Created by Querying ' || dat.name || ' on ' || TO_CHAR(
  sysdate, 'dd-Mon-yyyy "at" HH:MI AM "System Time' || ' using SQL.SQL, by Chris Nelson') where_when
 , NULL
FROM
 dual
 , v$database   dat
/

TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS
SET termout OFF -
 pages 0

PROMPT PROMPT
PROMPT PROMPT --This query selects NON-NULL columns by default, and you can set the others to display later.
PROMPT PROMPT
PROMPT PROMPT --Looking at &&v_own..&&v_tbl
PROMPT PROMPT --First COUNT the records in the table:

SELECT
 CHR(10) || 'SET feedback OFF' || CHR(10) || CHR(10) ||
 'SELECT' || CHR(10) || CHR(9) || 'COUNT(1) "Total Rows" '
  || CHR(10) ||
 'FROM ' || CHR(10) || CHR(9) || RTRIM( '&&v_own..&&v_tbl') || CHR(10)
 || '/'
 || CHR(10) || CHR(10) || 'SET feedback ON' || CHR(10)
FROM
 dual
/
 

SELECT
 CHR(10) || 'SET pages 1000 -' || CHR(10) || CHR(9) || 'lines 1000' || CHR(10)
FROM
 sys.dual
/

SELECT
 DECODE( column_id
  , 1, 'SELECT' || CHR(9) || 'rowid'
  )
 || DECODE( nullable
  , 'Y', '--'
  )
 || DECODE( SIGN( column_id - 1)
  , 0, CHR(9) || ', ' || '&&v_syn' || '.' || LOWER( RPAD( column_name, 33, ' '))
  , 1, CHR(9) || ', ' || '&&v_syn' || '.' || LOWER( RPAD( column_name, 33, ' '))
  )
 || DECODE( nullable
  , 'Y', CHR(9) || CHR(9) || CHR(9) || '--nullable'
  )
FROM
 sys.all_tab_columns
WHERE 1=1
AND table_name = RTRIM( UPPER( '&&v_tbl'))
ORDER BY
 COLUMN_ID
/

SELECT
 'FROM ' || CHR(10) || CHR(9) || DECODE( NVL( '&&v_own', 'NO_OWNER_LISTED')
     , 'NO_OWNER_LISTED', NULL
     , LOWER( '&&v_own') || '.')
  || RTRIM( LOWER( '&&v_tbl')) || CHR(9) || CHR(9) || '&&v_syn' || CHR(10) ||
 'WHERE 1=1' || CHR(10) ||
 '/' || CHR(10) || CHR(10) ||
 'PROMPT' || CHR(10) || 'PROMPT' || CHR(9) || 'Running &&v_syn..SQL, a view of '
  || '&&v_own..&&v_tbl created from SQL.sql, by Chris Nelson' || CHR(10) ||
 'PROMPT' || CHR(10)
FROM
 sys.dual
/

SPOOL OFF

SET termout ON

PROMPT
PROMPT **************************************************
PROMPT ** Created file &&v_syn..sql
PROMPT **************************************************
PROMPT
PROMPT Now just '@&&v_syn' to show OR 'ed &&v_syn' to filter your data.

SET heading ON -
 feedback ON -
 pages 34
 
 
 

V_T.sql is also a handy little utility query to look up 'table like' and 'view
like' when you want a quick way to look up a table or view name.  Nothing too
special about it, though.

SET echo OFF -
 verify OFF -
 feedback ON

SET pages 34 -
 lines 1000

PROMPT
PROMPT *   Suggested use:  Use v_t.SQL to locate TABLES or VIEWS and OWNERS, then use t_c.SQL to
PROMPT *   view COLUMNS within tables, including NULLABLE, NOT_NULL or 'all' columns.
PROMPT

ACCEPT v_look PROMPT "What TABLE or VIEW name do you want to look for?  (Default 'ALL'): "
ACCEPT v_ownr PROMPT "What OWNER do you want to look for?  (Default 'ALL'):              "

COLUMN "Owner"   FORMAT A10
COLUMN "View or Table Name" FORMAT A35
COLUMN "Origin"  FORMAT A15

BREAK ON "Origin"

SELECT
 LOWER( owner)   "Owner"
 , LOWER( views.view_name) "View or Table Name"
 , 'From ALL_VIEWS'  "Origin"
FROM
 sys.all_views  views
WHERE 1=1
AND views.view_name LIKE UPPER( '%&&v_look%')
AND views.owner LIKE UPPER( '%&&v_ownr%')
UNION
SELECT
 LOWER( owner)   "Owner"
 , LOWER( tables.table_name) "View or Table Name"
 , 'From ALL_TABLES'  "Origin"
FROM
 sys.all_tables  tables
WHERE 1=1
AND tables.table_name LIKE UPPER( '%&&v_look%')
AND tables.owner LIKE UPPER( '%&&v_ownr%')
ORDER BY "Origin"
/

CLEAR BREAKS
CLEAR COLUMNS

Return to : Oracle Database, SQL, Application, Programming Tips