Error In DBI - Cannot execute multiple statements

[pankaj_wolfhunter]

Greetings,

Sample script

#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracle:dbname","username","password") || die
"Cannot connect to Database : $DBI::errstr\n";

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
});


Error:

DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD
ERROR: error possibly near <*> indicator at char 10 in '
<*>spool test.sql
select sysdate from dual
spool off
') [for Statement "
spool test.sql
select sysdate from dual
spool off
"] at hi.txt line 8.

What is wrong with the query?

Help would be appreciated

TIA

 

[Paul Lalli]

Subject: Error In DBI - Cannot execute multiple statements

That's exactly correct. You cannot execute multiple statements. This
is not an error in DBI. It's an error in your code.

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
});

Error:

DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD
ERROR: error possibly near <*> indicator at char 10 in '
<*>spool test.sql
select sysdate from dual
spool off
') [for Statement "
spool test.sql
select sysdate from dual
spool off
"] at hi.txt line 8.

What is wrong with the query?

You are trying to execute three statements at once.

$dbh->do('spool test.sql');
$dbh->do('select sysdate from dual');
$dbh->do('spool off');

or, if you prefer not typing as much, perhaps:
$dbh->do($_)
for ('spool test.sql', 'select sysdate from dual', 'spool off');


Of course, I would recommend not using a SQL*Plus-specific feature
like this, and just do it the "normal" way.
my ($date) = $dbh->selectrow_array('select sysdate from dual');
open my $ofh, '>', 'test.sql' or die $1;
print $ofh "$date\n";

Paul Lalli

 

[Mumia W.]

Greetings,

Sample script

#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracle:dbname","username","password") || die
"Cannot connect to Database : $DBI::errstr\n";

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
});


Error:

DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD
ERROR: error possibly near <*> indicator at char 10 in '
<*>spool test.sql
select sysdate from dual
spool off
') [for Statement "
spool test.sql
select sysdate from dual
spool off
"] at hi.txt line 8.

What is wrong with the query?

Help would be appreciated

TIA

It sounds like your database software is configured to disallow multiple
SQL statements to be put into a single query. Break the statements up:

$dbh->do(q{ spool test.sql });
$dbh->do(q{ select sysdate from dual });
$dbh->do(q{ spool off });

Note that I have no knowledge of Oracle.

 

[pankaj_wolfhunter]

Greetings,

Sample script

#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracle:dbname","username","password") || die
"Cannot connect to Database : $DBI::errstr\n";

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
});

Error:

DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD
ERROR: error possibly near <*> indicator at char 10 in '
<*>spool test.sql
select sysdate from dual
spool off
') [for Statement "
spool test.sql
select sysdate from dual
spool off
"] at hi.txt line 8.

What is wrong with the query?

Help would be appreciated

TIA

It sounds like your database software is configured to disallow multiple
SQL statements to be put into a single query. Break the statements up:

$dbh->do(q{ spool test.sql });
$dbh->do(q{ select sysdate from dual });
$dbh->do(q{ spool off });

Note that I have no knowledge of Oracle.- Hide quoted text -

- Show quoted text -

Thanks Paul, Mumia.

As Paul suggested, I tried with

Script:

#!usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracle:dbname","username","password") || die
"Database Connection not Made : $DBI::errstr\n";

my ($date) = $dbh->selectrow_array('select sysdate from dual');
open my $ofh, '>', 'test.sql' or die $1;
print $ofh "$date\n";

$dbh->disconnect();

It works. One question, if some error occurs from oracle side then it
gets displayed on console.
How can I redirect the output of a query (error or not) to another
file without displaying it on screen?

TIA

 

[Mumia W.]

[ script snipped ]
It works. One question, if some error occurs from oracle side then it
gets displayed on console.
How can I redirect the output of a query (error or not) to another
file without displaying it on screen?

TIA

The command "perldoc -f open" will show you how to redirect STDERR to a
file. It's best to do this only temporarily.

 

[Peter J. Holzer]

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
}); [...]
What is wrong with the query?

"spool" is not SQL command.

Also, a newline in an SQL query doesn't terminat the query, so you tried
to execute the query "spool test.sql select sysdate from dual spool off",
which is almost certainly not what you wanted.

hp

 

[Paul Lalli]

On 2007-03-01 12:09, (e-mail address removed)

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
}); [...]
What is wrong with the query?

"spool" is not SQL command.

True, but completely irrelevant. DBI doesn't care if the command
passed to do() is valid as per the SQL standard. As long as the
underlying RDMS understands it, it's fine.

Also, a newline in an SQL query doesn't terminat the query, so you
tried to execute the query "spool test.sql select sysdate from dual
spool off", which is almost certainly not what you wanted.

That, however, is relevant.

Paul Lalli

 

[Peter J. Holzer]

On 2007-03-01 12:09, (e-mail address removed)

$dbh->do(q{
spool test.sql
select sysdate from dual
spool off
}); [...]
What is wrong with the query?

"spool" is not SQL command.

True, but completely irrelevant. DBI doesn't care if the command
passed to do() is valid as per the SQL standard. As long as the
underlying RDMS understands it, it's fine.

But that's exactly the point: The RDBMS doesn't understand it, that's
why he got the error message.

Spool is an SQL*Plus command, *not* an (Oracle) SQL command.

hp