Re: Passing SQL*Plus bind variables to host command

IANAL_VISTA (IANAL_Vista_at_hotmail.com) wrote: : sameergn_at_gmail.com wrote in
: news:1115078085.366791.172670_at_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.