formatting output from SELECT * 1

[jmannix]

I get frustrated when I select all fields from a large table and the column headings and values wrap across many lines. I usually perform this task on a few rows to get an idea of what values are in the fields when I am unfamilar with the information stored in the table. Does anyone have a simple solution that would format the output like:

Field1: value1
Field2: value2
Field3: value3
etc ...

Thanks in advance,

Joe

 

[SantaMufasa]

Joe,

Here is my script, "VSelectW.sql" (Vertical Select for Windows). (BTW, I widened this post's screen display to avoid undesirable code wrap-around.)

Section 1 -- Sample invocation and output:

SQL> @vselectw

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.

=> salary >= 1000

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.

=> salary desc

================================================================================
rowid                         : [AAACIdAAEAAAAQiAAA]
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                : []
================================================================================
rowid                         : [AAACIdAAEAAAAQiAAB]
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                : []
================================================================================

Section 2 -- Code for "VSelectW.sql"

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
set linesize 80
spool c:\temp\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;
   Path_name       varchar2(20)    := 'C:\temp\';
   sample_dbfile   varchar2(100);
-- ===========================================================
-- 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,Path_name,'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_labels(1):= 'rowid';
           column_number   := 1;                    -- 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,Path_name,'TempVWriter.sql','W');
   dh_file.file_prt(2,'Declare'||chr(10)||'Begin'||chr(10)||
           chr(9)||'for r in (select rowid,a.* from &tname a'||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;
/
@C:\temp\TempVWriter
*******************************************************************************************************************

Let me know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:21 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 14:21 (10Dec04) Mountain Time

 

[jmannix]

Dave (Mufasa),

Thank you very much! Obviously you also have found a need to modify the format of the output. I was really kind of hoping there was some SQLPlus setting that I missed. I didn't expect a complete PL/SQL script, although I was beginning to think I would have to write something similar. Once again, thank you for sharing.

Joe

 

[goranm]

Hi !

This was an interesting thing to do.
It is often you wish you have these functionality.

I´m not so good at PL/SQL, and when I tried to run the script I get this error for the line
" dh_file.file_open(1,Path_name,'TempDesc.lst','R');" :

"PLS-00201: identifier 'DH_FILE.FILE_OPEN' must be declared"

What do I have to do ?
And is it something more I have to take care of ?

/Goran

 

[SantaMufasa]

Goran,

Thanks for confirming the code. I had forgotten to include a packaged routine I used for "utl_file" management. Run the following code that creates the necessary procedure, and everything should improve:

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. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application 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 (see address below) when 
REM you have comments, suggestions, and/or difficulties with this
REM packages 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: DH_FILE.SQL - PL/SQL code to create a package (DH_FILE)
REM       providing the following 5 procedures and 1 function:
REM		1) DH_FILE.FILE_OPEN	(<file#>,<path>,<file>,
REM					 <'W','R','A'>)
REM			[Write, Read, Append] respectively.
REM		2) DH_FILE.FILE_CLOSE	(<file#>)
REM			...uses handle from FILE array.
REM		3) DH_FILE.FILE_PRT	(<file#>,<output string>)
REM			...uses handle from FILE array.
REM		4) DH_FILE.FILE_STAGE	(<file#>,<output string>)
REM			...uses handle from FILE array.
REM		5) DH_FILE.FILE_FLUSH	(<file#>)
REM			...uses handle from FILE array.
REM		6) DH_FILE.READ_NEXT	(<file#>)
REM			...RETURNs next line from file_handle
REM			...uses handle from FILE array.
REM
REM	This package facilitates usage of the Oracle UTL_FILE
REM	package.  The UTL_FILE file operations include: 
REM	FOPEN, IS_OPEN, FCLOSE, FCLOSE_ALL, GET_LINE, PUT,
REM	NEW_LINE, PUT_LINE, PUTF, and FFLUSH 
REM
REM	VERY IMPORTANT NOTE!!!: For any write use of UTL_FILE,
REM	the instance must be prepared to write to O/S directories
REM	via the init.ora parameter,
REM	"UTL_FILE_DIR = <path><or "*", meaning "all directories">.
REM
REM AUTHOR: Dave Hunt, Co-Principal, DaSages
REM	    dave@Dasages.com
REM         1-801-TRY-DAVE (1-801-879-3283)
REM
REM **************************************************************
REM Maintenance History:
REM
REM 19-MAR-97: Original Code
REM 22-DEC-98: Enhanced to allow for multiple file handling by number
REM **************************************************************
REM **************************************************************
REM DH_FILE Package Specification
REM **************************************************************
create or replace package dh_file is
   procedure FILE_OPEN 	(which_file in binary_integer,
			path in varchar2,
			filename in varchar2,
			read_write_append in varchar2);
   procedure FILE_CLOSE (which_file in binary_integer);
   procedure FILE_PRT	(which_file in binary_integer,
			str_to_prt in varchar2);
   procedure FILE_STAGE (which_file in binary_integer,
			str_to_prt in varchar2);
   procedure FILE_FLUSH (which_file in binary_integer);
   function  READ_NEXT 	(which_file in binary_integer) return varchar2;
   -- pragma restrict_references(read_next,WNDS);
end;
/
REM **************************************************************
REM DH_FILE Package Body
REM **************************************************************
create or replace package body dh_file is
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_OPEN 
--  **************************************************************
	type file_handle_stencil is table of utl_file.file_type
		index by binary_integer;
	file_handles file_handle_stencil;
	file_handle	utl_file.file_type;
	procedure FILE_OPEN 	(which_file in binary_integer,
				path in varchar2,
				filename in varchar2,
				read_write_append in varchar2) is
	begin
		file_handles(which_file)
				:=	utl_file.fopen(
					location => path,
					filename => filename,
					open_mode => read_write_append,
					max_linesize => 32767);
	end;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_CLOSE 
--  **************************************************************
	procedure FILE_CLOSE (which_file in binary_integer) is
	begin
		utl_file.fclose(file_handles(which_file));
	end FILE_CLOSE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_PRT 
--  **************************************************************
	procedure FILE_PRT 	(which_file in binary_integer,
				str_to_prt in varchar2) is 
	begin
		utl_file.put_line(file_handles(which_file), str_to_prt);
	end FILE_PRT;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_STAGE
--  **************************************************************
	procedure FILE_STAGE 	(which_file in binary_integer,
				str_to_prt in varchar2) is
	begin
		utl_file.put (file_handles(which_file), str_to_prt);
	end FILE_STAGE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_FLUSH
--  **************************************************************
	procedure FILE_FLUSH 	(which_file in binary_integer) is
	begin
		utl_file.fflush (file_handles(which_file));
	end FILE_FLUSH;
--  **************************************************************
--  Packaged Global Function Definition: DH_FILE.READ_NEXT 
--  **************************************************************
	function  READ_NEXT 	(which_file in binary_integer)
				return varchar2 is
		hold_text	varchar(2000);
	begin
		utl_file.get_line (file_handles(which_file), hold_text);
		return hold_text;
	exception
		when no_data_found then
			return '<EOF>';
	end READ_NEXT;
end;
/
REM **************************************************************
REM End of Package: DH_FILE
REM **************************************************************

Sorry for the earlier omission. Let me know as soon as this works for you so that I can go to bed.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:44 (14Dec04) UTC (aka "GMT" and "Zulu"),
@ 00:44 (14Dec04) Mountain Time

 

[goranm]

Thank you for your answer.

I succed in creating the package "dh_file",
but when I try the package body I get this message:

13 begin
14 file_handles(which_file)
15 := utl_file.fopen(
16 location => path,
17 filename => filename,
18 open_mode => read_write_a
19 max_linesize => 32767);
20 end;

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/9 PL/SQL: Statement ignored
15/23 PLS-00306: wrong number or types of arguments in call to 'FOPEN'

Have a good nights sleep.

/Goran

 

[SantaMufasa]

Goran,

Again, good catch. I'll bet that PL/SQL for Oracle 8i does not support the last parameter ("max_linesize => 32767") in the FOPEN function. Just remove that entry plus the preceding comma, and try it again.

Let me know if that fixes the problem. Sorry for the extra hassle to get this going.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 08:53 (14Dec04) UTC (aka "GMT" and "Zulu"),
@ 01:53 (14Dec04) Mountain Time

 

[goranm]

Hi and thanks again !

No problems for the extra hassle, instead I feel stupid that I´m not able to fix the problems myself, but as I said, I´m no good at PL/SQL.
So if you can have patience with me here comes my next problem.

I was able to create the package body when I removed the line you told me.

If I then just run the first part of your first code (above the -- Anonymous block, "VSelect")
I get the following:


SQL> prompt

SQL> accept tname prompt "Enter the table or view you wish to display vertically: "
Enter the table or view you wish to display vertically: prompt
SQL> prompt Enter the "WHERE" clause(s)...
Enter the "WHERE" clause(s)...
SQL> prompt - if there is no "WHERE" clause, press [Enter].
- if there is no "WHERE" clause, press [Enter].
SQL> prompt - do not include the word, "WHERE"; just specify syntax beyond "WHERE".
- do not include the word, "WHERE"; just specify syntax beyond "WHERE".
SQL> prompt - do not use single quotes for literals; use double quotes (") to enclose literals.
- do not use single quotes for literals; use double quotes (") to enclose literals.
SQL> prompt

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

SQL> accept orderby prompt '=> '
=> prompt
SQL> set termout off
SQL> set linesize 80
SQL> spool c:\temp\TempDesc.lst
SQL> desc &tname
SP2-0561: Object does not exist .
SQL> spool off
SQL> set termout on
SQL> set serveroutput on
SQL>

I will also tell you that I´m running this script in an old environment (Oracle7 Server Release 7.3.4.5.0)

/Goran

 

[SantaMufasa]

Goran,

Thanks for sticking with this until we get it going. To fix the unwanted behaviour you are seeing, add "set echo off" to the first line of the script. I usually do that command as part of my login, so it is not an issue for me, but I should have added it to "VSelect.sql" for future sharing.

Also, you are probably already doing this, but be sure that you invoke the "VSelect.sql" script from the SQL prompt as "@VSelect" instead of copying and pasting...copying and pasting does not work properly when you have "Accept...prompt" code.

Try it again and let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 09:42 (14Dec04) UTC (aka "GMT" and "Zulu"),
@ 02:42 (14Dec04) Mountain Time

 

[goranm]

Hi !

It´s great to have a support like yours.

It seems that I have trouble with the TRIM function.
LTRIM and RTRIM seems to work, but the TRIM gives me the following:

37 dbms_output.put_line(trim(describe_text)); -- prints "Error:"

PLS-00201: identifier 'TRIM' must be declared.

I removed the TRIM function and tried again.

Then I got this:

Error at Line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 82
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "OWUSER.DH_FILE", line 14
ORA-06512: at line 33
ORA-06512: at line 92

I don´t know if the last two lines are coming from the script I run.
If so, here are the belonging rows:
33 dh_file.file_open(1,Path_name,'TempDesc.lst','R');
92 Assemble_columns (Successful);

/Goran

 

[SantaMufasa]

Goran,

I cannot remember whether PL/SQL for Oracle V7.3 supported the "UTL_FILE" flat-file-processing package. Is there a good reason that you have not upgraded to at least Oracle 8? Oracle7 has been off support for ages. Do you at least have access to an Oracle 8 installation somewhere to test whether your problem is due to "Geriatric Oracle"?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:36 (14Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:36 (14Dec04) Mountain Time

 

[goranm]

Hi !

Yes, we have an Oracle 8 installation as well, so I will test it there.
I hope I can test it tomorrow when I´m back to work.

I am most grateful for all your help.
I will let you know how it works.

/Goran

 

[goranm]

Hi again !
Now I have been able to test the script in an 8i-environment.
No problems with the packages (even the trim-function worked).
But then I pasted in the following part and tried to fill in tablename.
But it gave me an error.
And I also was a bit confused that I had to fill in everything twice (where clause, order by and tname).
Is that ok?

1 prompt
2 accept tname prompt "Enter the table or view you wish to display vertically: "
3 prompt
4 prompt Enter the "WHERE" clause(s)...
5 prompt - if there is no "WHERE" clause, press [Enter].
6 prompt - do not include the word, "WHERE"; just specify syntax beyond "WHERE".
7 prompt - do not use single quotes for literals; use double quotes (") to enclose literals.
8 prompt
9 accept where prompt '=> '
10 prompt
11 prompt Enter the "ORDER BY" clause...
12 prompt - if there is no "ORDER BY" clause, press [Enter].
13 prompt - do not include the words, "ORDER BY"; just specify syntax beyond "ORDER BY"
14 prompt - do not use single quotes for literals; use double quotes (") to enclose literals.
15 prompt
16 accept orderby prompt '=> '
17 prompt
18 set termout off
19 set linesize 80
20 spool c:\temp\TempDesc.lst
21 desc &tname
22 spool off
23 set termout on
24 set serveroutput on
25 -- ===========================================================
26 -- Anonymous block, "VSelect"
27 -- ===========================================================
28 declare
29 describe_text varchar2(500);
30 Column_number binary_integer;
31 type varchar_stencil is table of varchar2(4000) index by binary_integer;
32 Column_labels varchar_stencil;
33 CodeTab varchar_stencil;
34 Successful boolean;
35 code_hold varchar2(500);
36 where_clause varchar2(500);
37 orderby_clause varchar2(500);
38 double_quote char(1) := '"';
39 two_single_quotes char(1) := chr(39);
40 first_time boolean := true;
41 Path_name varchar2(20) := 'C:\temp\';
42 sample_dbfile varchar2(100);
43 -- ===========================================================
44 -- Function definition
45 -- ===========================================================
46 function rep
47 (string_to_rep in varchar2, reps in number)
48 return varchar2
49 is
50 begin
51 return lpad(string_to_rep,reps*length(string_to_rep),string_to_rep);
52 end;
53 -- ===========================================================
54 -- Procedure definition
55 -- ===========================================================
56 procedure Assemble_columns (Successful out boolean)
57 is
58 begin
59 dbms_output.enable(1000000);
60 dh_file.file_open(1,Path_name,'TempDesc.lst','R');
61 describe_text := dh_file.read_next(1);
62 describe_text := ltrim(rtrim(describe_text));
63 if describe_text like 'ERROR:%' then
64 dbms_output.put_line(trim(describe_text)); -- prints "Error:"
65 describe_text := trim(dh_file.read_next(1)); -- reads Actual error text
66 dbms_output.put_line(trim(describe_text)); -- prints Actual error text
67 Successful := false; -- returns 'Error' condition
68 else
69 describe_text := dh_file.read_next(1); -- reads "----" line
70 describe_text := dh_file.read_next(1); -- reads 1st column name
71 column_labels(1):= 'rowid';
72 column_number := 1; -- initializes column_number
73 while describe_text <> '<EOF>' loop
74 if length(trim(describe_text)) > 0 then
75 column_number := column_number+1; -- increments column_number
76 column_labels (column_number) := trim(substr(describe_text,1,35)); -- tables column name
77 end if;
78 describe_text := dh_file.read_next(1); -- reads next column name
79 end loop;
80 Successful := true; -- returns 'Success' condition
81 end if;
82 dh_file.file_close(1);
83 end;
84 -- ===========================================================
85 -- Procedure definition
86 -- ===========================================================
87 procedure Create_code
88 is
89 begin
90 if length('&where') > 0 then
91 where_clause := ' WHERE '||
92 replace(ltrim(rtrim('&where')),double_quote,two_single_quotes);
93 else
94 where_clause := null;
95 end if;
96 if length('&orderby') > 0 then
97 orderby_clause := ' ORDER BY '||
98 replace(ltrim(rtrim('&orderby')),double_quote,two_single_quotes);
99 else
100 orderby_clause := null;
101 end if;
102 dh_file.file_open(2,Path_name,'TempVWriter.sql','W');
103 dh_file.file_prt(2,'Declare'||chr(10)||'Begin'||chr(10)||
104 chr(9)||'for r in (select rowid,a.* from &tname a'||Where_clause||' '||
105 Orderby_clause||') loop');
106 dh_file.file_prt(2,'dbms_output.put_line('''||rep('=',80)||''');');
107 for i in 1..Column_number loop
108 code_hold := 'dbms_output.put_line(rpad('''||column_labels(i)||
109 ''',30)||'': [''||r.'||column_labels(i)||'||'']'');';
110 dh_file.file_prt(2,code_hold);
111 end loop;
112 dh_file.file_prt(2,chr(9)||'end loop;'||chr(10)||'end;'||chr(10)||'/'||chr(10));
113 dh_file.file_close(2);
114 end;
115 -- ===========================================================
116 -- Main-line Logic
117 -- ===========================================================
118 begin
119 Assemble_columns (Successful);
120 if Successful then
121 Create_code;
122 end if;
123 end;
124 /
125* @C:\temp\TempVWriter
SQL> /
Set value for tname: artgrp
old 21: desc &tname
new 21: desc artgrp
Set value for where: company_id = "GGN"
old 90: if length('&where') > 0 then
new 90: if length('company_id = "GGN"') > 0 then
Set value for where: company_id = "GGN"
old 92: replace(ltrim(rtrim('&where')),double_quote,two_single_quotes);
new 92: replace(ltrim(rtrim('company_id = "GGN"')),double_quote,two_single_quotes);
Set value for orderby: artgroup
old 96: if length('&orderby') > 0 then
new 96: if length('artgroup') > 0 then
Set value for orderby: artgroup
old 98: replace(ltrim(rtrim('&orderby')),double_quote,two_single_quotes);
new 98: replace(ltrim(rtrim('artgroup')),double_quote,two_single_quotes);
Set value for tname: artgrp
old 104: chr(9)||'for r in (select rowid,a.* from &tname a'||Where_clause||' '||
new 104: chr(9)||'for r in (select rowid,a.* from artgrp a'||Where_clause||' '||
prompt
*
Eror in line 1:
ORA-00900: invalid SQL statement

/Goran

 

[SantaMufasa]

Goran,

It appears to me, since a sequential number precedes every line of your code, that you issued a "get <script_name>" command at your SQL*Plus prompt. Using the "get" command causes Oracle to improperly analyse your SQL*Plus commands. Instead, you should issue the command "@<script_name>" at the SQL*Plus prompt. This, then, causes the SQL*Plus interpreter to analyze and interpret each separate command individually rather than as a single (invalid) SQL command.

So, the things you should do next to resolve your issues are 1) Add to your script, anywhere prior to Line 25, the SQL*Plus command, "set verify off" and 2) execute your script from the SQL*Plus command prompt as "@<script_name>".

Let us know if this improves things.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:24 (17Dec04) UTC (aka "GMT" and "Zulu"),
@ 00:24 (17Dec04) Mountain Time