help with Perl code to insert a long string (> 32512 chars) via stored procedure

[andrewkl]

hi,

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

     #!/usr/local/bin/perl   ## Perl v5.8.6 built for sun4-solaris
     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 = "<some_unique_value>";
  2  $string = "<a very long 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
  );
 
 

[amitpatel66]

From where you are reading these lines of data? from a file or some where else?
Is it possible for you to read the data 32512 length string and do an insert in oder to avoid this error?

[amitpatel66]

Hi Andrew,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum

Thanks
MODERATOR

[debasisdas]

Instead of using CLOB try to use LONG.