×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Tips and Tricks

How do I display SELECT results vertically (instead of horizontally)? by SantaMufasa
Posted: 27 Jan 04 (Edited 27 Jan 04)

Following is my "VSelect.sql" (Vertical SELECT) script. (It does use PL/SQL's UTL_FILE package to write a temporary script file [TempVWriter.sql] to your default SQL*Plus directory. So, ensure that your Oracle instance's "UTL_FILE_DIR" parameter is either set to "*" or at least to your SQL*Plus default directory. The script will prompt for 1) the name of your table (or view), 2) "WHERE" clause (if needed), and 3) "ORDER BY" clause (if needed).

Following, then, are 1) the contents of the script (which you should save to your own script in order to execute), 2) a sample VSelect.sql invocation, 3) and resulting output.

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor "Dasages, LLC" makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM
REM Please contact the author via email (dave@dasages.com) when
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: VSelect.sql - PL/SQL code to display vertically (versus
REM    standard horizontal display) all columns of a table or view.
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM         dave@dasages.com
REM
REM **************************************************************
prompt
accept tname prompt "Enter the table or view you wish to display vertically: "
prompt
prompt Enter the "WHERE" clause(s)...
prompt - if there is no "WHERE" clause, press [Enter].
prompt - do not include the word, "WHERE"; just specify syntax beyond "WHERE".
prompt - do not use single quotes for literals; use double quotes (") to enclose literals.
prompt
accept where prompt '=> '
prompt
prompt Enter the "ORDER BY" clause...
prompt - if there is no "ORDER BY" clause, press [Enter].
prompt - do not include the words, "ORDER BY"; just specify syntax beyond "ORDER BY"
prompt - do not use single quotes for literals; use double quotes (") to enclose literals.
prompt
accept orderby prompt '=> '
prompt
set termout off
spool TempDesc.lst
desc &tname
spool off
set termout on
set serveroutput on
-- ===========================================================
-- Anonymous block, "VSelect"
-- ===========================================================
declare
    describe_text    varchar2(500);
    Column_number    binary_integer;
    type varchar_stencil    is table of varchar2(4000) index by binary_integer;
    Column_labels    varchar_stencil;
    CodeTab        varchar_stencil;
    Successful    boolean;
    code_hold    varchar2(500);
    where_clause    varchar2(500);
    orderby_clause    varchar2(500);
    double_quote    char(1)        := '"';
    two_single_quotes char(1)    := chr(39);
    first_time    boolean        := true;
-- ===========================================================
-- Function definition
-- ===========================================================
function rep
    (string_to_rep in varchar2, reps in number)
    return varchar2
is
begin
   return lpad(string_to_rep,reps*length(string_to_rep),string_to_rep);
end;
-- ===========================================================
-- Procedure definition
-- ===========================================================
procedure Assemble_columns (Successful out boolean)
is
begin
    dbms_output.enable(1000000);
    dh_file.file_open(1,'D:\dhunt\sqldba','TempDesc.lst','R');
    describe_text    := dh_file.read_next(1);
    describe_text    := ltrim(rtrim(describe_text));
    if describe_text like 'ERROR:%' then
        dbms_output.put_line(trim(describe_text)); -- prints "Error:"
        describe_text    := trim(dh_file.read_next(1)); -- reads Actual error text
        dbms_output.put_line(trim(describe_text)); -- prints Actual error text
        Successful    := false; -- returns 'Error' condition
    else
        describe_text    := dh_file.read_next(1); -- reads "----" line
        describe_text    := dh_file.read_next(1); -- reads 1st column name
        column_number    := 0;             -- initializes column_number    
        while describe_text <> '<EOF>' loop
            if length(trim(describe_text)) > 0 then
                column_number := column_number+1; -- increments column_number
                column_labels (column_number) := trim(substr(describe_text,1,35)); -- tables column name
            end if;
            describe_text    := dh_file.read_next(1); -- reads next column name
        end loop;
        Successful    := true; -- returns 'Success' condition
    end if;
    dh_file.file_close(1);
end;
-- ===========================================================
-- Procedure definition
-- ===========================================================
procedure Create_code
is
begin
    if length('&where') > 0 then
        where_clause    := 'WHERE '||
            replace(ltrim(rtrim('&where')),double_quote,two_single_quotes);
    else
        where_clause    := null;
    end if;
    if length('&orderby') > 0 then
        orderby_clause    := 'ORDER BY '||
            replace(ltrim(rtrim('&orderby')),double_quote,two_single_quotes);
    else
        orderby_clause    := null;
    end if;
    dh_file.file_open(2,'D:\dhunt\sqldba','TempVWriter.sql','W');
    dh_file.file_prt(2,'Declare'||chr(10)||'Begin'||chr(10)||
        chr(9)||'for r in (select * from &tname '||Where_clause||' '||
        Orderby_clause||') loop');
    dh_file.file_prt(2,'dbms_output.put_line('''||rep('=',80)||''');');
    for i in 1..Column_number loop
        code_hold    := 'dbms_output.put_line(rpad('''||column_labels(i)||
            ''',30)||'': [''||r.'||column_labels(i)||'||'']'');';
        dh_file.file_prt(2,code_hold);
    end loop;
    dh_file.file_prt(2,chr(9)||'end loop;'||chr(10)||'end;'||chr(10)||'/'||chr(10));
    dh_file.file_close(2);
end;
-- ===========================================================
-- Main-line Logic
-- ===========================================================
begin
    Assemble_columns (Successful);
    if Successful then
        Create_code;
    end if;
end;
/
@TempVWriter

REM *** End of Script ***

SQL> @vselect

Enter the table or view you wish to display vertically: s_emp

Enter the "WHERE" clause(s)...
- if there is no "WHERE" clause, press [Enter].
- do not include the word, "WHERE"; just specify syntax beyond "WHERE".
- do not use single quotes for literals; use double quotes (") to enclose literals.

=> rownum <= 3

Enter the "ORDER BY" clause...
- if there is no "ORDER BY" clause, press [Enter].
- do not include the words, "ORDER BY"; just specify syntax beyond "ORDER BY"
- do not use single quotes for literals; use double quotes (") to enclose literals.

=> last_name, first_name

================================================================================
ID                            : [3]
LAST_NAME                     : [Nagayama]
FIRST_NAME                    : [Midori]
USERID                        : [mnagayam]
START_DATE                    : [17-JUN-91]
COMMENTS                      : []
MANAGER_ID                    : [1]
TITLE                         : [VP, Sales]
DEPT_ID                       : [31]
SALARY                        : [1400]
COMMISSION_PCT                : []
================================================================================
ID                            : [2]
LAST_NAME                     : [Ngao]
FIRST_NAME                    : [LaDoris]
USERID                        : [lngao]
START_DATE                    : [08-MAR-90]
COMMENTS                      : []
MANAGER_ID                    : [1]
TITLE                         : [VP, Operations]
DEPT_ID                       : [41]
SALARY                        : [1550]
COMMISSION_PCT                : []
================================================================================
ID                            : [1]
LAST_NAME                     : [Velasquez]
FIRST_NAME                    : [Carmen]
USERID                        : [cvelasqu]
START_DATE                    : [03-MAR-90]
COMMENTS                      : []
MANAGER_ID                    : []
TITLE                         : [President]
DEPT_ID                       : [50]
SALARY                        : [2500]
COMMISSION_PCT                : []
SQL>


*** End of FAQ Tip ***

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close