Home » SQL & PL/SQL » Client Tools » Set path in variable (SQL*Plus: Release 11.2.0.1.0, WINDOWS)
Set path in variable [message #656569] Tue, 11 October 2016 10:32 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Hi

I am generating some csv files from sql*plus using spool command in a sql script file.

Example:

SQL>spool D:\Filezilla\colleagues\Mokarem\File1.csv
- - - - - -
- - - - - -
SQL>spool off
SQL>spool D:\Filezilla\colleagues\Mokarem\File2.csv
- - - - - -
- - - - - -
SQL>spool off
etc.

Now I want to set the path in a variable once and concatenate it with file name
like below example:

SQL>spool v_path||file1.csv
- - - - - - -
- - - - -- -
SQL>spool off
SQL>spool v_path||file2.csv
- - - - -- -
- - - - - - -
SQL>spool off


Is it possible to achieve? How.....

[Updated on: Tue, 11 October 2016 10:49]

Report message to a moderator

Re: Set path in variable [message #656571 is a reply to message #656569] Tue, 11 October 2016 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

def path=D:\Filezilla\colleagues\Mokarem\
spool &path..file1.csv
Re: Set path in variable [message #656575 is a reply to message #656571] Tue, 11 October 2016 11:53 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Thanks.

If I use double dots .. like spool &path..file1.csv, the file names are created like .file1.csv, .file2.csv

If I use single dot . like spool &path.file1.csv, the file names are ok. But one strange problem is one blank line is appended at the begging of file.



Re: Set path in variable [message #656576 is a reply to message #656575] Tue, 11 October 2016 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, single dot, just a typo.

I don't know for the other issue as you didn't post anything I can reproduce.

Re: Set path in variable [message #656577 is a reply to message #656576] Tue, 11 October 2016 12:01 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Below is my script:

set feedback off
set verify off
set trimspool on
set heading off
set termout off
set echo off
set linesize 32767
define filepath = D:\Filezilla\colleagues\Mokarem\
--1
spool &filepath.MNF.csv
SELECT '"    ";"MNF_ABR";"MNF"' as col 
  FROM DUAL 
UNION ALL
SELECT '"'||rownum||'";"'||MNF_ABR||'";"'|| MNF||'"' as col 
  FROM ROH_MNF 
 WHERE cty = 'D'  
   AND rownum <11
/
spool off
--2
spool &filepath.MOL.csv
SELECT '"  ";"FCC";"MOL"' as col 
  FROM dual 
UNION ALL
SELECT '"'||rownum||'";"'||FCC||'";"'||MOL||'"' as col 
  FROM ROH_MOL 
 WHERE cty = 'D' 
   AND rownum<11
/
spool off
Re: Set path in variable [message #656578 is a reply to message #656577] Tue, 11 October 2016 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> host type e:\z.sql
set feedback off
set verify off
set trimspool on
set heading off
set termout off
set echo off
set linesize 32767
define filepath = E:\
spool &filepath.MNF.csv
SELECT '"    ";"MNF_ABR";"MNF"' as col
  FROM DUAL
/
spool off

SQL> @e:\z.sql
SQL> host type E:\MNF.csv
"    ";"MNF_ABR";"MNF"
No blank line at top of the spool file.
Re: Set path in variable [message #656586 is a reply to message #656575] Tue, 11 October 2016 23:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
mokarem wrote on Tue, 11 October 2016 09:53

...But one strange problem is one blank line is appended at the begging of file.
This does not seem to happen when the commands are run from the SQL prompt, but does happen when the commands are stored in a SQL script file, then that SQL script file is run. To fix the problem, add:

SET NEWPAGE NONE
Re: Set path in variable [message #656590 is a reply to message #656586] Wed, 12 October 2016 00:57 Go to previous message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is indeed my default value but not SQL*Plus one:

Quote:
SET NEWP[AGE] {1 | n | NONE}
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

[Updated on: Wed, 12 October 2016 00:58]

Report message to a moderator

Previous Topic: Check data types of columns from large query
Next Topic: Error 6 initializing SQL*Plus
Goto Forum:
  


Current Time: Thu Nov 23 10:57:15 CST 2017

Total time taken to generate the page: 0.01463 seconds