Solved

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

Posted on 2011-09-02
9
320 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
  • 6
  • 3
9 Comments
 
LVL 73

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 73

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
 
LVL 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

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

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 73

Accepted Solution

by:
sdstuber earned 250 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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 60
Web Service from a stored procdure oracle 10 49
join 2 views with 5 conditions 3 46
Configure a Bean in an XML file 4 15
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now