Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Data Exchange on Solaris via CSV file

SOLVED
Go to solution
Highlighted
Jan Pavelka
Occasional Visitor

Data Exchange on Solaris via CSV file

Hi all,
I have two SD application servers on Solaris platform and I need to perform Data Exchange task from CSV or TXT file.

Unfortunately HR department is not able to provide data in DB format - only CSV or TXT is possible.

But on Solaris platform I can use only JDBC drivers to access data and I miss JDBC to CSV. I've already tried to use csvjdbc utility from sourceforge.org. It was working OK in Java application but with sd_export it failed.

Do you see any other possibilities?

I appreciate any hints - it is very urgent.

Thank you
Jan
6 REPLIES
Radoslav Krochm
Frequent Visitor
Solution

Re: Data Exchange on Solaris via CSV file

Hi Jan,

there is one possibility to use "external table" in oracle db. You have to create external table in oracle db or your schema and when you define external table you specify your csv or txt file as datasource for external oracle table.

Then you can access csv or txt file as regular oracle table, it means you can use standard data-exchange process.

BR, Frodo

Jan Pavelka
Occasional Visitor

Re: Data Exchange on Solaris via CSV file

Hi Frodo,
I've never used external tables but the idea looks pretty good. I'm going to try it.
I'll assign you some points later but probably it can be a full bundle :o)

BR
Jan
Radoslav Krochm
Frequent Visitor

Re: Data Exchange on Solaris via CSV file

Hi,

Look at some hints:

CREATE TABLE (first_name CHAR(15),
last_name CHAR(20),
year_of_birth INT,
phone CHAR(12),
area_code CHAR(3),
exchange CHAR(3),
extension CHAR(4)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_table_dir
ACCESS PARAMETERS
(FIELDS RTRIM
(first_name (1,15) CHAR(15),
last_name (*,+20),
year_of_birth (36,39),
phone (40:52),
area_code (*-12, +3),
exchange (*+1, +3),
extension (*+1, +4)
)
)
LOCATION ('foo.dat')
);

This is content of 'foo.dat':
Alvin Tolliver 1976415-922-1982
Kenneth Baer 1963212-341-7912
Mary Dube 1973309-672-2341

foot.dat in example above is your HR csv or txt file and must be placed in external_table_dir directory.

First you have to create this directory(You must be connected to oracle as any oracle user):

CREATE DIRECTORY external_table_dir AS 'full path to directory'

Then, setup access rights to external_table_dir:

GRANT READ ON DIRECTORY external_table_dir TO

GRANT WRITE ON DIRECTORY external_table_dir TO


!!! Oracle process must have rights to read/Write to external_table_dir directory.

Suma sumarum:

1. Create directory ...
2. Grant ....
3. Create table

BR, Frodo
Jasper Verweij
Honored Contributor

Re: Data Exchange on Solaris via CSV file

Jan,

See http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=649577 for JDBC examples.

According to hp support Fositex should work with CSV.

Jasper
Jan Pavelka
Occasional Visitor

Re: Data Exchange on Solaris via CSV file

Hi Frodo,

thanks, we already learned all this stuff and tables are ready for providing data!

The last issue is charset converting - we need UTF-8. But it should be an easy task.

BR
Jan
Jan Pavelka
Occasional Visitor

Re: Data Exchange on Solaris via CSV file

Hi Jasper,

thank you. I've already studied this thread a few times.
I had diffculties with csvjdbc and xlsql as well - terrible java errors.

I haven't tried Fositex - it is my secret weapon for next occasions :o)

External tables completely solved my problem.
BR
Jan
//Add this to "OnDomLoad" event