|
Re: sqlplus or DBI
by insensate (Hermit) on Nov 18, 2002 at 21:13 UTC
|
Typically when calling sqlplus scripts from any outside program... be it Perl or Shell... I wrap the sql commands in the following:
whenever SQLERROR exit SQL.SQLCODE
set feeback off
set verify off
set lines 32
set pages 0
set linesize 500
set tab off *
your script
exit SQL.SQLCODE
*SQLPlus often formats white space in output using tab characters, not
+ spaces... "set tab off" prevents this
You probably also want to use the -s switch to use silent mode. After implementing this, you can say:
$result=`sqlplus -s user/pass \@myfile`;
if($result=~/(?:SP2|ORA)/){
print "SQL ERROR: $result\n";
}
| [reply]
[d/l]
[select] |
|
|
Ooh, no! You don't want to do that!
The system status code is only one byte wide, and so can only handle values in the range 0 to 255. Picking a random example, do you know what error code 244 refers to? Yes, that's right: "integrity constraint (%s.%s) violated - child record found"
The point here is that the particular error I've quoted above is actually code 2292, which any good mod function can tell you is 256*8+244. The multiples of 256 get thrown away by the operating system.
Now, what do you think happens if you get the error "space quota exceeded for tablespace '%s'"? Yes, that's right: it's code 1536, which is exactly 256*6. And the system neatly truncates this to 0.
Actually, the best solution I found was to use WHENEVER ERROR EXIT 2, and terminate the script with EXIT 1. That way, you can tell if the script never started (exit code 0), completed successfully (code 1), or failed (code 2).
--
Tommy
Too stupid to live.
Too stubborn to die.
| [reply] |
|
|
Remember that using backticks to execute a call is not the same as using system(). The latter returns the exit status...the former the output of the call. So when I call a sql script that will exit with sql.sqlerror and it fails with ORA-2292... the errorcode will indeed be the value in $result as long as it is called with backticks or qx//.
Jason
| [reply]
[d/l] |
|
|
|
|
Re: sqlplus or DBI
by fruiture (Curate) on Nov 18, 2002 at 18:23 UTC
|
system() is not the right function for the purpose of capturing output. Consider qx// (aka `backticks`) or open() for that job.
I don't know if grepping for "ERROR" in the command's STDOUT would suffice, you should be able to specify that ;)
open my $pipe , '-|' , 'sqlplus user/pass @test.sql'
or die "hmm? $!";
while( <$pipe> ){
print if /error/i
}
close $pipe or die "hmhmm? $!";
--
http://fruiture.de | [reply]
[d/l] |
Re: sqlplus or DBI
by metadatum (Scribe) on Nov 18, 2002 at 18:19 UTC
|
the system call returns an error code -- and thats all.
If you want to be able to parse the output, use backticks:
@results = `sqlplus user/pass \@test.sql`;
| [reply]
[d/l] |
|
|
Yea but the error code is not correct. Even if I have errors in my sql, it still returns 0, success.
What would I be parsing the output for ?
thanks,
| [reply] |
Re: sqlplus or DBI
by x31forest (Novice) on Nov 18, 2002 at 18:38 UTC
|
Found something usefull on google groups just now.
Adding whenever sqlerror exit failure to my sql makes sqlplus return an error code > 0. So I'll be using that untill i hear of something better.
Any way I can do the same thing using DBI ?
thanks,
Paul | [reply] |
|
|
Yes, you can do the same thing with DBI. It would be even better...
The easy way ist to slurp the sql-commands-file and directly submit it to $dbh->prepare( $commands )->execute
Mor advanced would be to parse the SQL beforehand and submit the queries one after the other. In fact, you only have to split on semicolon, except semicolons in quotes.
--
http://fruiture.de
| [reply] |
Re: sqlplus or DBI
by UnderMine (Friar) on Nov 19, 2002 at 15:02 UTC
|
I am currious to see the script as generally you should not need to do that
try :-
$/='/';
while (<FILE>) {
# process and return errors
}
This assumes you are seperating your commands with '/' execution which is usual in oracle.
Hope this helps
UnderMine | [reply]
[d/l] |