Solved

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

Posted on 1998-08-09
4
242 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:dsweeney
  • 2
4 Comments
 
LVL 4

Expert Comment

by:mitek
ID: 1209167
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";
  &ora_close($c);
================================================================

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";
  &ora_close($c);

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.

 
0
 
LVL 4

Expert Comment

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

0
 

Author Comment

by:dsweeney
ID: 1209169
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) :

#HERES WHERE WE CONNECT TO THE DATABASE
#------------------------------------------------------------------------
if (!($db = new Win32::ODBC($DSN))){
      print "Content-type: text/html\n\n Error connecting to $DSN\n";
      print "Error: " . Win32::ODBC::Error() . "\n";
      exit;
}
$statement = "UPDATE tickets SET comments = '$body'";
if ($db->Sql($statement)){
      sqlerror();
}

$db->Close();

0
 

Accepted Solution

by:
matthewallum earned 300 total points
ID: 1209170
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?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now