Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

All,
I need to get the return value from a sql script and redirect the outputto a file.
One of the requirements is to avoid showing passwords to 'ps' and the same code needs to run on win and unix.
I am now calling the script using code as below. 
 
--------- Start  My Code 
open SQLP, "|sqlplus -s /nolog >> $output_file_name " 
                                        or die "cannot open sqlplus $E
+RRNO \n"; 
# Connect and execute the script. This is done by writing to the strea
+m we open for sqlplus. 
SQLP "connect $user_name/$password \n" or die "cannot write in doScrip
+t"; 
print SQLP "\@$script_name; \n" or die "cannot write in doScript"; 
close SQLP or die "cannot close in doScript"; 

print "Status is :" . ($? >> 8); 
--------- End   My Code

[download]
The sql script I execute is :- 
 
****** Start  t.sql 
select sysdate from dual 
/ 
exit 77 
****** End    t.sql

[download]
This works if the exit value is 0 but anything else fails. 
 
................. Start  Error I see 
Uncaught exception from user code: 
        cannot close in doScript at sqlutils.pm line 160. 
        sqlutils::doSqlScript ('t.sql', 'scott', 'tiger@iasdb.local',
'a.log') called at tutils.pl ine 21 
................. End     Error I see

[download]
Your suggestions please.
Thanks & Regards,
Sunil.

Replies are listed 'Best First'.
Re: read return value from a sql script using piped open
by dws (Chancellor) on Dec 10, 2002 at 08:12 UTC
    Unless you dropped some characters doing a copy/paste,   SQLP "connect $user_name/$password \n" or die "cannot write in doScript"; is missing a leading print
[reply]
[d/l]
[select]
Re: read return value from a sql script using piped open
by graff (Chancellor) on Dec 10, 2002 at 07:44 UTC
    Maybe what you need to do is move the "exit" command for sqlplus out of the "$script_name" file and into a print statement: 
    ...
print SQLP "\@$script_name; \n" or die "cannot write in doScript"; 
# add:
print SQLP "exit\n" or die "cannot write exit to sqlplus in doScript";
close SQLP or die "cannot close in doScript"; 

...

****** Start  t.sql 
  select sysdate from dual 
  / 
****** end t.sql
...

    [download]
    Note: that's just something to try -- I don't have a clue whether it will work.

    Is it the case that you have no hope of installing DBI and DBD::Oracle on the platforms involved? This would make it a lot easier to communicate with oracle through perl, without exposing account names/passwords.

    One other point: are you sure you want to redirect sqlplus output to $output_file_name via the ">>" (append) operator? Do you intend to read that file back after a sequence of queries has been completed, or is it being read back after each query?

[reply]
[d/l]
Re: read return value from a sql script using piped open
by tachyon (Chancellor) on Dec 10, 2002 at 14:22 UTC

    ....same code needs to run on win and unix.

    Well this will not run on Windows open SQLP, "|sqlplus -s /nolog >> $output_file_name "

    If you want it portable you will just have to write some more code probably using DBI. You will be hard pressed to get system calls that will port although you can branch. For example in some of my application installation scripts I will often do this sort of thing to get the required behaviour, quotes, grammar, etc, etc: 

    my $OS      = $^O;
my $cwd     = cwd();
my $script  = $0;

$OS =~m/MSWin32/ ? system('cls') : system('clear');

[blah]

sub cpan_install {
    my ( @modules ) = @_;
    # get the right quotes for the OS Win wants " *nix wants '
    my $quote = $OS =~m/MSWin32/ ? '"' : "'";
    print "
I will now try to install the missing modules using the CPAN shell. 
If you have not used this before you will need to answer a number of 
initialization questions. You can generally just accept the defaults 
and it will work. Unfortunately if you have not used it before and try
+ 
to use it here it will hang invisibly waiting for your input, so you w
+ill
need to run it from the command line using the command below....\n\n";
    for my $module ( @modules ) {
        my $cmd = 'perl -MCPAN -e ' . $quote . 'install '. $module . $
+quote;
        print "Trying to install $module command line $cmd\nPlease be 
+patient.....\n";
        print `$cmd`;
    }
}

    [download]

    It works on *nix and Win but the code per se is not portable - there is just different code for each system....

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]
[select]
Re: read return value from a sql script using piped open
by tall_man (Parson) on Dec 10, 2002 at 19:51 UTC
    Take a look at IPC::Run. It will give you a lot more control of the other process.
[reply]
      Finally
      http://www.perldoc.com/perl5.6.1/pod/func/close.html
      solved my issue.
[reply]

Back to Seekers of Perl Wisdom