Problem while using Load when clause?

Question

I need to reject the rows from an external table which starts (first column of every row) with some special characters (eg. ~ \ etc) in a CSV file.

For that I have used LOAD WHEN clause in the ACCESS PARAMETERS clause. I have been using the following statement in the Access parameters clause:

load when (1:1) != '~'

But it throws an error stating "INVALID CHARACTER"

If I try to use the below statement,

load when (empname != '~empname')

It throws:

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-06512: at "SYSTEM.EMP_PROCEDURE", line 101
ORA-06512: at line 1

Line 101 in the procedure is select * from emp;

How do I use LOAD WHEN clause - any examples?

score 0 · Answer 1 · answered May 12 '11 at 12:35

0

Have you tried using the ASCII value of a tilde?

load when (1:1) != CHR(126)

answered May 12 '11 at 12:35

Datajam

4,141
2
23
25

Thanks for the advice, but Iam facing the same issue 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 "(": expecting one of: "and, badfile, byteordermark, characterset, column, data, delimited, discardfile, disable_directory_link_check, exit, fields, fixed, load, logfile, language, not, nodiscardfile, nobadfile, nologfile, date_cache, or, processing, readsize, string, skip," – Raj May 13 '11 at 06:17
CONTD., KUP-01007: at line 2 column 40 ORA-06512: at "SYS.ORACLE_LOADER", line 19 ORA-06512: at "SYSTEM.EMP_PROCEDURE", line 101 ORA-06512: at line 1 – Raj May 13 '11 at 06:17

score 0 · Accepted Answer · answered May 13 '11 at 01:08

0

You could use this:

LOAD WHEN (1:1) != 0x'7e'

Reference: http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/et_params.htm#i1009513

answered May 13 '11 at 01:08

Jeffrey Kemp

59,135
14
106
158

Thanks for the advice, it is working. Since I am using TOAD I tried with single quotation, but when i tried with LOAD WHEN (1:1) != 0x''7e'' it is working. – Raj May 13 '11 at 06:31

Problem while using Load when clause?

2 Answers2