I'm building a powershell script that will execute a SQL query and spool result to a csv. This file is afterwards read and other actions will be made. I would like to spool in the User Profile directory of the user who runs the script.
Now i''ve hardcoded my directory and works fine.
spool C:\users\<username>\documents\cutoff.csv REP
but i'm not sure I've rights to save a file in any other directory on the HD.
I thought that would be something like this
spool %userprofiledirectory%\cutoff.csv
like in windows powershell there is the command
[Environment]::GetFolderPath('MyDocuments')
If you're using SQLcl or SQL Developer, then just use the CD command to set your current working directory - this will be used for both looking for files to open/read/execute as well as where to write new files.
You can of course also use a fully qualified file with directory path on the SPOOL command.
SQLcl: Release 19.2.1 Production on Thu Aug 15 09:07:50 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Aug 15 2019 09:07:55 -04:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> help cd
CD
---
Changes path to look for script at after startup.
(show SQLPATH shows the full search path currently:
- CD current directory setting set by last cd command
- baseURL (url for subscripts)
- topURL (top most url when starting script)
- Last Node opened (i.e. file in worksheet)
- Where last script started
- Last opened on sqlcl path related file chooser
- SQLPATH setting
- "." if in SQLDeveloper UI (included in SQLPATH in command line (sdsql))
).
SQL>