Why is this external table not working???

#1

This is driving me crazy!

Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03

Any thoughts???

#2

amerar (AT) iwc (DOT) net wrote:

Quote:

This is driving me crazy!

Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

amerar (AT) iwc (DOT) net wrote:

Quote:

This is driving me crazy!

Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

amerar (AT) iwc (DOT) net wrote:

Quote:

This is driving me crazy!

Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

amerar (AT) iwc (DOT) net wrote:

Quote:

This is driving me crazy!

Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On Jan 11, 5:46*pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

ame... (AT) iwc (DOT) net wrote:
This is driving me crazy!

Ok, I have an external table that is tab delimited. *I am not
interested in all of the fields, jsut a few of them. *My table
definition is below. *But, it is not working! *Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
* ID * * * * * * * * * * * VARCHAR2(5),
* REPORT_DATE * * * *VARCHAR2(8),
* ESTIMATE * * * * * VARCHAR2(8)
)
ORGANIZATION EXTERNAL
* ( *TYPE ORACLE_LOADER
* * *DEFAULT DIRECTORY INDATA_DIRECTORY
* * *ACCESS PARAMETERS
* * * *( RECORDS DELIMITED BY NEWLINE
* * FIELDS *TERMINATED BY '|'
* * MISSING FIELD VALUES ARE NULL (
* * * "ID" * * * * * * * POSITION(1:5) * *CHAR(5),
* * * "REPORT_DATE" * * * * *POSITION(49:8) * CHAR(8),
* * * "ESTIMATE" * * * * * * POSITION(65:8) * CHAR(8))
* * *)
* * *LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
* )
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD * * A * * * 7.20 * *11 * * *5 * * * 00949
JON * * A 6.71 *47. /2005 * * * 5
CCFG *0 8.20 * *0 7 * * 5 * * * 0185
EERT *3.80 * * *3.8 2007 * * * *-99
LLF * * A 82 * *10.00 007 * * * 3 * * * 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

- Show quoted text -

Can I forget about the whole delimiter thingy? Can I say that a tab
character is X amount of spaces, and then just use positions?

#7

On Jan 11, 5:46*pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

ame... (AT) iwc (DOT) net wrote:
This is driving me crazy!

Ok, I have an external table that is tab delimited. *I am not
interested in all of the fields, jsut a few of them. *My table
definition is below. *But, it is not working! *Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
* ID * * * * * * * * * * * VARCHAR2(5),
* REPORT_DATE * * * *VARCHAR2(8),
* ESTIMATE * * * * * VARCHAR2(8)
)
ORGANIZATION EXTERNAL
* ( *TYPE ORACLE_LOADER
* * *DEFAULT DIRECTORY INDATA_DIRECTORY
* * *ACCESS PARAMETERS
* * * *( RECORDS DELIMITED BY NEWLINE
* * FIELDS *TERMINATED BY '|'
* * MISSING FIELD VALUES ARE NULL (
* * * "ID" * * * * * * * POSITION(1:5) * *CHAR(5),
* * * "REPORT_DATE" * * * * *POSITION(49:8) * CHAR(8),
* * * "ESTIMATE" * * * * * * POSITION(65:8) * CHAR(8))
* * *)
* * *LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
* )
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD * * A * * * 7.20 * *11 * * *5 * * * 00949
JON * * A 6.71 *47. /2005 * * * 5
CCFG *0 8.20 * *0 7 * * 5 * * * 0185
EERT *3.80 * * *3.8 2007 * * * *-99
LLF * * A 82 * *10.00 007 * * * 3 * * * 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

- Show quoted text -

Can I forget about the whole delimiter thingy? Can I say that a tab
character is X amount of spaces, and then just use positions?

#8

On Jan 11, 5:46*pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

ame... (AT) iwc (DOT) net wrote:
This is driving me crazy!

Ok, I have an external table that is tab delimited. *I am not
interested in all of the fields, jsut a few of them. *My table
definition is below. *But, it is not working! *Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
* ID * * * * * * * * * * * VARCHAR2(5),
* REPORT_DATE * * * *VARCHAR2(8),
* ESTIMATE * * * * * VARCHAR2(8)
)
ORGANIZATION EXTERNAL
* ( *TYPE ORACLE_LOADER
* * *DEFAULT DIRECTORY INDATA_DIRECTORY
* * *ACCESS PARAMETERS
* * * *( RECORDS DELIMITED BY NEWLINE
* * FIELDS *TERMINATED BY '|'
* * MISSING FIELD VALUES ARE NULL (
* * * "ID" * * * * * * * POSITION(1:5) * *CHAR(5),
* * * "REPORT_DATE" * * * * *POSITION(49:8) * CHAR(8),
* * * "ESTIMATE" * * * * * * POSITION(65:8) * CHAR(8))
* * *)
* * *LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
* )
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD * * A * * * 7.20 * *11 * * *5 * * * 00949
JON * * A 6.71 *47. /2005 * * * 5
CCFG *0 8.20 * *0 7 * * 5 * * * 0185
EERT *3.80 * * *3.8 2007 * * * *-99
LLF * * A 82 * *10.00 007 * * * 3 * * * 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

- Show quoted text -

Can I forget about the whole delimiter thingy? Can I say that a tab
character is X amount of spaces, and then just use positions?

#9

On Jan 11, 5:46*pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

ame... (AT) iwc (DOT) net wrote:
This is driving me crazy!

Ok, I have an external table that is tab delimited. *I am not
interested in all of the fields, jsut a few of them. *My table
definition is below. *But, it is not working! *Here is my definition
and what happends when I query the data:

CREATE TABLE TARGET_PRICE_EST_EXT
(
* ID * * * * * * * * * * * VARCHAR2(5),
* REPORT_DATE * * * *VARCHAR2(8),
* ESTIMATE * * * * * VARCHAR2(8)
)
ORGANIZATION EXTERNAL
* ( *TYPE ORACLE_LOADER
* * *DEFAULT DIRECTORY INDATA_DIRECTORY
* * *ACCESS PARAMETERS
* * * *( RECORDS DELIMITED BY NEWLINE
* * FIELDS *TERMINATED BY '|'
* * MISSING FIELD VALUES ARE NULL (
* * * "ID" * * * * * * * POSITION(1:5) * *CHAR(5),
* * * "REPORT_DATE" * * * * *POSITION(49:8) * CHAR(8),
* * * "ESTIMATE" * * * * * * POSITION(65:8) * CHAR(8))
* * *)
* * *LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
* )
REJECT LIMIT 50;

ID REPORT_D ESTIMATE
----- -------- --------
AAD * * A * * * 7.20 * *11 * * *5 * * * 00949
JON * * A 6.71 *47. /2005 * * * 5
CCFG *0 8.20 * *0 7 * * 5 * * * 0185
EERT *3.80 * * *3.8 2007 * * * *-99
LLF * * A 82 * *10.00 007 * * * 3 * * * 03

Any thoughts???

You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

- Show quoted text -

Can I forget about the whole delimiter thingy? Can I say that a tab
character is X amount of spaces, and then just use positions?

#10

amerar (AT) iwc (DOT) net wrote:

Quote:

On Jan 11, 5:46 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
ame... (AT) iwc (DOT) net wrote:
This is driving me crazy!
Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:
CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;
ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03
Any thoughts???
You have a choice ... you can either be positional or delimited but you
can't choose to be both.

My recommendation would be tab delimited and then select what you want.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

- Show quoted text -

Can I forget about the whole delimiter thingy? Can I say that a tab
character is X amount of spaces, and then just use positions?

No.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org