Re: copy a table with a long raw column

If you have perl with DBI, DBD::Oracle modules installed, this is trivial. Here is a sample script to copy table foo to table bar:

#!/usr/local/bin/perl
use DBI;
my($dbh,$sth,$sth1);

$dbh=DBI->connect('dbi:Oracle:orcl','scott','tiger') or die $DBI::errstr;
$dbh->{'RaiseError'}=1;
$dbh->{'LongReadLen'}=1_000_000; # limit to 1Mb
$dbh->{'LongTruncOk'}=0;

# fetch from table foo
$sth=$dbh->prepare("select file_name,content from foo"); $sth->execute;

# create new table bar
$sth1=$dbh->

        prepare("create table bar (file_name varchar2(64), content long raw)"); $sth1->execute;

# Populate the new table bar with content of table foo $sth1=$dbh->prepare("insert into bar values (?,?)"); while ( my($file,$content)=$sth->fetchrow_array ) {

        $sth1->bind_param(1,$file);
        $sth1->bind_param(2,$content,{ 'ora_type' => 24 });
        # BLOB => 113, CLOB => 112, LONG RAW => 24
        $sth1->execute;

$sth->finish;
$sth1->finish;
$dbh->disconnect;

Alexander Moeckel wrote:
>
> Now you have a problem ...
> I tried to copy really long and really long raw (>32KB) with PL/SQL or
> SQL - nothing works. Last week I've seen somewhere on the Oracle
> Homepage, that with 7.3.x writing really long columns is not supported.
> The only way to do this is OCI, but it's a hard way!
>
> Alex
>
> sofresud wrote:
> >
> > I would like to copy some rows of a table that contains a LONG RAW column to
> > another table.
> > this is on Oracle 7.3.4
> > i tried :
> > insert into DESTINATION Select Col1, Col2, Col3 from SOURCE where
> > Col1=MYFILTER;
> >
> > but as i thought it doesn't work ! cause the Col2 is a raw format.
> > do u have a idea how can i do a such thing, SQL or PL/SQL procedure ?
> >
> > thank you
Received on Fri Dec 03 1999 - 04:18:38 CST