See Capturing System Output.

Unfortunately, that thread only mentions backticks and piped opens. I would recommend one of the several modules I discussed here instead.

My script is only to verify database connection, database and listen is up.

Even for that purpose, I would use DBI and DBD::Oracle. It avoids messing with the default shell, and it avoids messing with SQLplus, which is absolutely not designed to be scripted.

DBI->connect(...) either works or fails, put that in a loop and report the collected pass/fail information.

Alexander

unfortunately, DBI would not be my option. I do not have internet access in Cloud.

Yes, even you can use CPAN. For working on networks that don't have a direct internet connection, host your own mini cpan mirror: minicpan. https://i.imgur.com/91sn32Q.jpg.

At the simplest, you could do:

my $result = qx{$ENV{ORACLE_HOME}/bin/sqlplus -L -V scott/scott \@data
+base 2>&1};
[download]

You can read the documentation for qx/.../ in perlop. Probably a better solution would be to use Capture::Tiny:

use Capture::Tiny qw(capture);

my ($stdout, $stderr, $exit) = capture {
    system("$ENV{ORACLE_HOME}/bin/sqlplus", '-L', '-V', 'scott/scott',
+ '@database');
};
[download]

Your original code escaped the @, so I carried that through in my examples. I'm not certain if that's intentional or not.

Hi Dave, yes. I added '/' to escape @ as it was considering @ as global symbol. I tried to use capture with the connected db, stdout output is "SQL*Plus: Release 12.1.0.2.0 Production" stder output is blank and exit output is 0. the output is the same with the down db. My intention is to verify database connection, so it is not a solution for me. And I cannot use DBI module.

The TNSPING utility can be used to test an Oracle Service Name. To use it:

1.Open a Command Prompt (click Start, click Run, type cmd, and then click OK).

2.Type tnsping <service name> (for Oracle 7.3 or Oracle 8i and later) or tnsping80 <service name> (for Oracle 8.0), and then press enter.

The TNS Ping Utility will result in an "OK" or a "Connect failed" message. [...]

This was basically the first result searching for "oracle test connection command line" btw. Sauce


holli

You can lead your users to water, but alas, you cannot drown them.

Try putting the SQL in a separate file.

#!perl
use strict;

open TMP,'>','~tmp.sql' or die "$!";
print TMP << 'END_OF_SQL';
SET HEADING OFF;
SELECT systimestamp || ' IP=' || sys_context('USERENV','IP_ADDRESS') 
FROM dual;
EXIT
END_OF_SQL
close TMP;

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

unlink '~tmp.sql';
[download]

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`;

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

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". ;-)

Thank you for your solution. This is exactly the stuff I wanted. It outputs the error message that caused the failing when it comes to connection failure.