Re: please someone help me with sql

"M. Gedecke" <spam_at_gedecke.de> wrote in message news:<am4ntc$k3s$1_at_redenix.uni-muenster.de>...
> i want to create an empty file which has the name of the content of a
> variable and an ending. i thought of something like that:
>
> variable a number
> a:=select count(*) from user_verwaltung where sicherungs_art=1 and
> geloescht_am is null;
> spool 'variable a'.sve';
> spool off
>
> could someone please help me with the syntax of the commands? the name of
> the file should be something like: 23.sve
>
> thanx
>
> max

This is a slight variation of something I posted in the past. This script will select a spool file name from a table entry (constant via dual) and then create a spool file with the variable contents value.

set echo off
rem
rem Sample SQL and PL/SQL passing variable to SQL*Plus from PL/SQL rem
rem 20020712 Mark D Powell Sample code for newsgroup post rem
set echo on
column my_variable new_value myfilename
variable v_charfld varchar2(20)

begin
select 'testfilename'
into :v_charfld
from sys.dual
;
end ;
/
select :v_charfld as my_variable from dual; print v_charfld
spool &&myfilename
select sysdate from dual;
spool off

The contents of the file is the select sysdate from dual in this case.  The prior post just used a host command to issue a (UNIX) touch command to create an empty file with the desired name.

HTH -- Mark D Powell -- Received on Mon Sep 16 2002 - 14:23:07 CDT