Passing SQL*Plus bind variables to host command

[s***@gmail.com]

Hi,

I tried this and it did not work

SQL> var dirName varchar2(50);
SQL> exec :dirName := 'newdir'

PL/SQL procedure successfully completed.

SQL> host mkdir :dirName

SQL> host ls -ld newdir :dirName
newdir: No such file or directory
drwxr-xr-x 2 sanaik cisco 96 May 2 16:47 :dirName

SQL>

It created the directory names :dirName and not the newdir.
Is it possible to pass bind variables to host command?

Thanks,
Sameer

[IANAL_VISTA]

Post by s***@gmail.com
Hi,
I tried this and it did not work
SQL> var dirName varchar2(50);
SQL> exec :dirName := 'newdir'
PL/SQL procedure successfully completed.
SQL> host mkdir :dirName
SQL> host ls -ld newdir :dirName
newdir: No such file or directory
drwxr-xr-x 2 sanaik cisco 96 May 2 16:47 :dirName
SQL>
It created the directory names :dirName and not the newdir.
Is it possible to pass bind variables to host command?

No. What makes you think that the OS knows anything about "bind variables"?
Besides WHY do you want to issue an "ls" command.
SQL*Plus can't see the results of it.

[Malcolm Dew-Jones]

IANAL_VISTA (***@hotmail.com) wrote:
: ***@gmail.com wrote in
: news:***@l41g2000cwc.googlegroups.com:

: > Hi,
: >
: > I tried this and it did not work
: >
: > SQL> var dirName varchar2(50);
: > SQL> exec :dirName := 'newdir'
: >
: > PL/SQL procedure successfully completed.
: >
: > SQL> host mkdir :dirName
: >
: > SQL> host ls -ld newdir :dirName
: > newdir: No such file or directory
: > drwxr-xr-x 2 sanaik cisco 96 May 2 16:47 :dirName
: >
: > SQL>
: >
: > It created the directory names :dirName and not the newdir.
: > Is it possible to pass bind variables to host command?
: >
: No. What makes you think that the OS knows anything about "bind variables"?

Obviously the os knows nothing about bind variables - but sqlplus
certainly does, and sqlplus is the tool that is parsing the sqlplus
command and invoking the necessary system calls. Seems like a reasonable
question to me.


: Besides WHY do you want to issue an "ls" command.
: SQL*Plus can't see the results of it.

Obviously the poster is checking the results - a process commonly called
testing, or debugging.


However, in this case I think the poster will find that regular sqlplus
define variables will work, something like

define dirname="newdir"
host "mkdir &dirname."

Note also that there is a trick with one of the sqlplus column format
commands that allows a value within the database (including bind
variables) to be set into a define variable. So, even though a bind
variable can't be used directly as originally attempted, it is
nevertheless possible to use the bind variable value in the host command -
by using the trick to set it into a define variable which can be used.

The docs about formating reports within sqlplus should explain the trick,
I forget what it is without my examples in front of me.

--

This space not for rent.

[Rene Nyffenegger]

Post by s***@gmail.com
Hi,
I tried this and it did not work
SQL> var dirName varchar2(50);
SQL> exec :dirName := 'newdir'
PL/SQL procedure successfully completed.
SQL> host mkdir :dirName
SQL> host ls -ld newdir :dirName
newdir: No such file or directory
drwxr-xr-x 2 sanaik cisco 96 May 2 16:47 :dirName
SQL>
It created the directory names :dirName and not the newdir.
Is it possible to pass bind variables to host command?

SQL> define dirname='newdir'
SQL> host mkdir &dirname
SQL> host ls &dirname

hth
Rene

[s***@gmail.com]

Thanks a lot to all those responded. It worked via define variable
mechanism.
However, I had to omit the double quotes around the command as in
host "mkdir &dirname."
With double quotes the shell was not able to find the mkdir, even after
specifying absolute path. Strange, but it worked w/o the quotes.

The actual requirement here was to get the directory name from DB,
create it and then continue further processing in SQL. I did not want
to quit SQL*Plus, create the directory and restart SQL*Plus.

Thanks again,