1

I need to create directory in Linux with date stamp from SQL*Plus. Is there a command that can be run in *.sql file to create a directory with current date?

  • No you don't. You are trying to solve the wrong problem. Just create the directory from your bash script. – Balazs Papp Feb 2 at 9:57
1

The SQL*Plus command you're looking for is host. It allows you to run a Linux shell command, so

host mkdir $(date +%F);

Adjust the date format as necessary (man date will help you with that).

1

As I already said, you are trying to solve the wrong problem. So here it is, using a script:

[oracle@o71 ~]$ ls -l
total 4
-rwxr-xr-x. 1 oracle oinstall 129 Feb  3 12:35 script.sh
[oracle@o71 ~]$ cat script.sh
export SPOOLDIR=$(date +%F)
mkdir $SPOOLDIR
sqlplus -S / as sysdba <<EOF
spool $SPOOLDIR/output.txt
select * from dual;
exit
EOF
[oracle@o71 ~]$ ./script.sh

D
-
X

[oracle@o71 ~]$ ls -l
total 4
drwxr-xr-x. 2 oracle oinstall  24 Feb  3 12:35 2019-02-03
-rwxr-xr-x. 1 oracle oinstall 129 Feb  3 12:35 script.sh
[oracle@o71 ~]$ ls -l 2019-02-03/
total 4
-rw-r--r--. 1 oracle oinstall 245 Feb  3 12:35 output.txt
[oracle@o71 ~]$ cat 2019-02-03/output.txt

D
-
X

[oracle@o71 ~]$
  • Thanks. I tried above and it works fine. however, when i try to call .sql file from script.sh, i keep getting error" not spooling currently" export SPOOLDIR=$(date +%F) mkdir $SPOOLDIR sqlplus -S / as sysdba @daily.sql<<EOF spool $SPOOLDIR/output.csv daily.sql file set markup csv on @daily.sql spool off exit – sam Feb 3 at 20:53
  • only issue i am having now is with file permissions. I can't change the permission of Spooled out files in script.sh – sam Feb 4 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.