How to use pointer files with multiple columns when loading xml data

Hi

I have a bunch of different files in a folder.

say 1.xml, 2.xml, 3.xml

I have created a file called clob_pointer.xml which has the following contents:

1.xml, 20110814143120
2.xml, 20110814143724
3.xml, 20110814144031

and i am trying to generate a definition for an external table that will allow me to read the second column.
The following is what i have that doesnt work.

CREATE TABLE ext_clob_table (text CLOB, thedate varchar2(300))
ORGANIZATION EXTERNAL
(
  TYPE oracle_loader
  DEFAULT DIRECTORY mytestdir
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ','
    (clob_pointer     CHAR(200))
    COLUMN transforms (text from lobfile (clob_pointer) from (mytestdir) CLOB)
  )
  location ('clob_pointer.txt')
);

Please help!
cesarchavezAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
if you want the date to be a date
REATE TABLE ext_clob_table (text CLOB, thedate date)
ORGANIZATION EXTERNAL
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ctemp
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ',' ltrim
    (clob_pointer     CHAR(200),
    thedate date "yyyymmddhh24miss")
    COLUMN transforms (text from lobfile (clob_pointer) from (mytestdir) CLOB)
  )
  location ('clob_pointer.txt')
);

Open in new window

0
 
sdstuberCommented:
are you sure you want the date to be a string?  why not have it be a date?
0
 
sdstuberCommented:
also, your pointer file is a little misleading,  I suggest NOT naming it with an XML extension  because the contents are not xml.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cesarchavezAuthor Commented:
im generating the pointer file dynamically so i could put it in another format but makes little difference.

Typo... the clob_pointer is .txt
0
 
sdstuberCommented:
based on your previous question I assume you are going to ask another question after this where you include some XML requirements?

0
 
cesarchavezAuthor Commented:
yes but thats a seperate question
0
 
sdstuberCommented:
if you want to keep your date value as a string


CREATE TABLE ext_clob_table (text CLOB, thedate VARCHAR2(300))
ORGANIZATION EXTERNAL
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ctemp
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ','
    (clob_pointer     CHAR(200),
    thedate CHAR(20))
COLUMN transforms (text from lobfile (clob_pointer) from (mytestdir) CLOB)
  )
  location ('clob_pointer.txt')
);
0
 
cesarchavezAuthor Commented:
ok that works for dates but what about strings?

I tried


CREATE TABLE ext_clob_table (text CLOB, thedate VARCHAR2(300))
ORGANIZATION EXTERNAL
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ctemp
  ACCESS PARAMETERS
  (FIELDS TERMINATED BY ','
    (clob_pointer     CHAR(200),
    thedate VARCHAR2(300))
COLUMN transforms (text from lobfile (clob_pointer) from (mytestdir) CLOB)
  )
  location ('clob_pointer.txt')
);
 
but it doesnt work
0
 
sdstuberCommented:
 (clob_pointer     CHAR(200),
    thedate VARCHAR2(300))

this is illegal

you can specify VARCHAR2 for the table's structure
but not for the LOADER syntax, all strings are CHAR  in loader
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.