OraFAQ Forum: Server Utilities » SFTP using PL/SQL

Home » RDBMS Server » Server Utilities » SFTP using PL/SQL (Oracle 12.1.0.2 on Redhat 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SFTP using PL/SQL [message #650920]

Fri, 06 May 2016 10:21

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

Report message to a moderator

Re: SFTP using PL/SQL [message #650921 is a reply to message #650920]

Fri, 06 May 2016 10:33

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: SFTP using PL/SQL [message #650922 is a reply to message #650920]

Fri, 06 May 2016 10:51

John Watson
Messages: 8966
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.

Report message to a moderator

Re: SFTP using PL/SQL [message #650924 is a reply to message #650922]

Fri, 06 May 2016 15:48

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.

Report message to a moderator

Re: SFTP using PL/SQL [message #650925 is a reply to message #650924]

Fri, 06 May 2016 16:05

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SFTP using PL/SQL [message #651437 is a reply to message #650920]

Tue, 17 May 2016 21:56

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

Report message to a moderator

Re: SFTP using PL/SQL [message #651438 is a reply to message #651437]

Tue, 17 May 2016 22:11

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SFTP using PL/SQL [message #651443 is a reply to message #651438]

Wed, 18 May 2016 01:04

John Watson
Messages: 8966
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651480 is a reply to message #651443]

Wed, 18 May 2016 22:35

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...

Report message to a moderator

Re: SFTP using PL/SQL [message #651482 is a reply to message #651480]

Wed, 18 May 2016 22:57

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651516 is a reply to message #651480]

Thu, 19 May 2016 06:48

EdStevens
Messages: 1376
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651536 is a reply to message #651516]

Thu, 19 May 2016 14:23

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

Report message to a moderator

Re: SFTP using PL/SQL [message #651537 is a reply to message #651536]

Thu, 19 May 2016 14:37

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651538 is a reply to message #651536]

Thu, 19 May 2016 14:44

John Watson
Messages: 8966
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

Report message to a moderator

Re: SFTP using PL/SQL [message #651582 is a reply to message #651536]

Fri, 20 May 2016 06:57

EdStevens
Messages: 1376
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').

Report message to a moderator

Re: SFTP using PL/SQL [message #651664 is a reply to message #651582]

Sun, 22 May 2016 23:38

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

Report message to a moderator

Re: SFTP using PL/SQL [message #651668 is a reply to message #651443]

Mon, 23 May 2016 01:10

John Watson
Messages: 8966
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651698 is a reply to message #651664]

Mon, 23 May 2016 06:37

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: SFTP using PL/SQL [message #651768 is a reply to message #651698]

Tue, 24 May 2016 21:47

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

Report message to a moderator

Re: SFTP using PL/SQL [message #651774 is a reply to message #651768]

Tue, 24 May 2016 22:38

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: SFTP using PL/SQL [message #651885 is a reply to message #651774]

Thu, 26 May 2016 13:47

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:31:22 CST 2025