REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	u_tab_data.sql
REM
REM <SQLDIR_GRP>UTIL TAB MOST</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Display table row in "vertical" format
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	u_tab_data.sql
REM 
REM Example:
REM	u_tab_data.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set serveroutput on size 100000
set feedback off
set verify off
set trimspool on

accept 1 prompt "Enter Table Name:"
accept 2 prompt "Enter Table Owner:"
accept 3 prompt "[1] Enter Look-up Column Name :"
accept column_value1 prompt "[1] Enter Look-up Column Value:"
accept 4 prompt "[2] Enter Look-up Column Name :"
accept column_value2 prompt "[2] Enter Look-up Column Value:"
accept 5 prompt "[3] Enter Look-up Column Name :"
accept column_value3 prompt "[3] Enter Look-up Column Value:"
set term off
spool tab_data.tmp

declare
    cursor tab_col_cur IS
        select   column_name
        ,        owner||'.'||table_name        table_o_n
        ,        column_id
        ,        decode(column_id,1,'',',') pos
        ,        '('||data_type||')'           data_type
        from     dba_tab_columns
        where    table_name = upper('&&1')
        and      owner      = upper('&&2')
        order by column_id;
    
    cursor tab_col_data_type_cur(p_column_name IN VARCHAR2) IS
        select data_type
        from   dba_tab_columns
        where  table_name = upper('&&1')
        and    owner      = upper('&&2')
        and    column_name = p_column_name;
    tab_col_data_type tab_col_data_type_cur%ROWTYPE;
    
    col1_where_wrap VARCHAR2(2);
    col2_where_wrap VARCHAR2(2);
    col3_where_wrap VARCHAR2(2);
        
    owner_table     VARCHAR2(300);
begin
    open tab_col_data_type_cur('&&3');
    fetch tab_col_data_type_cur INTO tab_col_data_type;
    IF tab_col_data_type.data_type = 'NUMBER'
    THEN
       col1_where_wrap := NULL;
    ELSE
       col1_where_wrap := chr(39);
    END IF;
    close tab_col_data_type_cur;
    
    open tab_col_data_type_cur('&&4');
    fetch tab_col_data_type_cur INTO tab_col_data_type;
    IF tab_col_data_type.data_type = 'NUMBER'
    THEN
       col2_where_wrap := NULL;
    ELSE
       col2_where_wrap := chr(39);
    END IF;
    close tab_col_data_type_cur;
    
    open tab_col_data_type_cur('&&5');
    fetch tab_col_data_type_cur INTO tab_col_data_type;
    IF tab_col_data_type.data_type = 'NUMBER'
    THEN
       col3_where_wrap := NULL;
    ELSE
       col3_where_wrap := chr(39);
    END IF;
    close tab_col_data_type_cur;
        
    dbms_output.put_line('set lines 132 pages 0 feedback on echo off verify off trimspool on');
    dbms_output.put_line('col line_brk newline');
    dbms_output.put_line('set arraysize 1');
    dbms_output.put_line('clear screen');
    dbms_output.put_line('spool tab_data.log');
    dbms_output.put_line('prompt DATA TYPE      COLUMN NAME                                       DATA');
    dbms_output.put_line('prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
    dbms_output.put_line('prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
    dbms_output.put_line('select');
    for tab_col_rec in tab_col_cur loop
       dbms_output.put_line(
            tab_col_rec.pos||
            '  '||
            chr(39)||
            ''||
            rpad(tab_col_rec.data_type,15)||
            rpad(tab_col_rec.column_name,50)||
            chr(39)||
            chr(124)||
            chr(124)||
            tab_col_rec.column_name||
            ' line_brk');
       owner_table := tab_col_rec.table_o_n;
    end loop;
    dbms_output.put_line('from      '||owner_table||'');
    dbms_output.put_line('where     '||NVL('&&3','X')||' = NVL('||col1_where_wrap||chr(38)||'column_value1'||col1_where_wrap||',''X'')');
    dbms_output.put_line('and       '||NVL('&&4','X')||' = NVL('||col2_where_wrap||chr(38)||'column_value2'||col2_where_wrap||',''X'')');
    dbms_output.put_line('and       '||NVL('&&5','X')||' = NVL('||col3_where_wrap||chr(38)||'column_value3'||col3_where_wrap||',''X'');');
    dbms_output.put_line('spool off');
end;
/
spool off
set term on

undefine 1
undefine 2

@tab_data.tmp