Solved

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

Posted on 2011-09-02
9
317 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP and Soap 3 29
microsoft access - xml 10 26
PL/SQL Search for multiple strings 5 22
Schema creation in Oracle12c 6 24
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

762 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

17 Experts available now in Live!

Get 1:1 Help Now