ftp text file in pl/'sql

hi how can i ftp file in pl/sql am oracle database 12c i what to ftp file from client to the sever
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Not sure what you are asking,M but potentially if you run into foreign/control characters in the file, when ft ping, switch to ascii mode, it will strip the control/bare line feeds.
1
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
I had the same thought as arnold.

Best you describe exactly what you're trying to accomplish.

Trying to ftp or rsync or in any way copy over raw database files, which are very different than database dump file, is complex.

First these files are incomplete, as data will live partially in memory buffers, unless you shutdown your database, to flush memory buffers + leave your database system shutdown during the entire transfer.

Also, once you have raw files transferred, you won't be able to do anything with them, as you still have to load them into another database system for use + very few database systems allow loading raw files.

Expand your question describing the exact nature of the data you'll be transferring. Likely someone can assist.
0
sdstuberCommented:
The package in this article should have everything you need.

https://www.experts-exchange.com/articles/3043/How-to-FTP-with-Oracle-PL-SQL.html

Also, it allows for ftp directly from the db to another server or from another server directly into the db without needing a file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chalie001Author Commented:
will i have select dialog where user can select file to ftp
0
sdstuberCommented:
pl/sql is not a user-interactive language.

if you want a dialog, you would need to write that using the tool/language of your choice and then invoke the ftp apis

If you have a file though, I agree with the previous posters though that going through pl/sql might be adding an extra step.
It will work, but not really value added unless you have other database actions involved as well; but if that were the case then I have to question what the file is for.  It's a common misconception that ftp requires a "file", but it doesn't.  If you have data, from say a select statement or stored procedure you can send that via ftp to a remote server.  The remote server will likely require materializing that data into a file but the source data doesn't need to be a file.
3
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
As sdstuber suggested, will i have select dialog where user can select file to ftp is a completely new topic.

Likely best to close out this question + ask a new question about adding user interaction to your ftp process.
0
chalie001Author Commented:
i what to copy fil from client pc which is window and the server is unix
am geting this error
DECLARE
    v_conn sdsftp.connection;
BEGIN
    sdsftp.clear_log;
    sdsftp.set_log_options(1);
    v_conn := sdsftp.open(:host, :username, :pwd);
    DBMS_OUTPUT.
     put_line(
        '----------------------------------------------------------------------------'
    );
    DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
    DBMS_OUTPUT.
     put_line(
        '----------------------------------------------------------------------------'
    );
    sdsftp.close(v_conn);
EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);
        RAISE;
END;

Error report -
ORA-29260: network error: not connected
ORA-06512: at "username.SDSFTP", line 370
ORA-06512: at "username.SDSFTP", line 217
ORA-06512: at line 20
ORA-24247: network access denied by access control list (ACL)
29260. 00000 -  "network error: %s"
*Cause:    A network error occurred.
*Action:   Fix the network error and retry the operation.

but i can ping the server from my client
0
sdstuberCommented:
The error says it all

your database is blocking network access

https://www.experts-exchange.com/articles/8429/How-to-use-Access-Control-Lists-in-Oracle.html
https://www.experts-exchange.com/articles/9074/How-to-use-Network-Access-Control-Lists-in-Oracle.html


Once you get the ACL fixed, you still might have a problem.

If you want to ftp from your pc to the database server, your must be running an ftp server on your pc, otherwise the database server won't have anything to connect to when it executes the package.
0
chalie001Author Commented:
am having this error now
when i put host name i get this error
Error report -
ORA-29260: network error: not connected
ORA-06512: at "dbusername.SDSFTP", line 398
ORA-06512: at "dbusername.SDSFTP", line 219
ORA-06512: at line 20
ORA-29260: network error: Connect failed because target host or object does not exist
29260. 00000 -  "network error: %s"
*Cause:    A network error occurred.
*Action:   Fix the network error and retry the operation.


when i put ip address in host name i get this
error report -
ORA-29260: network error: not connected
ORA-06512: at "dbusername.SDSFTP", line 398
ORA-06512: at "dbusername.SDSFTP", line 219
ORA-06512: at line 20
ORA-29260: network error: TNS:no listener
29260. 00000 -  "network error: %s"
*Cause:    A network error occurred.
*Action:   Fix the network error and retry the operation.
0
sdstuberCommented:
Remember the ftp code runs on the database server, NOT the pc.

So, the database server probably doesn't know the name of the pc you are trying to connect to.
Using the ip allows it to find the pc, but then the  "no listener" problem is because that pc is either not running an ftp server, or there is a firewall blocking access to the ftp server.
0
chalie001Author Commented:
How do I start the ftp server am in window 7
0
sdstuberCommented:
I don't think windows 7 has an ftp server, you'd have to download and install one like filezilla.

If you're using a terminal/ssh tool like mobaxterm it may have an ftp server built in (I know for sure mobaxterm does, some like the hummingbird suite come with a server but it's a separate tool, not sure about others.)
0
arnoldCommented:
Look at adding IIS appwiz.cpl add/remove windows features....
I think an FTP server is a component of IIS and I think is available on windows 7.
0
sdstuberCommented:
What are you trying to do that you think using pl/sql on the db server will help as a means of ftp from a pc?

Instead of using the db server to pull the file, why not use the pc to push the file?

Alternatively, is there a fileshare (SMB, NFS, or other) available that is accessible by both the pc and the db server? If so, instead of transferring the file via ftp, simply write to the fileshare location from the pc and then read the fileshare on the db server.

