0

I have a side project I'm working on currently that requires me to copy over a .csv file from a remote FTP and save it locally. I figured I would use DBMS_SCHEDULER.GET_FILE but I do not have permission. When I asked my manager, he said that I wont be able to get privileges to do this and should look up other ways.

After researching for a couple of days I keep coming back to DBMS_SCHEDULER, am I out of luck or are my searching skills terrible.

Thanks

user2405778
  • 467
  • 6
  • 16
  • 29

1 Answers1

3

I'm not certain you'd want to use DBMS_SCHEDULER for this; from what I understand from the documentation (never used this myself) the FTP site would have to be completely open to all; there is a parameter destination_permissions, but it's only "Reserved for future use", i.e. there's no way of specifying any permissions at the moment.

If I'm right with this then I agree with your manager, though not necessarily for the same reasons (it seems like you'll never get permission to use DBMS_SCHEDULER which I hope is incorrect).

There are other methods of doing this:

  1. UTL_TCP; this is simply a method of interacting over a TCP/IP protocol. Oracle Base has an article, which includes a FTP package based on UTL_TCP and instructions how to use it. This also requires the use of the UTL_FILE package, which can write OS files.

  2. UTL_HTTP; I'm 99% certain it's possible to connect to an FTP using this; it's certainly possible to connect to a SFTP/any server. It'll require a little more work but it would be worth it in the longer run. It would also require the use of UTL_FILE.

  3. A Java stored procedure to FTP directly; this is probably the best approach; create one using one of the many Java FTP libraries.

  4. A Java stored procedure to call call OS commands; this is easiest method but the least extensible. Oracle released a white paper on calling OS commands from within PL/SQL back in 2008 but there's plenty of other stuff out there (including Oracle Base again)

Lastly, you could question whether this is actually what you want to do...

  1. What scheduler do you use? Does it have event driven scheduling? If so there's no need to FTP from within Oracle; use UTL_FILE to write a file to the OS and then OS commands from there.

  2. Was the other file originally in a database? If that's the case you don't need to extract it. You could use DBMS_FILE_TRANSFER to collect it straight from the database or even create a JDBC connection or (more simply) a database link to SELECT the data directly.

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • Thank you very much for the reply. In regards to the permission, no one has it other than the administrator so it's not just me. I will look into these other methods and try to find one to use. – user2405778 Aug 19 '13 at 20:45
  • As for the use, we want to try and create a procedure that checks a clients FTP and see if there are any new files. When there is a new file, we want to download it to a temp folder, upload it to our DB then delete the file. We both dont know if its possible but I am doing research and seeing what we can do. – user2405778 Aug 19 '13 at 20:47
  • Yes, it's more than possible. I do it constantly every day (though not with FTPs). I suspect that you will need more permissions than you currently have though. The DBA may have to loosen the purse strings if this is to happen. It _is_ less safe with an FTP but you can create a separate schema with no other privileges to go out searching or even better use a completely separate database. – Ben Aug 19 '13 at 20:53
  • Awesome, that is good to hear. Once again I thank you for your reply. – user2405778 Aug 19 '13 at 21:13