Here document and sqlplus

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

Hello monks , I am invoking the database as follow:

open (SQLPLUS, "|sqlplus -s usernam/pass" ) or die "";
print SQLPLUS <<SQL
select * from tab where tnam like 'monks';
SQL
close SQLPLUS;
[download]

the script work fine , but I am trying to find away to check the output , I need to know if I am getting somthing like

No rows found
[download]

Is there a way for me to check the output of the select statment inside the here document? Can someone help ? thanks

Comment on Here document and sqlplus Select or Download Code

Replies are listed 'Best First'.
Re: Here document and sqlplus by Roger (Parson) on Jan 29, 2004 at 06:08 UTC
The simplest (fix) I can think of is to redirect the output of sqlplus to a file, and then read from that file afterwards. `open (SQLPLUS, "\|sqlplus -s usernam/pass > result.txt" ) or die ""; print SQLPLUS <<SQL select * from tab where tnam like 'monks'; SQL close SQLPLUS; # read the results open RESULT, "<result.txt"; $result = do { local $/; <RESULT> }; close RESULT;` [download]	[reply] [d/l]
Re: Here document and sqlplus by dws (Chancellor) on Jan 29, 2004 at 05:53 UTC
Is there a way for me to check the output of the select statment inside the here document? No. The here document is just a string. You have the semi-class problem of having a command that you want to both write to and read from. Take a look in perlipc in the online documentation (`perldoc perlipc`), and look for the section titled "Bidirectional Communication with Another Process". It'll describe how to use `IPC::Open2`.	[reply] [d/l] [select]
Re: Here document and sqlplus by pelagic (Priest) on Jan 29, 2004 at 09:20 UTC
try this: `print SQLPLUS <<SQL spool anyfile.log; select * from tab where tnam like 'monks'; spool off SQL close SQLPLUS;` [download] You can then analyse the content of the spool file either manually or within your program. You should also check the return value of sqlplus ... Imre	[reply] [d/l]
Re: Here document and sqlplus by techy (Scribe) on Jan 29, 2004 at 15:52 UTC
This may be slightly off-topic for the question asked, but is there a reason why you are opening a pipe to sqlplus instead of using DBI and DBD::Oracle? There is nothing wrong with the suggestions listed in this node (as long as the script is only being executed by a single user at a time), however I believe you will find that opening a pipe to sqlplus doesn't scale well as an application grows, and locks you into a single database vendor. Regards, techy	[reply]