Re: problem: insert long fields into table

From: Fred Ruffet <fruffet_at_kaptech.com>
Date: Tue, 25 May 1999 14:54:12 +0200
Message-ID: <7ie6hr$1s1$1@gatekeeper.ornano.kapt.com>

OK. The following perl procs don't exactly answer the question, but they're so near... I wrote these while trying to insert and retrieve photos into long raws fields. All you need to change, I believe, is the datatype number of long raw to long (24 to 8).
Excuse me for the french variables names, but... I'm french...

**Insert**

sub oracle_insert_photo {
 # Args : Filename to read
 # Primary key of the record to insert

#init

$data="";
$fichier_image="<$_[0]";
$length_to_read=50;
$actual_pos=0;
$length=0;

#read the file
 open fichier_image or die "Je ne peux pas ouvrir $_[0]";  while (!(eof fichier_image)) {

     $length=read fichier_image,$data,$length_to_read,$actual_pos;
     $actual_pos+=$length;

 }
 close fichier_image;

#insert the record
$insert="insert into tro_photo (clef,photo) values (:1,:2)";
$sth = $dbh->prepare($insert) || die $dbh->errstr;
$attrib{'ora_type'}=24; # Type 24 = long raw
$sth->bind_param(1,$_[1]) || die $dbh->errstr;
$sth->bind_param(2,$data,\%attrib) || die $dbh->errstr;
$sth->execute || die$dbh->errstr;
$sth->finish;

}

**select**

sub oracle_select_photo {
 # Args : Filename to create
 # Primary Key of the record to read

#select
$query="select photo from tro_photo where clef=$_[1]";
$sth=$dbh->prepare($query) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;

#read it by pieces

my $data="";
 my $lump=4096;
 my $offset=0;
 while (1) {

     my $frag=$sth->blob_read(0,$offset,$lump);
     last unless defined $frag;
     my $ll=length $frag;
     last unless $ll;
     $data.=$frag;
     $offset+=$ll;

 }
$sth->finish;

#write the file
$fichier_image=">".$_[0];
 open fichier_image or die "Je ne peux pas ouvrir $_[0]";  print fichier_image $data;
 close fichier_image;
}

Regards,

Fred

Rüdiger J. Schulz a écrit dans le message <374A9295.572C8BA0_at_berlin.de>...

>how can I insert long fields into oracle tables
>with sql*loader or perl or pl/sql
>
>thanx in advance
>
>--
>b  e  r  l  i  n  .  d  e  -  der onlinedienst der stadt
>________________________________________________________
>Primus-Online Berlin-Brandenburg GmbH & Co. KG
>debis Haus am Potsdamer Platz
>10875 Berlin
>
>Rüdiger J. Schulz
>Technik
>
>tel: +49 (30) 2554-1497
>fax: +49 (30) 2554-3725
>email: r.schulz_at_berlin.de
>homepage: http://www.berlin.de

>
>

Received on Tue May 25 1999 - 07:54:12 CDT