RE: Copying longs in Perl

From: Bobak, Mark <>
Date: Mon, 5 Oct 2009 15:43:34 -0400
Message-ID: <>

Hi Jason,

There's no need to maintain data as a long datatype, is there? Convert it to LOB when you create the table. Testcase follows:
XMLSTORE_at_xmldb64> create table test_long(a number, b long);

Table created.

XMLSTORE_at_xmldb64> insert into test_long values(1,'Hello world!');

1 row created.

XMLSTORE_at_xmldb64> commit;

Commit complete.

XMLSTORE_at_xmldb64> create table test_long_copy as select * from test_long; create table test_long_copy as select * from test_long


ERROR at line 1:
ORA-00997: illegal use of LONG datatype

XMLSTORE_at_xmldb64> create table test_lob_copy as select a,to_lob(b) b from test_long;

Table created.

XMLSTORE_at_xmldb64> desc test_lob_Copy

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  CLOB

Hope that helps,


From: [] On Behalf Of Jason Heinrich Sent: Monday, October 05, 2009 3:18 PM
To: Oracle List
Subject: Copying longs in Perl

I have a perl script that manages audit data, and one of the things it does is create a copy of the sys.aud$ table in a separate tablespace for archiving purposes. I'm attempting to add the sys.fga_log$ table to this script so I can manage the fine-grained audit data as well, but it contains a long column so I can't just do a "create table as select" or I'll get an ORA-00997. The standard answer to this problem is to use the copy command in sqlplus, but I'd prefer to keep the code in perl. Is this possible?


Jason Heinrich

-- Received on Mon Oct 05 2009 - 14:43:34 CDT

Original text of this message