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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #281817 is a reply to message #281811] |
Mon, 19 November 2007 07:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 ***
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 19:50:23 CDT 2024
|