?
Solved

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

Posted on 2011-09-02
9
Medium Priority
?
337 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:cesarchavez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36475342
are you sure you want the date to be a string?  why not have it be a date?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36475348
also, your pointer file is a little misleading,  I suggest NOT naming it with an XML extension  because the contents are not xml.
0
 

Author Comment

by:cesarchavez
ID: 36475363
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36475369
based on your previous question I assume you are going to ask another question after this where you include some XML requirements?

0
 

Author Comment

by:cesarchavez
ID: 36475421
yes but thats a seperate question
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36475451
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36475466
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
 

Author Comment

by:cesarchavez
ID: 36475595
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36475662
 (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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question