Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-02
9
Medium Priority
?
340 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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