468,167 Members | 1,965 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,167 developers. It's quick & easy.

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

hi,

I have the following Perl code that inserts a string to an Oracle DB via a stored procedure:
Expand|Select|Wrap|Line Numbers
  1.      #!/usr/local/bin/perl   ## Perl v5.8.6 built for sun4-solaris
  2.      use strict;
  3.      BEGIN {
  4.          $ENV{'TNS_ADMIN'}   = '/auto/engweb/oracle/sqlnet';
  5.          $ENV{'ORACLE_HOME'} = '/usr/packages/dbdoracle/9.2.0';
  6.      }
  7.      use lib qw( /usr/packages/dbdoracle/9.2.0 );
  8.      use DBI;
  9.      use DBI qw(:sql_types);
  10.      use DBD::Oracle qw(:ora_types);
  11.      ...
  12.   1  $pk = "<some_unique_value>";
  13.   2  $string = "<a very long string....>";  
  14.   3  $stmt = 'BEGIN my_package.my_proc(:primary_key, :value); END;';
  15.        #   see near bottom for details of "my_proc"
  16.   4  $sth = $dbh->prepare ($stmt);
  17.   5  $sth->bind_param(":primary_key", $pk);
  18.   6  $sth->bind_param(":value", "$string");
  19.   7  $sth->execute();
  20.     ...
  21.  
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:
Expand|Select|Wrap|Line Numbers
  1.   PROCEDURE my_proc
  2.   (
  3.      pk            IN VARCHAR2,
  4.      col_val       IN CLOB
  5.   )
  6.   IS
  7.      v_stmt        VARCHAR2(100);
  8.  
  9.   BEGIN
  10.      v_stmt := ' INSERT INTO my_table ( pk, clob_col ) ' ||
  11.                ' VALUES (:1, :2)';
  12.  
  13.      EXECUTE IMMEDIATE v_stmt
  14.              USING     pk, col_val;
  15.  
  16.      COMMIT;
  17.   END;
  18.  
  19. --------------------------------------------
  20.  

the table is:
Expand|Select|Wrap|Line Numbers
  1.  
  2.   CREATE TABLE my_table
  3.   (
  4.     pk                      VARCHAR2 (100) NOT NULL,
  5.     clob_col                CLOB
  6.   );
  7.  
  8.  
Jan 7 '08 #1
3 3716
amitpatel66
2,367 Expert 2GB
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?
Jan 8 '08 #2
amitpatel66
2,367 Expert 2GB
Hi Andrew,

Welcome to TSDN!!

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

Thanks
MODERATOR
Jan 8 '08 #3
debasisdas
8,127 Expert 4TB
Instead of using CLOB try to use LONG.
Jan 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff | last post: by
15 posts views Thread by Jay | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
reply views Thread by SOI_0152 | last post: by
1 post views Thread by pitjpz | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.