open cursor with hold question

[Liu, Jenny]

Hi,

How could I declare an open cursor with hold in a perl DBI program?

I have to do a commit for every 30000 insertion to avoid long transaction.
But the commit will close my 
($patkey,$extid) = $sth->fetchrow_array) cursor.  I got an SQL error 400.

Please advise.

Thanks


Jenny



The following is part of the program:


        $Sql_stmt = qq(insert into misys_ifc:ifc_ob_assoc_det  values \( 0,
CURRENT, ?, ?, ?\) );
        my $sth2 = $dbh->prepare($Sql_stmt);

        $Sql_stmt = qq(select  patkey, ext_id from pat_ext_info where
accttype='P'
                                        and co_source=?);
        $sth = $dbh->prepare($Sql_stmt with hold);
        $sth->execute($company);


        my $count=0;
      while (($patkey,$extid) = $sth->fetchrow_array){

                $sth2->execute($patkey, $extid, $p_serial_key);
                $count++;
                if($count%30000==0){
                       $dbh->commit;   #########  THIS IS THE ONE GIVE ME
TROUBLE
                }
        }
        $dbh->commit;
        undef $sth1;
        undef $sth2;