Re: Unique spool filenames?

From: jared still <jkstill_at_teleport.com>
Date: 1997/05/25
Message-ID: <33878831.28149219@news.teleport.com>#1/1

I have not seen any responses that give a good solution to creation of temporary ( and unique ) filenames for SQL*PLUS.

Here are 2 SQL scripts: tmpfile.sql and rmfile.sql

Create a temporary and unique filename with a call to tmpfile.sql

eg:
@tmpfile

the tmp filename is in the variable _tmp_file_name_

to use in the spool command:

spool &&_tmp_file_name

Don't forget to remove those tmp files!

@rmfile &&_tmp_file_name

This does assume you are using unix. ;)

• tmpfile.sql -------------------
  • tmpfile.sql
  • create a temporary file name
  • it will be of the form /tmp/<INSTANCE>.<USER>.<HSECS>
  • the temp filename will be in the variable _tmp_file_name_
  • use rmfile to remove tmp files

set verify off feed off
set echo off pause off feed off term off

var tmpstamp_ varchar2(30);
var dbname_ varchar2(8);

declare

	tmpsecs  number;
	secs varchar2(9);
	sec_len integer := 0;
begin
	select hsecs into tmpsecs from v$timer;

	--dbms_output.enable(1000000);
	--dbms_output.put_line('secs: ' || secs);

	select to_char(tmpsecs)
	into :tmpstamp_ 
	from dual;

	select lower(substr(global_name,1,instr(global_name,'.')-1))
	into :dbname_
	from global_name;

end;
/

col tmpstamp_ noprint new_value timestamp col filetmp_ noprint new_value _tmp_file_name_ col dbname_ noprint new_value instance

select :tmpstamp_ tmpstamp_, :dbname_ dbname_ from dual;

select

	'/tmp/' || 
	'&&instance' || 
	'.' || 
	lower(user)  || '.' ||
	'&timestamp' filetmp_ 

from dual
/

set term on

• rmfile.sql ---------------------
  • remove tmpfile

set verify off

prompt "enter temp filename: "

select 'removing &&1' from dual;

!if test -w '&&1' ; then rm &&1; fi

undef 1

• END ---------------------------

Jared Still
Blue Cross Blue Shield of Oregon
Oracle DBA/Part Time Perl Evangelist ;)
jkstill_at_teleport.com
jkstill_at_bcbso.com Received on Sun May 25 1997 - 00:00:00 CDT