Re: Export of Specific Rows (ugh Long Raw columns)

Doug Anderson wrote:
>
> Does anyone know of a way to export specific rows from
> a table, not the entire table? There is one special problem:
> LONG RAW columns.
>
> I have looked into copying rows into a temporary table, then
> exporting that table. The CREATE TABLE ... AS SELECT...
> seems to be fine for normal tables, but those with a LONG RAW
> column can't use this syntax. I wrote a PL/SQL procedure to
> handle copying the BLOBs but this causes large amounts of
> redo-log and therefore archive-log space to be used. If only
> the "CREATE TABLE ... UNRECOVERABLE AS SELECT
> ..." would work for BLOBs !
>
> Any ideas?
>
> - Doug Anderson
> Oracle Database Administrator
> DAtheDBA_at_mindspring.com

The SQL*Plus COPY command might work. It is not documented in the Oracle SQL Reference, but is documented in the SQL*Plus guide and Oracle DBA Handbook (Kevin Loney, Oracle Press/McGraw Hill). It can copy LONG datatype columns & allows one to specify batches using the SQL*Plus copycommit & arraysize commands.

Syntax:

COPY FROM
username/password_at_connect_string
[APPEND|CREATE|INSERT|REPLACE]
<table_name>
USING <subquery>;

Except for the last line, each line in the COPY command must be terminated with a dash (-).

example of replicating a table on another server (after Loney):

set copycommit 1
set arraysize 1000
COPY FROM hr/hrpswd_at_tns:hris -
CREATE employee -
USING -
SELECT * FROM employee; Received on Tue Mar 11 1997 - 00:00:00 CST