OraFAQ Forum: Server Administration » IGNORE COMMENTS IN EXTERNAL TABLE

Home » RDBMS Server » Server Administration » IGNORE COMMENTS IN EXTERNAL TABLE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

IGNORE COMMENTS IN EXTERNAL TABLE [message #214413]

Tue, 16 January 2007 06:09

suvv
Messages: 17
Registered: October 2006

Junior Member

I currently have a partitioned table, split by month going back some 7 years.
The business have now agreed to archive off some of the data but to have it
available if required. I therefore intend to select month by month, the data
to a flat file and make use of external tables to provide the data for the
business if required. Then to remove the months partition thus releaseing
space back to the database. Each months data is only about 100bytes long, but
there could be about 15million rows.
I can successfully create the external table and read the data.
However I would like to place a header in the spooled file but the only method
I have found to ignore the header is to use the "SKIP" parameter. I would
rather not use this as it hard codes the number of lines I reserve for a
header.
Is there another method to add a comment to a external tables file and have the
selects on the file ignore the comments?
Could I use "LOAD WHEN ( column1 != '--' )" as each comment line begins with a
double dash?

Also, is there a limit to the size of an external tables file?

Thanks
JD

Report message to a moderator

Re: IGNORE COMMENTS IN EXTERNAL TABLE [message #215467 is a reply to message #214413]

Mon, 22 January 2007 07:07

cbruhn2
Messages: 41
Registered: January 2007

Member

Why didn't you try it Cool

Here is my test setup

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
15:13:15 SQL>! cat new_t.sql
create or replace directory orafaq as '/home/oracle/orafaq';
drop table t1;
CREATE TABLE t1 (
column1 varchar2(20),
line varchar2(1000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY orafaq
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
load when (column1 != '--')
nobadfile
nologfile
nodiscardfile
FIELDS terminated by "@"
MISSING FIELD VALUES ARE NULL
(
column1 , line
)
)
location
(
't.txt'
)
)REJECT LIMIT UNLIMITED NOPARALLEL;
15:13:53 SQL> !cat t.txt
--@ this is a comment
X @this is not a comment
 @and this is the same
--@ but again a comment
 
15:14:37 SQL> @new_t.sql
 
Directory created.
 
Elapsed: 00:00:00.13
 
Table dropped.
 
Elapsed: 00:00:00.18
 
Table created.
 
Elapsed: 00:00:00.03
15:15:09 SQL> select * from t1 ;
 
COLUMN1
--------------------
LINE
--------------------------------------------------------------------------------X
this is not a comment
 
 
and this is the same
 
 
Elapsed: 00:00:00.24
15:15:32 SQL>

Please notice that there might be problems if column1 is null. I have tried with (column1 != '--' and column1 is not null) but gets following error message, when selecting :

15:17:35 SQL> select * from t1 ;
select * from t1
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "is": expecting one of: "equal, notequal"
KUP-01007: at line 2 column 40
ORA-06512: at "SYS.ORACLE_LOADER", line 19
 
 
Elapsed: 00:00:00.11
15:17:41 SQL>

best regards

Report message to a moderator

Previous Topic:	what's default setting for those user(sys,system,and so on) for oracle 10g
Next Topic:	deleting files in recovery area

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 06:22:43 CST 2024