472,336 Members | 1,158 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,336 software developers and data experts.

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


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.     ...
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.


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

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);
  9.   BEGIN
  10.      v_stmt := ' INSERT INTO my_table ( pk, clob_col ) ' ||
  11.                ' VALUES (:1, :2)';
  13.      EXECUTE IMMEDIATE v_stmt
  14.              USING     pk, col_val;
  16.      COMMIT;
  17.   END;
  19. --------------------------------------------

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

Welcome to TSDN!!

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

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

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

Similar topics

by: Jeff | last post by:
I am getting Unable to find operator in query string. Query string currently is INSERT INTO Results (Name,Email,Comments,File) VALUES...
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and...
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get...
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second...
by: Christian Maier | last post by:
Hi After surfing a while I have still trouble with this array thing. I have the following function and recive a Segmentation fault, how must I...
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you...
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe...
by: pitjpz | last post by:
We have moved our Database to another server. The server it was on used SQL 4 and the new one its on now uses SQL5 the only problem we can find...
by: jonathan184 | last post by:
The code seems to be working fine for some records but I am thinking it is finding some other records with special characters and so on. I am looking...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.