Turing on $dbh->trace(...) would have shown you the problem quickly. Note to dbi-users people: suggest using trace more often! :-)
Tim. On Mon, Apr 15, 2002 at 05:49:30PM +0530, Rahul R M Desai wrote: > Hi Tim, > > You were right. The problem was in quoting the $timestamp. The script is > going through fine now. > Thanks for the help. > > with regards, > Rahul > > ----- Original Message ----- > From: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ; > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > Sent: Monday, April 15, 2002 5:41 PM > Subject: RE: Problem Calling a stored procedure - Urgent > > > I would suggest that you approach this problem by simplifying it as much > as possible. For example, try putting your parameter inside the prepare > statement and see if it works. If it does, then you know that there's a > problem with the way that you're using bind_params. > > I notice two things about the bind params statement: 1) using the ':' > for a placeholder does not work for all database drivers. Better to use > the '?' method. 2) You're probably better off not quoting $timestamp > when it's used as a bind parameter as it is passed to the database > seperately from the SQL statement. > > If you're going to be using the Perl DBI module a lot, then you should > consider getting the excellent book "Programming the Perl DBI" published > by O'Reilly. > > Regards, > > Tim Helck > > -----Original Message----- > From: Rahul R M Desai [ mailto:[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> ] > Sent: Monday, April 15, 2002 7:33 AM > To: [EMAIL PROTECTED] > Cc: Karthik Gurumurthy > Subject: Problem Calling a stored procedure - Urgent > > > Hi all, > > I am very new to Perl and the DBI package. > I have a no. of procedures which call each other on the Oracle DB. From > the SQL prompt they are running properly. But when I run the same > through a perl script, it gives some errors. > > The code that i have written is : > ------------------------------------------------------------------------ > > ------------ > my $dbh = DBI->connect("dbi:Oracle:$db", > $username, $password, {AutoCommit => 0} ) > or die "Database connection not made: $DBI::errstr"; > $timestamp = time(); > eval { > my $func = $dbh->prepare("BEGIN do_complete_migration(:parameter1); > END;"); > $func->bind_param(":parameter1", q{$timestamp}); > $func->execute; > $dbh>commit; > }; > print "Done...\n"; > > $dbh->disconnect(); > ------------------------------------------------------------------------ > > ------------ > > When I run this script, I get the following error : > ------------------------------------------------------------------------ > > ----------------------------------- > DBD::Oracle::st execute failed: ORA-00911: invalid character > ORA-06512: at "APP_OWNER.INSERT_ATTR", line 21 // There is a Execute > Immediate statement on this line > ORA-06512: at "APP_OWNER.MIGRATE_LEADS", line 18 // Calls proc > Insert_attr > ORA-06512: at "APP_OWNER.DO_COMPLETE_MIGRATION", line 6 // Calls proc > migrate_leads > ORA-06512: at line 1 (DBD: oexec error) at mcs.pl line 85, <IN> line > 109. > Done... > ------------------------------------------------------------------------ > > ---------------------------------------- > > I am not able to figure out if there is syntax problem in the perl > script. I am sure that there i no problem in the procedure as they run > properly if I execute the proc fron the sql prompt. Please help if > somebody can figure this out. > > > Thanks and regards, > Rahul Desai >
