?
Solved

Can a Oracle external table go to a client machine and grab data saved in a excel csv file?

Posted on 2010-01-12
4
Medium Priority
?
567 Views
Last Modified: 2012-05-08
I need to develop a pl/sql stored procedure to load a csv file from a client side to a external table on the server side. I think I need to develop this procedure since I think Oracle external table which must be placed in the server side canot go directly goes to user machine and grab data from a csv file saved in user's directory. I  don't know how to go to develop such a procedure and need help started. Thanks
0
Comment
Question by:lmymusic2000
[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
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 26298011
yes/no - depends

If the server has access to the client machine (possibly samba shares or nfs mounts)
and
if the remote filesystems are accessible by Oracle
and
if the pl/sql procedure has access to that directory (via utl_file_dir or directory object) then yes

if any of those are not true, then no



0
 

Author Comment

by:lmymusic2000
ID: 26298166
If the server has no access to the client machine then how would I go to develop a procedure to load a csv file from the client side to a external table in the server side? I know that it has to do with UTL FILE package but I do not know exactly how to  code to achieve this? Any help would be appreciated.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 26298237
utl_file won't help either,  same restrictions as above.

you will need to have some client side tool that will load a clob/blob directly into the database or ftp the file to the server.
0
 

Author Comment

by:lmymusic2000
ID: 26303569
In my case this user can access to Portal through which I can develop a pl/sql procedure on the back side that connects user by a form for them to upload their cvs files to the server but I do not have enough knowledge to do this and I am not sure it is possible to do this like you mention above. Another approach to this problem is upload this cvs file as a blob object to a table.  I know how to write a procedure for user to upload their file by blob object to a table but I don't know how to break down this blob into individual data. I need help on converting this blob file to individual data if you know of a way it can possible, I appreciated very much.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

800 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