execute Oracle anonymous procedure using perl

[hs]

Hello, I am trying to run a ORacle anonymous block using perl DBI, but I am receiving error as mentioned below. Please help..

sub runThis($procT) { 
    my $dbh1=DBI->connect("dbi:ODBC:$db","$usr","$pass",
        {RaiseError => 1 , 
        PrintError => 0}
        );    
    $sqlstmt1 = "BEGIN $procT END;";    
     $sth1 = $dbh1->do($sqlstmt1) or die $dbh1->errstr;    
    $dbh1 -> disconnect;
}

runThis("BEGIN NULL; END;");

--------------------------------------- Error message:

DBD::ODBC::db do failed: [Oracle][ODBC][Ora]ORA-06550: line 1, column 
+7:
PLS-00103: Encountered the symbol "END" when expecting one of the foll
+owing:

---------------------------- Thanks

[NetWallah]

Please use <code> tags , as described in Markup in the Monastery.

You are effectively passing the command:

BEGIN BEGIN NULL;END;END

To oracle. This seems to generate an Oracle syntax error.

To see what it does, run that using "sqlplus", and see if you get the same result.

[Anonymous Monk]

Thanks for response. I am using ODBC as see in the DBI statement.

[poj]

#!perl
use strict;
use DBD::Oracle;

runThis("BEGIN :now  := CURRENT_TIMESTAMP ; END;");

sub runThis { 
   my $procT = shift;
   my $dbh1=DBI->connect(
      'dbi:Oracle:host=localhost;sid=xe','user','password', 
      {RaiseError => 1 , PrintError => 0} );     
   
   my $now = '';   
   my $sth1 = $dbh1->prepare($procT); 
   
   $sth1->bind_param_inout(":now",\$now,0);
   $sth1->execute;
   $dbh1->disconnect; 
   print "time now is $now\n";
 }

poj

[Anonymous Monk]

Thanks for the response. I tried this but I am receiving below error message. DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1 (DBD ERROR: OCIStmtExecute) for Statement "BEGIN :now := CURRENT_TIMESTAMP ; END;" with ParamValues: :now='' at t.pl line 16.

[poj]

What are your OS, Perl and Oracle versions ?

poj

[mje]

In ODBC you use '{call procname (arguments)}'.