On Mon, 26 Aug 2002 20:52:39 +0530 "Sundarraj, Senthil Kumar (Cognizant)"
<[EMAIL PROTECTED]> wrote:
> I have a stored Procedure like
>
> package StoredProcedures ;
> #use strict;
> use DBI;
> use DBD::Oracle qw(:ora_types );
> my ($key,@row,$dbh,$rv,$stmt);
>
> sub InsertBCOBills {
>
> my($rampii,$rampii_iss_date,$baid,$baid_iss_date,$complex,$document)=@_;
> eval {
> $stmt = $dbh->prepare(qq{
> BEGIN
> StoredProcedure.InsertBCOBills_sp(:1,:2,:3,:4,:5,:6);
> END;
> }) or die db_error("Statement Preparation for InsertBCOBills
> fails");
If you will be doing this a lot, you may want to prepare() at the same time
you connect(). You already are declaring $stmt in the same my() as $dbh.
You should also include $DBI::errstr in the die() string or you won't know
why prepare() failed.
> $stmt->bind_param(6,$document,{ora_type=>ORA_CLOB,ora_field=>'xmlbillobject'
> });
I have seen some recent reports where the {ora_type, ora_field} argument is
not necessary and may cause an error. Try this without it and see what
happens. That may still leave a problem if $document is more than 4K, so
test it with larger values.
> $stmt->execute;
> $dbh->commit();
You are not doing any error checking, that is not a good idea. Unless you
turn on RaiseError these statement will not throw an error.
> };
> if($@){
> warn "Execution of Stored Procedure InsertBCOBills fails
> $dbh->errstr";
In a string context '$dbh->errstr' is interpolated as "$dbh" . "->errstr";
use $DBI::errstr instead.
> $dbh->rollback;
> }
> $stmt->finish;
finish() is inappropriate for non-SELECT SQL.
> }
>
> In my oracle database the stored procedure is like
>
> create or replace PACKAGE StoredProcedure IS
> PROCEDURE InsertBCOBills_sp(p_RAMPII IN Number,
> p_RAMPIIBillIssueDate IN
> Date, p_BAID IN Number, p_BAIDBillIssueDate IN date, p_complex IN char,
> p_XMLObject IN CLOB);
> END;
> When i invoke the subroutine InsertBCOBills,it says
> DBD::Oracle::st execute failed: (DBD ERROR: LOB refetch attempted
> for unsupported statement type).What could be the problem?
The ora_field name you are giving does not match the parameter name and
ora_field may not be allowed for BEGIN blocks.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
