Re: Newbie help with simple script

On Jan 28, 12:36 pm, "AS400Help" <x..._at_trillion.demon.co.uk> wrote:
> I only have a copy of oracle 10 and that has the SPOOL c:\myfile.txt APP
> command which lets me output text to a file as I run a script.
> But I want to do the same in lower versions maybe 9 and 8 which dont have
> the APP command I believe.
> So I am running the script below to produce seperate files. Is there a way
> to do something like this and output to a single file, which will work on
> any version of Oracle?
>
> Thanks for your help.
>
> set termout on;
> PROMPT **1 Version v$version
> set termout off;
> set linesize 4400
> spool C:\OScan1.fil
> select * from v$version;
> spool off
>
> set termout on;
> PROMPT **2 Parameters v$parameter
> set termout off;
> set linesize 1645
> spool C:\OScan2.fil
> select name,value,description from v$parameter order by name;
> spool off
>
> set termout on
> PROMPT **3 Users sys.dba_users
> set termout off
> set linesize 4400
> spool C:\OScan3.fil
> column user_id justify left;
> select * from sys.dba_users;
> spool off

I don't understand why you're going through all of these gyrations in that script when a single spool command and one output file could be generated:

set termout on;
spool C:\OScan.fil
PROMPT **1 Version v$version
set termout off;
set linesize 4400
select * from v$version;
set termout on;
PROMPT **2 Parameters v$parameter
set termout off;
set linesize 1645
select name,value,description from v$parameter order by name; set termout on
PROMPT **3 Users sys.dba_users
set termout off
set linesize 4400
column user_id justify left;
select * from sys.dba_users;
spool off

I also don't understand why you feel the need to prompt the user (yourself) with the nature of the results obtained; eliminate the prompts and you have the same output you're getting in 10g with the APPEND directive to the SPOOL command:

set termout off;
set linesize 4400
spool C:\OScan.fil
select * from v$version;
set linesize 1645
select name,value,description from v$parameter order by name; set linesize 4400
column user_id justify left;
select * from sys.dba_users;
spool off

Yes, your prompts are 'informational' to someone running this script, but why have them to begin with? A simple script, like that shown above, will produce the same result and will work on any prior version of SQL*Plus. You do not include the prompt text in the spool file, so why take the time and effort to generate them in the first place? They appear to be nothing more than wasted effort from my point of view as no one else runs this script except you.

David Fitzjarrell Received on Sun Jan 28 2007 - 14:01:32 CST