Home » RDBMS Server » Server Administration » IGNORE COMMENTS IN EXTERNAL TABLE
IGNORE COMMENTS IN EXTERNAL TABLE [message #214413] Tue, 16 January 2007 06:09 Go to next message
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
Re: IGNORE COMMENTS IN EXTERNAL TABLE [message #215467 is a reply to message #214413] Mon, 22 January 2007 07:07 Go to previous message
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




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:
  


Current Time: Sun Dec 04 16:48:22 CST 2016

Total time taken to generate the page: 0.08099 seconds