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

How to update long field in oracle 7.3?

Hi,
    I am writing a program which needs the program to update long field in the table.
    I am using oracle 7.3 on solaris 2.6 and dbi module to connect to the oracle data base from the perl program.
    when my input field is very small then i can update Long field properly but when my input variable grows to 4000 bytes or more it just aborts (doesn't update the long field).
    I tried this on oracle 8 on windows NT 4.0 Then it works perfectly but does not work on the oracle 7.3 on solaris 2.6.
0
tambde
Asked:
tambde
  • 3
  • 2
1 Solution
 
mjswartCommented:
I had the same problem, and I had to give up on perl and use a procedure using PL/SQL when I could guarantee that the long field was under 2 megs and I had to use the Pro C compiler when the long field could be over 2 Megs.
0
 
ventolinCommented:
# never give up.

# here dbh has your connection:

insert_long($sql, \$long_string);

sub insert_long {
my ($sql, $long_string);
($sql, $file_ref) = @_;
$ab = $dbh->prepare($sql);
my $rc = $ab->bind_param(1, $$long_string, 24);
$rc = $ab->execute;
$rc = $dbh->commit;
$rc = $ab->finish;
return($rc);
}
0
 
ventolinCommented:
oops... typo.

change:

($sql, $file_ref) = @_;

to:

($sql, $long_string) = @_;

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tambdeAuthor Commented:
$sql="update publication set
due_date='2000-1-22|2000-1-23|2000-1-24|2000-1-25|2000-1-26|2000-1-27|2000-1-28|2000-1-29|2000-1-30|2000-1-31|2000-2-1|2000-2-2|2000-2-3|2000-2-4|2000-2-5|2000-2-6|2000-2-7|2000-2-8|2000-2-9|2000-2-10|2000-2-11|2000-2-12|2000-2-13|2000-2-14|2000-2-15|2000-2-16|2000-2-17|2000-2-18|2000-2-19|2000-2-20|2000-2-21|2000-2-22|2000-2-23|2000-2-24|2000-2-25|2000-2-26|2000-2-27|2000-2-28|2000-2-29|2000-3-1|2000-3-2|2000-3-3|2000-3-4|2000-3-5|2000-3-6|2000-3-7|2000-3-8|2000-3-9|2000-3-10|2000-3-11|2000-3-12|2000-3-13|2000-3-14|2000-3-15|2000-3-16|2000-3-17|2000-3-18|2000-3-19|2000-3-20|2000-3-21|2000-3-22|2000-3-23|2000-3-24|2000-3-25|2000-3-26|2000-3-27|2000-3-28|2000-3-29|2000-3-30|2000-3-31|2000-4-1|2000-4-2|2000-4-3|2000-4-4|2000-4-5|2000-4-6|2000-4-7|2000-4-8|2000-4-9|2000-4-10|2000-4-11|2000-4-12|2000-4-13|2000-4-14|2000-4-15|2000-4-16|2000-4-17|2000-4-18|2000-4-19|2000-4-20|2000-4-21|2000-4-22|2000-4-23|2000-4-24|2000-4-25|2000-4-26|2000-4-27|2000-4-28|2000-4-29|2000-4-30|2000-5-1|2000-5-2|2000-5-3|2000-5-4|2000-5-5|2000-5-6|2000-5-7|2000-5-8|2000-5-9|2000-5-10|2000-5-11|2000-5-12|2000-5-13|2000-5-14|2000-5-15|2000-5-16|2000-5-17|2000-5-18|2000-5-19|2000-5-20|2000-5-21|2000-5-22|2000-5-23|2000-5-24|2000-5-25|2000-5-26|2000-5-27|2000-5-28|2000-5-29|2000-5-30|2000-5-31|2000-6-1|2000-6-2|2000-6-3|2000-6-4|2000-6-5|2000-6-6|2000-6-7|2000-6-8|2000-6-9|2000-6-10|2000-6-11|2000-6-12|2000-6-13|2000-6-14|2000-6-15|2000-6-16|2000-6-17|2000-6-18|2000-6-19|2000-6-20|2000-6-21|2000-6-22|2000-6-23|2000-6-24|2000-6-25|2000-6-26|2000-6-27|2000-6-28|2000-6-29|2000-6-30|2000-7-1|2000-7-2|2000-7-3|2000-7-4|2000-7-5|2000-7-6|2000-7-7|2000-7-8|2000-7-9|2000-7-10|2000-7-11|2000-7-12|2000-7-13|2000-7-14|2000-7-15|2000-7-16|2000-7-17|2000-7-18|2000-7-19|2000-7-20|2000-7-21|2000-7-22|2000-7-23|2000-7-24|2000-7-25|2000-7-26|2000-7-27|2000-7-28|2000-7-29|2000-7-30|2000-7-31|2000-8-1|2000-8-2|2000-8-3|2000-8-4|2000-8-5|2000-8-6|2000-8-7|2000-8-8|2000-8-9|2000-8-10|2000-8-11|2000-8-12|2000-8-13|2000-8-14|2000-8-15|2000-8-16|2000-8-17|2000-8-18|2000-8-19|2000-8-20|2000-8-21|2000-8-22|2000-8-23|2000-8-24|2000-8-25|2000-8-26|2000-8-27|2000-8-28|2000-8-29|2000-8-30|2000-8-31|2000-9-1|2000-9-2|2000-9-3|2000-9-4|2000-9-5|2000-9-6|2000-9-7|2000-9-8|2000-9-9|2000-9-10|2000-9-11|2000-9-12|2000-9-13|2000-9-14|2000-9-15|2000-9-16|2000-9-17|2000-9-18|2000-9-19|2000-9-20|2000-9-21|2000-9-22|2000-9-23|2000-9-24|2000-9-25|2000-9-26|2000-9-27|2000-9-28|2000-9-29|2000-9-30|2000-10-1|2000-10-2|2000-10-3|2000-10-4|2000-10-5|2000-10-6|2000-10-7|2000-10-8|2000-10-9|2000-10-10|2000-10-11|2000-10-12|2000-10-13|2000-10-14|2000-10-15|2000-10-16|2000-10-17|2000-10-18|2000-10-19|2000-10-20|2000-10-21|2000-10-22|2000-10-23|2000-10-24|2000-10-25|2000-10-26|2000-10-27|2000-10-28|2000-10-29|2000-10-30|2000-10-31|2000-11-1|2000-11-2|2000-11-3|2000-11-4|2000-11-5|2000-11-6|2000-11-7|2000-11-8|2000-11-9|2000-11-10|2000-11-11|2000-11-12|2000-11-13|2000-11-14|2000-11-15|2000-11-16|2000-11-17|2000-11-18|2000-11-19|2000-11-20|2000-11-21|2000-11-22|2000-11-23|2000-11-24|2000-11-25|2000-11-26|2000-11-27|2000-11-28|2000-11-29|2000-11-30|2000-12-1|2000-12-2|2000-12-3|2000-12-4|2000-12-5|2000-12-6|2000-12-7|2000-12-8|2000-12-9|2000-12-10|2000-12-11|2000-12-12|2000-12-13|2000-12-14|2000-12-15|2000-12-16|2000-12-17|2000-12-18|2000-12-19|2000-12-20|2000-12-21|2000-12-22|2000-12-23|2000-12-24|2000-12-25|2000-12-26|2000-12-27|2000-12-28|2000-12-29|2000-12-30|2000-12-31|'
where pub_name='toi di1'";
$ab = $dbh->prepare($sql);
my $rc = $ab->bind_param(1, $temp_due_date, 24);
$rc = $ab->execute;
$rc = $dbh->commit;
I used above code but still it doesn't work.
0
 
tambdeAuthor Commented:
$sql="update publication set
due_date='2000-1-22|2000-1-23|2000-1-24|2000-1-25|2000-1-26|2000-1-27|2000-1-28|2000-1-29|2000-1-30|2000-1-31|2000-2-1|2000-2-2|2000-2-3|2000-2-4|2000-2-5|2000-2-6|2000-2-7|2000-2-8|2000-2-9|2000-2-10|2000-2-11|2000-2-12|2000-2-13|2000-2-14|2000-2-15|2000-2-16|2000-2-17|2000-2-18|2000-2-19|2000-2-20|2000-2-21|2000-2-22|2000-2-23|2000-2-24|2000-2-25|2000-2-26|2000-2-27|2000-2-28|2000-2-29|2000-3-1|2000-3-2|2000-3-3|2000-3-4|2000-3-5|2000-3-6|2000-3-7|2000-3-8|2000-3-9|2000-3-10|2000-3-11|2000-3-12|2000-3-13|2000-3-14|2000-3-15|2000-3-16|2000-3-17|2000-3-18|2000-3-19|2000-3-20|2000-3-21|2000-3-22|2000-3-23|2000-3-24|2000-3-25|2000-3-26|2000-3-27|2000-3-28|2000-3-29|2000-3-30|2000-3-31|2000-4-1|2000-4-2|2000-4-3|2000-4-4|2000-4-5|2000-4-6|2000-4-7|2000-4-8|2000-4-9|2000-4-10|2000-4-11|2000-4-12|2000-4-13|2000-4-14|2000-4-15|2000-4-16|2000-4-17|2000-4-18|2000-4-19|2000-4-20|2000-4-21|2000-4-22|2000-4-23|2000-4-24|2000-4-25|2000-4-26|2000-4-27|2000-4-28|2000-4-29|2000-4-30|2000-5-1|2000-5-2|2000-5-3|2000-5-4|2000-5-5|2000-5-6|2000-5-7|2000-5-8|2000-5-9|2000-5-10|2000-5-11|2000-5-12|2000-5-13|2000-5-14|2000-5-15|2000-5-16|2000-5-17|2000-5-18|2000-5-19|2000-5-20|2000-5-21|2000-5-22|2000-5-23|2000-5-24|2000-5-25|2000-5-26|2000-5-27|2000-5-28|2000-5-29|2000-5-30|2000-5-31|2000-6-1|2000-6-2|2000-6-3|2000-6-4|2000-6-5|2000-6-6|2000-6-7|2000-6-8|2000-6-9|2000-6-10|2000-6-11|2000-6-12|2000-6-13|2000-6-14|2000-6-15|2000-6-16|2000-6-17|2000-6-18|2000-6-19|2000-6-20|2000-6-21|2000-6-22|2000-6-23|2000-6-24|2000-6-25|2000-6-26|2000-6-27|2000-6-28|2000-6-29|2000-6-30|2000-7-1|2000-7-2|2000-7-3|2000-7-4|2000-7-5|2000-7-6|2000-7-7|2000-7-8|2000-7-9|2000-7-10|2000-7-11|2000-7-12|2000-7-13|2000-7-14|2000-7-15|2000-7-16|2000-7-17|2000-7-18|2000-7-19|2000-7-20|2000-7-21|2000-7-22|2000-7-23|2000-7-24|2000-7-25|2000-7-26|2000-7-27|2000-7-28|2000-7-29|2000-7-30|2000-7-31|2000-8-1|2000-8-2|2000-8-3|2000-8-4|2000-8-5|2000-8-6|2000-8-7|2000-8-8|2000-8-9|2000-8-10|2000-8-11|2000-8-12|2000-8-13|2000-8-14|2000-8-15|2000-8-16|2000-8-17|2000-8-18|2000-8-19|2000-8-20|2000-8-21|2000-8-22|2000-8-23|2000-8-24|2000-8-25|2000-8-26|2000-8-27|2000-8-28|2000-8-29|2000-8-30|2000-8-31|2000-9-1|2000-9-2|2000-9-3|2000-9-4|2000-9-5|2000-9-6|2000-9-7|2000-9-8|2000-9-9|2000-9-10|2000-9-11|2000-9-12|2000-9-13|2000-9-14|2000-9-15|2000-9-16|2000-9-17|2000-9-18|2000-9-19|2000-9-20|2000-9-21|2000-9-22|2000-9-23|2000-9-24|2000-9-25|2000-9-26|2000-9-27|2000-9-28|2000-9-29|2000-9-30|2000-10-1|2000-10-2|2000-10-3|2000-10-4|2000-10-5|2000-10-6|2000-10-7|2000-10-8|2000-10-9|2000-10-10|2000-10-11|2000-10-12|2000-10-13|2000-10-14|2000-10-15|2000-10-16|2000-10-17|2000-10-18|2000-10-19|2000-10-20|2000-10-21|2000-10-22|2000-10-23|2000-10-24|2000-10-25|2000-10-26|2000-10-27|2000-10-28|2000-10-29|2000-10-30|2000-10-31|2000-11-1|2000-11-2|2000-11-3|2000-11-4|2000-11-5|2000-11-6|2000-11-7|2000-11-8|2000-11-9|2000-11-10|2000-11-11|2000-11-12|2000-11-13|2000-11-14|2000-11-15|2000-11-16|2000-11-17|2000-11-18|2000-11-19|2000-11-20|2000-11-21|2000-11-22|2000-11-23|2000-11-24|2000-11-25|2000-11-26|2000-11-27|2000-11-28|2000-11-29|2000-11-30|2000-12-1|2000-12-2|2000-12-3|2000-12-4|2000-12-5|2000-12-6|2000-12-7|2000-12-8|2000-12-9|2000-12-10|2000-12-11|2000-12-12|2000-12-13|2000-12-14|2000-12-15|2000-12-16|2000-12-17|2000-12-18|2000-12-19|2000-12-20|2000-12-21|2000-12-22|2000-12-23|2000-12-24|2000-12-25|2000-12-26|2000-12-27|2000-12-28|2000-12-29|2000-12-30|2000-12-31|'
where pub_name='toi di1'";
$ab = $dbh->prepare($sql);
my $rc = $ab->bind_param(1, $temp_due_date, 24);
$rc = $ab->execute;
$rc = $dbh->commit;
I used above code but still it doesn't work.
Please help.
0
 
ventolinCommented:
# the code works.

$sql =qq!update production set due_date=:1 where pub_name='toi di1'!;

# put all those pipe delimited dates in $long_string

insert_long($sql,\$long_string);


__END__

looks like you need a better design for your data. you should have a seperate table for all of those dates. you shouldn't have to have pipe delimited data... this is the reason for a database.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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