Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

updating more than 2K of data to a LONG datatype Oracle field using Perl/ODBC module

Hello, I am trying to update a datatype LONG field in an Oracle table (can hold 2G of char data), but the SQL UPDATE statement will only allow 2K thru a quoted string (eg UPDATE tickets SET comments = '$body'  -- and $body > 2k of data).  I have heard something about binding a variable to a database column thru some other mechanism than SQL, but the only examples I have ever seen are thru C call interface.  There must be some way to do it with Perls ODBC module for windows 32.
  • 2
1 Solution
I once did it this way:

  $c = &ora_open($l,"BEGIN dda.dda_alist(:1,:2,:3); END;");
  $c->bind_param_inout(1, \$tab_name,32) || die "bind_param: $ora_errstr";
  $c->bind_param_inout(2, \$alist,32000) || die "bind_param: $ora_errstr";
  $c->bind_param_inout(3, \$rec_id,255) || die "bind_param_out: $ora_errstr";
  $c->execute || die "execute: $ora_errstr";

It looks like it CAN handle 32K variables, but I didn't really pushed it to the limit to find out how long a variable can get.

So, the solution would look like something like this:

  $c = &ora_open($l,"BEGIN UPDATE tab1 SET long_col = :1 WHERE sid = :2; END;");
  $c->bind_param_inout(1, \$tab_name,32000) || die "bind_param: $ora_errstr";
  $c->bind_param_inout(2, \$alist,32) || die "bind_param: $ora_errstr";
  $c->execute || die "execute: $ora_errstr";

This is just like binding sql variables in Pro*C, but within the Perl engine.

Let me know if it worked for you, and if not, reopen the question. Also, if it did, tell me how long a variable can get if one uses this technology.

Also ... It just came to me ...
bind_param_inout may work only for stored procedures, for variables it maybe just bind_param or something ...

dsweeneyAuthor Commented:
I am decent at perl but new to programming, need more than code fragments.  I am upping it to 300 but I need more info/example.  Here is my code fragment to do it the old (2K quoted string) way in case it helps ($body is the > 2k body of the text I want to store, $DSN is my ODBC connect string) :

if (!($db = new Win32::ODBC($DSN))){
      print "Content-type: text/html\n\n Error connecting to $DSN\n";
      print "Error: " . Win32::ODBC::Error() . "\n";
$statement = "UPDATE tickets SET comments = '$body'";
if ($db->Sql($statement)){


I know with SQL server you can use an 'UPDATETEXT' statement rather than a normal UPDATE for large amounts of data maybe theres something similar on Oracle?

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now