help with Perl code to insert a long string via stored procedure

hi,

I have the following Perl code that inserts a string to an Oracle DB via a stored procedure:

use strict;
BEGIN {
$ENV{'TNS_ADMIN'} = '/auto/engweb/oracle/sqlnet';
$ENV{'ORACLE_HOME'} = '/usr/packages/dbdoracle/9.2.0';
}
use lib qw( /usr/packages/dbdoracle/9.2.0 );
use DBI;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
...
1 $pk = "";
2 $string = "";
3 $stmt = 'BEGIN my_package.my_proc(:primary_key, :value); END;';
# see near bottom for details of "my_proc"
4 $sth = $dbh->prepare ($stmt);
5 $sth->bind_param(":primary_key", $pk);
6 $sth->bind_param(":value", "$string");
7 $sth->execute();
...

for strings < 32512 chars, the code works fine.

For strings >= 32513 chars, I see the following error messages. How do I resolve these errors?

-------------------------------

DBD::Oracle::st execute failed: ORA-01460: unimplemented or unreasonable conversion requested (DBD ERROR: OCIStmtExecute)

-------------------------------

I changed line 6 to:

$sth->bind_param(":value", "$string", SQL_LONGVARCHAR );

but saw the same error.

-------------------------------

I then tried:

$sth->bind_param(":value", "$string", SQL_LONGVARBINARY);

which produced:

DBD::Oracle::st execute failed: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments

-------------------------------

Next, I tried:

$sth->bind_param(":value", "$string", { ora_type => ORA_CLOB } );

but got:

DBD::Oracle::st execute failed: ORA-01403: no data found (DBD ERROR: LOB refetch
attempted for unsupported statement type (see also ora_auto_lob attribute))


Is there anything else I can try? Any pointer is appreciated.


--Andrew

==================================================================================

my stored proc is very simple:

PROCEDURE my_proc
(
pk IN VARCHAR2,
col_val IN CLOB
)
IS
v_stmt VARCHAR2(100);

BEGIN
v_stmt := ' INSERT INTO my_table ( pk, clob_col ) ' ||
' VALUES (:1, :2)';

EXECUTE IMMEDIATE v_stmt
USING pk, col_val;

COMMIT;
END;

--------------------------------------------

the table is:

CREATE TABLE my_table
(
pk VARCHAR2 (100) NOT NULL,
clob_col CLOB,
);