Home » Other » Client Tools » how to format table display in oracle
how to format table display in oracle [message #281744] Mon, 19 November 2007 03:16 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
Hi all,


here iam updating a table which is having 70 columns and more than 10 thousand records, even col names are also big....

whenever displaying the table it is displaying in zig zag format and it is not readable.So iam not able to identify either data is updated or not.

Iam using oracle 10g and running select * from tablename command..

Is there any command to display table with too many columns in a formatted way please let me know..

Regards,

Ramakrishna M

[Updated on: Mon, 19 November 2007 03:17]

Report message to a moderator

Re: how to format table display in oracle [message #281746 is a reply to message #281744] Mon, 19 November 2007 03:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Which tool you are using to interact with Oracle DB ?

Thumbs Up
Rajuvan.
Re: how to format table display in oracle [message #281747 is a reply to message #281744] Mon, 19 November 2007 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
For a table which has many columns, perhaps installing and using a GUI tool is the easiest solution. Oracle offers a free tool called SQL Developer. Visit Oracle Technology Network pages to download this product.

Otherwise, you might write a query which would select only desired column(s) instead of all of them. Or, there's a possibility to select table contents "vertically" instead of "horizontally"; there are several solutions to it. I *think* I have a script somewhere so, if you decide to use such an approach, I'll look for it.
Re: how to format table display in oracle [message #281749 is a reply to message #281744] Mon, 19 November 2007 03:23 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member


sqlplus
Re: how to format table display in oracle [message #281751 is a reply to message #281744] Mon, 19 November 2007 03:27 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
hi littlefoot,

any of the scripts u mentioned may help because iam trying all types to extract rows and columns .So eventhough iam selecting one column also it is not readable.
Re: how to format table display in oracle [message #281790 is a reply to message #281744] Mon, 19 November 2007 05:37 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
please reply with answers as iam in urgent need of the display format.
Re: how to format table display in oracle [message #281796 is a reply to message #281790] Mon, 19 November 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't use IM Speak
2/ Don't use URGENT nothing is urgent in forum
3/ Post in the correct, you will have a faster answer -> SQL Client Tools
4/ Read SQL*Plus manual you will know how to format your columns and lines

Regards
Michel
Re: how to format table display in oracle [message #281799 is a reply to message #281796] Mon, 19 November 2007 06:18 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
sorry all, iam new to this forum.


michel , iam trying to find out sql* plus manual.


please have a look at my question

[Updated on: Mon, 19 November 2007 06:19]

Report message to a moderator

Re: how to format table display in oracle [message #281803 is a reply to message #281799] Mon, 19 November 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference

Regards
Michel
Re: how to format table display in oracle [message #281811 is a reply to message #281744] Mon, 19 November 2007 06:43 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
michel , as iam novice learner of sql, so please give me little bit clearer solution as i havent found any matches to my problem in sql* plus reference.

Re: how to format table display in oracle [message #281817 is a reply to message #281811] Mon, 19 November 2007 07:05 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Have a look at the section on Formatting Reports. Since you are trying to format a report, I would suggest that you look there to learn the report formatting techniques (I know, I know it's a bit subtle, I mean, who would have thought to look in the index for a word like format, but there you have it)
Re: how to format table display in oracle [message #281853 is a reply to message #281817] Mon, 19 November 2007 09:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, here it is - a script written by David L. Hunt.

As some Forum members can not download and open attachments (although this is a pure TXT file), I'll post the whole script.

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 ***
Re: how to format table display in oracle [message #281857 is a reply to message #281853] Mon, 19 November 2007 10:46 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Seems that dh_file package is needed. Google and you will easily find it.
Re: how to format table display in oracle [message #281935 is a reply to message #281857] Tue, 20 November 2007 01:31 Go to previous message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
hi littlefoot ,thanks for your effort ,but iam not in a position to implement your script as iam begginer so ,it may useful to me as a future reference.

Previous Topic: SQL Plus command length
Next Topic: How to connect to sqlplus
Goto Forum:
  


Current Time: Thu Mar 28 19:25:26 CDT 2024