Error In DBI - Cannot execute multiple statements

  • Thread starter pankaj_wolfhunter
  • Start date
P

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
 
P

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
 
M

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.
 
P

pankaj_wolfhunter

Greetings,
Sample script
#!/usr/bin/perl -w
$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
});

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

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
 
M

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.
 
P

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
 
P

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top