I'm still confused though as to what role the database and pl/sql have in this.  You've haven't described any functionality where pl/sql will be of added value.
1
chalie001Author Commented:
am trying to copy text file from user pc to server am now geting this error
ORA-20001: Permanent error from FTP server: 530 Login incorrect.
29260. 00000 -  "network error: %s"
*Cause:    A network error occurred.
*Action:   Fix the network error and retry the operation.

this si how i add my ftp server
After you've installed the FTP Service and IIS Management Console, perform the following steps.

Run Administrative Tools | Internet Information Services (IIS) Manager
Expand the local machine.
Right-click Sites and Add FTP Site.
Call it "Default FTP Site" with a path of "C:\inetpub\ftproot"; hit next
Enable Start FTP site automatically, select Allow SSL; hit next
Enable Anonymous Authentication; hit Finish
You should now be able to FTP to localhost. You may choose different options, but the options described above work for me and are very similar to the default options in IIS 6 FTP.

Note, you may also need to enable the FTP server in the firewall. For that use the following command.

netsh advfirewall firewall set rule group="FTP Server" new enable="yes"
0
sdstuberCommented:
Have you created an account in your ftp server for your db to connect to?

if you're using anonymous, does your ftp server require a password, even it ignores it?

The sdsftp package always sends a password, if your server doesn't expect one, you'll need to change the code to not send a password when the user is anonymous.
0
slightwv (䄆 Netminder) Commented:
>>am trying to copy text file from user pc to server am now geting this error

What is the actual business requirement you are trying to solve?

There are MANY easier ways to get a file from a client PC to a server than making the client PC an ftp server.  Depending on how your network is set up it can also open up that PC to security issues.

First method is network copy but I'm guessing your actual requirement is more involved than that.

Without knowing ANYTHING about your actual needs:  How would I solve this problem
SQL*Loader.  It comes with normal Oracle Client if you install the utilities or tools package if you are using the Instant Client.


Please tell us what the file contains and what you want to do with it once you get it to the database.
Tell us if this is part of some application and if you need to do this on a regular basis.
0
chalie001Author Commented:
my ftp require password how can i change the code
0
sdstuberCommented:
>>> my ftp require password how can i change the code

you don't need to, the package already assumes you will provide a password

In code example you showed above, the :pwd bind variable is where you'd put the password

    v_conn := sdsftp.open(:host, :username, :pwd);
0
chalie001Author Commented:
i did that that what is giving error
0
sdstuberCommented:
then the username and/or password you are passing in to the "open" function aren't correct.

Check for misspelling, typos or case sensitivity.

Add additional logging to the "send_ftp_command" procedure if you want and you can watch the tcp traffic and replicate it yourself to test with any telnet client.


but, you shouldn't need to go that far.
the ftp authentication is just 3 steps usually   open connection, send USER command, send PASS command.

If you've configured your ftp server to also require additional ACCT commands, then that is a 4th step, but I haven't encountered any ftp servers that actually implemented that.

easiest way to test - use another ftp client, log in to your ftp server using the exact same values you pass in to the "open" function.
If it works with your ftp client, then it should work with the code, if it doesn't then I have no idea why that might be.
If it doesn't work with another ftp client, then it won't work with the code either.
0
chalie001Author Commented:
hi am geting this error now ORA-20001 permanent error from ftp server :550 imp.txt no such file or directory
0
sdstuberCommented:
the error says exactly what it is, your file doesn't exist
0
arnoldCommented:
The placement of the file might be inaccessible to the oracle user or the path to access it is not correctly specified, the other possibility is that SELinux if enable is preventing access .....
0
chalie001Author Commented:
chki did check which path must i use
0
chalie001Author Commented:
am checking status or getenforce and am geting command not goind
0
arnoldCommented:
right click on the c:\inputfile directory, and get properties, go to the security tab, advanced, and then the effective rights tab, use oracle user and see what rights this user has to this directory. If oracle user has effective rights to read the contents of the directory, repeat the task for the file within.

This deals with confirming oracle user has rights to access c: the directory and the file..
next step is to move the file to where oracle access is guaranteed and to then make sure the error is not related to what you want the thing to do.
0
chalie001Author Commented:
you mean permission
i dont have c:\inputfile
0
arnoldCommented:
Yes, this means your user does not have access rights to the folder or its contents which is why it denies and generates errors,  You should check based on the credentials under which the Oracle DB is running. not your username, since the access is potentially through the oracle user or through the Agent credentials.
0
slightwv (䄆 Netminder) Commented:
Another possibility:  In the file explorer window where you show the .txt file exists, have you changed the default settings to actually show file extensions?  By default they are hidden so you might actually have a file there named tstimportfile12.txt.txt.

From a CMD prompt check:
dir c:\importfile\tsting*

Have you confirmed the machine and folder where the file exists is accessible via ftp?  From a remote machine, use regular ftp to see if you can retrieve the file.  Once that works, then try using Oracle.

A while back I asked this:
What is the actual business requirement you are trying to solve?

I'm still betting there is a better way than making the remote client an ftp sever just to get a file into an Oracle database.
0
chalie001Author Commented:
business requirements
I have Oracle form which got data in text file I need to import the data in database and also move the text file to certain folder in server this can be application server or database server BUT I think will use Oracle form webutil this approach will require more work than webutil
0
slightwv (䄆 Netminder) Commented:
My Forms knowledge is ancient so I cannot be much help with the specific code but my common sense says YES, using Forms to read the file off the client seems like a MUCH BETTER option than configuring the client to be an ftp server just to get a file.

Making the client an ftp server also makes it more of a security risk.

I had to google around but I think you are looking for CLIENT_TEXT_IO.

Once you get the file you should be able to write it to the database server wherever you want.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.