RE: Copying longs in Perl

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 5 Oct 2009 15:43:34 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F226C99C0_at_AAPQMAILBX02V.proque.st>

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>
XMLSTORE_at_xmldb64> desc test_lob_Copy

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

Hope that helps,

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 05 2009 - 14:43:34 CDT