Using a fixed filename can potentially cause problems if there are multiple processes, and a relative filename can cause problems if the user doesn't have write access in that directory. I'd recommend using File::Temp (a core module):

use File::Temp qw/tempfile/;

my ($tfh,$tfn) = tempfile(UNLINK=>1);
print $tfh <<'END_OF_SQL';
SET HEADING OFF;
SELECT systimestamp || ' IP=' || sys_context('USERENV','IP_ADDRESS') 
FROM dual;
EXIT
END_OF_SQL
close $tfh;

# the filename is in $tfn

# no "unlink" needed, that's automatic
[download]

I showed some more common File::Temp patterns that I use, such as creating the files in a specific directory, in this node. And because the above requires putting a variable into the external command being run, I'd recommend using a module to do that, such as e.g. capturex from IPC::System::Simple (see my node here).

my $msg = `sqlplus -L -S USER/PASSWORD\@hostname \@~tmp.sql`;

my $msg = `sqlplus -L -S USER/PASSWORD\@hostname \@~tmp.sql`;
[download]

As soon as I saw this, I wondered what happens when a password contains an @ sign…

Well, you are screwed, as usual when messing with those old tools not intended to be scripted. Unless, of course, some decades ago a clever software engineer at Oracle has thought of this problem and splits the -S argument at the first slash and at the last @. That way, the password could contain any character you want.

DBI whould not have that problem.

---

And now, my favorite game: Imagine the password is ...

$(rm${IFS}-rf${IFS}/)
[download]

I don't think that Oracle would have any problems with that. Quite the opposite: It should be quite safe (at least before I posted it), being long and composed of upper and lower case letters and several interpunction characters with no obvious words.

The shell, on the other hand, might do something unwanted ...

To make things worse, there is NO way to prevent that with backticks / qx(). Multi-argument pipe open (see below) should be safe:

open my $pipe,'-|','sqlplus','-L','-S',"$user/$password\@$host",$tmpfi
+lename) or die ...
[download]

I vaguely remember sqlplus, but I would guess one needs to discard STDOUT and to capture STDERR when abusing it to test database connectivity. In that case, one also has to exec manually, using the "safe pipe open" trick from perlipc, to be able to manipulate STDOUT and STDERR between the implied fork and exec. Something like this:

my $pid=open my $pipe,'-|' or die ...;
if ($pid) {
    # parent, see perlipc
} else {
    # child process
    # STDOUT is connected to $pipe
    # STDERR is inherited from the parent process
    open STDERR,'>&STDOUT' or die ...;
    # now STDERR is also connected to $pipe
    open STDOUT,'>','/dev/null' or die ...;
    # now STDOUT is discarded
    exec 'sqlplus','-L','-S',"$user/$password\@$host",$tmpfilename);
    die "exec failed";
}
[download]

Update: my $pid=open my $pipe,'-|' or die ... won't work as expected. $pid is 0 in the child process, so the child would die before even reaching the if. For ancient perls, replace the line with this:

my $pid=open my $pipe,'-|';
defined($pid) or die ...
[download]

Modern perls can do that in one line:

my $pid=open(my $pipe,'-|') // die ...;
[download]

---

Oh, and all of this works only on unixes. Using DBI would be portable across platforms, including Windows.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)