OraFAQ Forum: Client Tools » Set path in variable

Home » Other » Client Tools » Set path in variable (SQL*Plus: Release 11.2.0.1.0, WINDOWS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Set path in variable [message #656569]

Tue, 11 October 2016 10:32

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

def path=D:\Filezilla\colleagues\Mokarem\
spool &path..file1.csv

Report message to a moderator

Re: Set path in variable [message #656575 is a reply to message #656571]

Tue, 11 October 2016 11:53

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.

Report message to a moderator

Re: Set path in variable [message #656576 is a reply to message #656575]

Tue, 11 October 2016 11:58

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Set path in variable [message #656577 is a reply to message #656576]

Tue, 11 October 2016 12:01

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

Report message to a moderator

Re: Set path in variable [message #656578 is a reply to message #656577]

Tue, 11 October 2016 12:10

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Set path in variable [message #656586 is a reply to message #656575]

Tue, 11 October 2016 23:11

Barbara Boehmer
Messages: 9103
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

Report message to a moderator

Re: Set path in variable [message #656590 is a reply to message #656586]

Wed, 12 October 2016 00:57

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 12:14:19 CST 2025