Error In DBI - Cannot execute multiple statements

Discussion in 'Perl Misc' started by pankaj_wolfhunter@yahoo.co.in, Mar 1, 2007.

  1. Guest

    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
     
    , Mar 1, 2007
    #1
    1. Advertising

  2. Paul Lalli Guest

    On Mar 1, 7:09 am, ""
    <> wrote:
    > 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
     
    Paul Lalli, Mar 1, 2007
    #2
    1. Advertising

  3. Mumia W. Guest

    On 03/01/2007 06:09 AM, wrote:
    > 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.
     
    Mumia W., Mar 1, 2007
    #3
  4. Guest

    On Mar 1, 8:48 pm, "Mumia W." <paduille.4060.mumia.w
    > wrote:
    > On 03/01/2007 06:09 AM, wrote:
    >
    >
    >
    >
    >
    > > 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
     
    , Mar 2, 2007
    #4
  5. Mumia W. Guest

    On 03/02/2007 01:15 AM, wrote:
    > [ 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.
     
    Mumia W., Mar 2, 2007
    #5
  6. On 2007-03-01 12:09, <> wrote:
    > $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


    --
    _ | Peter J. Holzer | Es ist ganz einfach ihn zu verstehen, wenn
    |_|_) | Sysadmin WSR | man nur alle wichtigen Worte im Satz durch
    | | | | andere ersetzt.
    __/ | http://www.hjp.at/ | -- Nils Ketelsen in danr
     
    Peter J. Holzer, Mar 3, 2007
    #6
  7. Paul Lalli Guest

    On Mar 2, 8:29 pm, "Peter J. Holzer" <> wrote:
    > On 2007-03-01 12:09,
    > > $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
     
    Paul Lalli, Mar 3, 2007
    #7
  8. On 2007-03-03 02:54, Paul Lalli <> wrote:
    > On Mar 2, 8:29 pm, "Peter J. Holzer" <> wrote:
    >> On 2007-03-01 12:09,
    >> > $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

    --
    _ | Peter J. Holzer | Es ist ganz einfach ihn zu verstehen, wenn
    |_|_) | Sysadmin WSR | man nur alle wichtigen Worte im Satz durch
    | | | | andere ersetzt.
    __/ | http://www.hjp.at/ | -- Nils Ketelsen in danr
     
    Peter J. Holzer, Mar 3, 2007
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,219
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    431
    Bart Nessux
    Feb 23, 2004
  3. Vince
    Replies:
    12
    Views:
    768
    Martin Gregorie
    Jan 21, 2008
  4. John Crichton
    Replies:
    6
    Views:
    282
    John Crichton
    Jul 12, 2010
  5. Yakov

    DBI; error in $handle->execute()

    Yakov, Dec 4, 2007, in forum: Perl Misc
    Replies:
    1
    Views:
    86
    J. Gleixner
    Dec 4, 2007
Loading...

Share This Page