SQL & PL/SQL

Can you check UTL_FILE.FOPEN it is able to read from a file... but check if it will work in your case or not ... also using SQL Loader program and Control file approach should also work ..

You have some  possibile of way of solutions.

For an example :

1) use  utl_file | external tables  and  as transport layer ( external  jobs/shell script  ,  NFS...)

2) use a ready-made/external tools   solution,  as ODI ,SQLLoder, and etc...

P.S. on java/pl_sql , ftp , sftp

N.B. But, you can write the simplest solution, because all information is  available.(documents,forums...) and  at internet many..many of pattern-blanks.

Just google and you will a find a plsql package capable of doing FTP, for instance https://oracle-base.com/articles/misc/ftp-from-plsql

SFTP you can't find with google, you have to write it yourself, or use some java or external  solutions.

As Anton says, there's already people who have written FTP packages out there for PL/SQL.

Whilst the code you've posted is the right starting point, using UTL_TCP, there's a lot more involved to FTP than just opening a connection and trying to read data.  FTP requires a connection down which you send the commands, and then (typically) a further passive connection opened on the port the initial communication returns to you, down which you then read the data etc.

For full details of the protocol you'd need to implement yourself see: https://tools.ietf.org/html/rfc959

SFTP on the other hand is a completely different protocol and is far more complex, due to the nature of how it deals with secure transmissions.

e.g. https://tools.ietf.org/html/draft-ietf-secsh-filexfer-13 is just one of the specifications for it...

You can see a more complete list given by the FileZilla team on their wiki site: https://wiki.filezilla-project.org/SFTP_specifications

Thanks for your reply. I tried this but it's not working showing TNS connection closed error

If your "FTP-server" only serves the SFTP-protocol that might happen

Thanks for your reply. IF i use UTL_FILE.FOPEN it throws an error like follows:

ORA-29280: invalid directory path

ORA-06512: at "SYS.UTL_FILE", line 41

ORA-06512: at "SYS.UTL_FILE", line 478

ORA-06512: at line 25

So could you please help me with some other solution if possible ?

Well, utl_file.fopen is to be used only for opening files that are in a filesystem in a directory accessible to user oracle. So, the first thing is to get the file to such a directory using the ftp package (or some other means - which I know not for I never did such). Only afterwards use ftp or select from external table that you have previously created.

Thank you very much for your reply. Could you please share the oracle pl sql script to copy csv file from SFTP server to Oracle Database local directory, if you have any ?

Have you actually read the previous replies?

It's not actually PL/SQL code, but here's something you may have found yourself:

https://dbashavefeelingstoo.wordpress.com/2011/07/22/sftp-with-plsql/

Hi,

writing plsql is typically like eating an elephant, you don't expect to solve the entire thing in one sitting, you need to split the problems up into its discrete steps.

In your case there is; -

1. Can I connect to the remote site sftp

2. Does Oracle have rights to read the file

3. Can utl_file read read the file

Only when each step is complete do you move on to the next problem.

You may find that on 3 - utl_file can never have rights because of the sites policies, in which case then you might consider

1. Can I connect to the remote site using sftp

2. Does Oracle have rights to sftp the file to a local directory

3. sftp get the file

4. Can utl_file read the file locally

Robert Angel wrote:
writing plsql is typically like eating an elephant, you don't expect to solve the entire thing in one sitting, you need to split the problems up into its discrete steps.

Not a fair statement about PL/SQL the language, as the size of the meal is determined by the complexity of the problem - and not by the language.

Eating an elephant in any other language, e.g. C or Java, requires the exact same approach of breaking the large complex problem into smaller and simpler problems.

Agreed.

I was not comparing pl/sql itself to an elephant, I was just trying to get the end user to move beyond 'X' did not work, when 'X' is more like 'A' worked, 'B' worked, 'C' failed....

No slight intended or implied by the elephant analogy.

Robert Angel wrote:
I was not comparing pl/sql itself to an elephant.

I should hope not. PL/SQL is no elephant.

It is more like a squirrel.

But not an ordinary squirrel.

More like..

lol.

I would not touch his nuts....