Home » RDBMS Server » Server Utilities » SFTP using PL/SQL (Oracle 12.1.0.2 on Redhat 6)
SFTP using PL/SQL [message #650920] Fri, 06 May 2016 10:21 Go to next message
Lucky A
Messages: 68
Registered: October 2007
Member
Hi to all,

How can I execute sftp file transfer directly from the oracle database using pl/sql?
Any assistance will be helpful.

Thanks,

Lucky A
Re: SFTP using PL/SQL [message #650921 is a reply to message #650920] Fri, 06 May 2016 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes: https://www.google.fr/?gfe_rd=cr&ei=EbksV7bzBLTP8AfRjJ3YCw#q=oracle+plsql+ftp.

Re: SFTP using PL/SQL [message #650922 is a reply to message #650920] Fri, 06 May 2016 10:51 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Note that sftp is nothing to do with FTP. It is ssh.

What do you want to do? Copy files? Rather than writing (or finding) a whole heap of code to use ssh from PL/SQL, you might be better off using the Scheduler to create an external script job. Much simpler.
Re: SFTP using PL/SQL [message #650924 is a reply to message #650922] Fri, 06 May 2016 15:48 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
The client wants to be able to copy files from the SFTP server to the database server.
Re: SFTP using PL/SQL [message #650925 is a reply to message #650924] Fri, 06 May 2016 16:05 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Lucky A wrote on Fri, 06 May 2016 13:48
The client wants to be able to copy files from the SFTP server to the database server.

SFTP is rather unsecure in both username/password & data are passed unencrypted across the network.
This whole issue really has nothing to do with Oracle & should be solved at OS level.
Re: SFTP using PL/SQL [message #651437 is a reply to message #650920] Tue, 17 May 2016 21:56 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
Hi Folks,

Please, this is an update to my previous post. The requirement has changed and has gotten challenging. The client has daily XML files transferred to the SFTP server - thousands of XML files everyday. The client wants the ability to read the files from the SFTP server directly into the Oracle database using PL/SQL. How can this task be accomplished.

Thanks,

Lucky
Re: SFTP using PL/SQL [message #651438 is a reply to message #651437] Tue, 17 May 2016 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>The client wants the ability to read the files from the SFTP server directly into the Oracle database using PL/SQL.
PL/SQL can only access files that are local to the DB Server; unless you want to implement SFTP in PL/SQL.
https://tools.ietf.org/html/rfc4253

>How can this task be accomplished?
Either with MUCH difficulty or not at all.
Re: SFTP using PL/SQL [message #651443 is a reply to message #651438] Wed, 18 May 2016 01:04 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I've already suggested that you use the Scheduler to launch your scp (or sftp, as you insist using that interface to ssh) commands. Did you try that?
Re: SFTP using PL/SQL [message #651480 is a reply to message #651443] Wed, 18 May 2016 22:35 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
I am researching the most viable option as the client doesn't want to make use of external table. I am also considering using third party tools...
Re: SFTP using PL/SQL [message #651482 is a reply to message #651480] Wed, 18 May 2016 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Lucky A wrote on Wed, 18 May 2016 20:35
I am researching the most viable option as the client doesn't want to make use of external table. I am also considering using third party tools...


how will we know when correct answer is posted here?
Re: SFTP using PL/SQL [message #651516 is a reply to message #651480] Thu, 19 May 2016 06:48 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Lucky A wrote on Wed, 18 May 2016 22:35
I am researching the most viable option as the client doesn't want to make use of external table.



So now we have yet another "requirement".
What is the objection to external tables? Why does the client care about the technical solution,as long as it meets his business requirement within his budget? To that end, you'd get far better advice (and thus better serve your clients interests) if you'd step back from a per-conceived (and often ill-conceived) technical solution and simply describe the business requirements. Where do these files come from (inside the organization? Some third party? Multiple third parties?). In business terms, what do the file mean to the business? How does one of these files relate to another?

And very important: What is the desired end-state of the data in these files? As long as you get to that desired end-state, what does the client care about the technique used to get there?
Re: SFTP using PL/SQL [message #651536 is a reply to message #651516] Thu, 19 May 2016 14:23 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
1) What is the objection to external tables? Why can't it be considered?
Because of the share volume of the files.
2) What's the the business requirements?
Want to load the data.
3) Where do these files come from - third party? Multiple third parties? Or from within?
From a third party.
4) How does one of these files relate to another?
All are related.
5) And very important: What is the desired end-state of the data in these files?
Take the data and load it into a LOB column of a table within an Oracle database and extrapolate the data.

Thanks to all for your guidance and assistance.

Lucky
Re: SFTP using PL/SQL [message #651537 is a reply to message #651536] Thu, 19 May 2016 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
have you enjoyed your week long snipe hunt?

post CREATE TABLE statement for the target table.
post file description of the raw data files & details how the raw file content gets mapped into target table.

>The client wants the ability to read the files from the SFTP server directly into the Oracle database using PL/SQL.
Above can't be done.
Can you prove me wrong?
Re: SFTP using PL/SQL [message #651538 is a reply to message #651536] Thu, 19 May 2016 14:44 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Quote:
1) What is the objection to external tables? Why can't it be considered?
Because of the share volume of the files.
The volume of files would not present any problem that I can see. You may well have a better DBA than me, I would be interested in knowing his reasoning.
--
John Watson
Oracle Certified Master DBA
Re: SFTP using PL/SQL [message #651582 is a reply to message #651536] Fri, 20 May 2016 06:57 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Lucky A wrote on Thu, 19 May 2016 14:23
1) What is the objection to external tables? Why can't it be considered?
Because of the share volume of the files.
2) What's the the business requirements?
Want to load the data.

That's not a business requirement.

Quote:

4) How does one of these files relate to another?
All are related.


I asked HOW they are related, not just THAT they are.
What kind of data is in the files? What does that data represent to the business? HOW does one file relate to another? What differentiates the data in one file from that of another? How many of these files do you get per day/week/month? What does the arrival of one of these files represent? Do you pull the file from the source or is it pushed to you from the source?

Quote:

5) And very important: What is the desired end-state of the data in these files?
Take the data and load it into a LOB column of a table within an Oracle database and extrapolate the data.


That is so vague as to be meaningless. Do the files have a structure? Would not make more sense to use that structure to map that structure into nomal rows and columns instead of just shoving the whole thing into a LOB? Exactly how would you 'extrapolate' data from this undifferentiated lob? (And I think you mean 'extract', not 'extrapolate').


Re: SFTP using PL/SQL [message #651664 is a reply to message #651582] Sun, 22 May 2016 23:38 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
Based on your inputs and suggestions, it's apparent that what the client wants is not easily doable, if at all. You guys have demonstrated that you are experienced and knowledgeable DBAs. If I go by the strict requirements from the client, I would pluck out my full head of hair before the task is completed.

Please, if you have any code that is written with Oracle in mind that will pull files (thousands per day) from an SFTP server (pushed to the client from 3rd party's), that will be a good start. External table is also an inclusive option...


Thank you,

Lucky
Re: SFTP using PL/SQL [message #651668 is a reply to message #651443] Mon, 23 May 2016 01:10 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
John Watson wrote on Wed, 18 May 2016 07:04
I've already suggested that you use the Scheduler to launch your scp (or sftp, as you insist using that interface to ssh) commands. Did you try that?

Re: SFTP using PL/SQL [message #651698 is a reply to message #651664] Mon, 23 May 2016 06:37 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Lucky A wrote on Sun, 22 May 2016 23:38
Based on your inputs and suggestions, it's apparent that what the client wants is not easily doable, if at all. You guys have demonstrated that you are experienced and knowledgeable DBAs. If I go by the strict requirements from the client, I would pluck out my full head of hair before the task is completed.



Well, as a dba it is your job to educate the client. You - and the client - need to focus on and articulate the desired end state of these files, and take all restrictions off the table. Once you can articulate that you can get some very good advice on the best technical solution.



Quote:
Please, if you have any code that is written with Oracle in mind that will pull files (thousands per day) from an SFTP server (pushed to the client from 3rd party's), that will be a good start. External table is also an inclusive option...


Previously you had insisted that external tables were NOT an option.

Re: SFTP using PL/SQL [message #651768 is a reply to message #651698] Tue, 24 May 2016 21:47 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
Hi All,

To answer the concern raised and to provide feedback:

The client uses RD Web Access to copy the XML files from a third party server to a local SFTP server.

I was asked on this forum why External Table is not an option. Because the client thinks if the use of External table is considered, then they either have to join all of the XML files or create thousands of external tables. Remember they have thousands of XML files been transferred daily. Also, do I know the content of the files or how they are related to one another? The answer at this preliminary stage, is no.

Using DBMS_Scheduler to launch scp creates security risk to the database - using the database to call an external procedure violates STIG requirements. The database is put at risk to unauthorized access to the procedure from outside of the database process...

The objective of the client is to be able to read the data of the XML files directly from the SFTP server, parse it and then load it into the database. They want to bypass the step of getting the XML files to a local server.

I came to this forum for guidance and to find out the possibility of achieving the client's objective - to find out whether the means of achieving this objective exist and how it can be achieved. After hearing your expert opinions and directives, it seems that the client's objective is implausible or if remotely possible, it will be time consuming and convoluted at best to achieve.
To beg the question; I am looking at what's plausible to get the XML data from the SFTP server onto the database. If it means pulling, on a daily basis, thousands of files unto a local machine and using External Table, then I'm open to such solution. But please give me example(s) on how to achieve what you suggest...

Thanks and best regards,

Lucky
Re: SFTP using PL/SQL [message #651774 is a reply to message #651768] Tue, 24 May 2016 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Realize & understand that PL/SQL code run DEEP inside the database engine & can only access the file system that is local to the DB Server.
This means the that any PL/SQL code will "pull" the data into the database from file that resides on the DB Server system
Alternatively somebody could write Java, PERL, or <your favorite language here> code that runs on the SFTP server to "push" the XML data into the database.
use of EXTERNAL TABLE requires the file to reside on the DB Server.

How is the decision made to determine which XML file gets loaded into which Oracle table?
Re: SFTP using PL/SQL [message #651885 is a reply to message #651774] Thu, 26 May 2016 13:47 Go to previous message
Lucky A
Messages: 68
Registered: October 2007
Member
With all of your inputs and having a clearer understanding of what the client wants and presenting the various options attainable, the best method is to use JSch API which is Java based to accomplish the task.

Thank you,
Lucky
Previous Topic: SQL*Loader-485 (split from http://www.orafaq.com/forum/t/130372/0/unread/#msg_651651 by bb)
Next Topic: impdp error
Goto Forum:
  


Current Time: Fri Sep 18 14:34:31 CDT 2020