Printing Data Vertical instead of Horizontal 1

[unisysguru]

I am dumping data for our conversion to Oracle, to compare to data dump on our old mainframe. How can I dump the data vertically, I may have 60 columns of data which is very hard to read.

e.g. Select * from Table;
I get:

field1|field2|field3
data1 |data2 |123123.000

would like:

field1 = data1
field2 = data2
field3 = 123123.000
etc.

Thanks for any help!

Bill

 

[SantaMufasa]

Bill,

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, 2) "WHERE" clause (if needed), and 3) "ORDER BY" clause (if needed).

Following, then, is 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 &quot;WHERE&quot; clause(s)...
- if there is no &quot;WHERE&quot; clause, press [Enter].
- do not include the word, &quot;WHERE&quot;; just specify syntax beyond &quot;WHERE&quot;.
- do not use single quotes for literals; use double quotes (&quot;) to enclose literals.

=> rownum <= 3

Enter the &quot;ORDER BY&quot; clause...
- if there is no &quot;ORDER BY&quot; clause, press [Enter].
- do not include the words, &quot;ORDER BY&quot;; just specify syntax beyond &quot;ORDER BY&quot;
- do not use single quotes for literals; use double quotes (&quot;) 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>

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:08 (27Jan04) GMT, 11:08 (27Jan04) Mountain Time)

 

[unisysguru]

Thanks Dave,

I am very new to Oracle, though I have done years of SQL. Where do you store the script and get it to execute? I see you are using an "@" infront, why is this?

Bill

 

[SantaMufasa]

Bill,

Q. Where do you store the script and get it to execute?
A. When you install Oracle, SQL*Plus assigns a default path for user file/script/output use. That default path is the &quot;bin&quot; directory under whatever is your ORACLE_HOME path. I, however, NEVER leave the default in place...I create my own &quot;D:\dhunt\SQL&quot; directory for reading and writing scripts, then I &quot;tell&quot; SQL*Plus to use this new path for its default. To make this change, <right-click> on whatever icon you use to invoke SQL*Plus then replace the current default path by modifying the &quot;Start In:&quot; value to your preferred path. Following that change, SQL*Plus looks in the new &quot;Start In:&quot; location for default file activities, including running &quot;VSelect.sql&quot; if the new path is where you store the script.

Q. (Why) are (you) using an &quot;@&quot; infront (of the script name)?
A. In SQL*Plus &quot;@<scriptname>&quot; or &quot;start <scriptname>&quot; invoke <scriptname> for execution.

Let us know how the VSelect.sql works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:28 (27Jan04) GMT, 13:28 (27Jan04) Mountain Time)

 

[sem]

You may handle a number of directories to be searched by sql*plus by seting SQL_PATH environment (registry) variable.

Regards, Dima

http://seminihin.narod.ru