13 Replies Latest reply: Feb 7, 2013 4:31 AM by 989683 RSS

    Reading local files from PLSQL

    989683
      Hi all...

      This is my first question here with the hope that i would get best results from you experts... :)
      here goes the question...

      I am having a task of copying a file from the oracle server (installed in server in my concern) and put into local machine(local D drive for Example) for further processing.I can access the file and put into the same directory using UTL_TCP..How to get it into my machine in any of the possible ways?? Please help me out in achieving the task.

      Thanks,
      Radnus
        • 1. Re: Reading local files from PLSQL
          Marwim
          Hello Radnus,

          welcome to the forum.

          You cannot access your PC from the server so it depends on the client you use.
          Example: With SQL*PLUS you can spool a CLOB to a local file or you can setup a simple APEX page to achieve this.

          We need more details about your environment to give you more help.

          Regards
          Marcus
          • 2. Re: Reading local files from PLSQL
            Karthick_Arp
            Welcome to the forum!!

            Please reade {message:id=9360002}

            May be try [url http://www.oracle-base.com/articles/misc/ftp-from-plsql.php] FTP from PL/SQL
            • 3. Re: Reading local files from PLSQL
              rp0428
              >
              This is my first question here with the hope that i would get best results from you experts...
              here goes the question...

              I am having a task of copying a file from the oracle server (installed in server in my concern) and put into local machine(local D drive for Example) for further processing.I can access the file and put into the same directory using UTL_TCP..How to get it into my machine in any of the possible ways?? Please help me out in achieving the task.
              >
              Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).

              Oracle can do that if you install Oracle's scheduler agent on the remote host.

              See the DBMS_SCHEDULER chapter of the PL/SQL Packages and Types Reference.
              http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#BABHDFFF
              >
              PUT_FILE Procedure
              This procedure saves a file to the operating system file system of a specified remote host or of the local computer. It differs from the equivalent UTL_FILE procedure in that it uses a credential and can save files to a remote host that has only a Scheduler agent (and not an Oracle Database) installed.
              • 4. Re: Reading local files from PLSQL
                989683
                Thanks Marwim for your reply..My oracle Version Info is as below...
                ********/
                Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
                PL/SQL Release 10.1.0.2.0 - Production
                CORE 10.1.0.2.0 Production
                TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
                NLSRTL Version 10.1.0.2.0 - Production
                *******/

                Both my Server and Client is running on Windows XP OS..

                I created a directory as below ,

                "CREATE OR REPLACE DIRECTORY DIR_TEST AS 'D:\TELENOR' " which is in server where oracle is installed in D drive which i can access successfully..but I need the DIR_TEST to be my local D drive.. below are the lines i used for putting in server side location

                DECLARE
                l_conn UTL_TCP.connection;
                BEGIN
                l_conn := ftp.login('MY FTP HOST', '21', 'USER', 'Passwrd');
                ftp.ascii(p_conn => l_conn);
                dbms_output.put_line('Successfuly Connected');
                ftp.get(p_conn => l_conn,
                p_from_file => '/home/wtiatl/Incoming/ArcGisv93Crack/ArcGisv9.3.Crack/install.txt',
                p_to_dir => 'DIR_TEST',
                p_to_file => 'test_FTP_get.txt');
                ftp.logout(l_conn);
                utl_tcp.close_all_connections;
                END;
                • 5. Re: Reading local files from PLSQL
                  BluShadow
                  986680 wrote:
                  p_from_file => *'/home/wtiatl/Incoming/ArcGisv93Crack/ArcGisv9.3.Crack/install.txt',*
                  You wouldn't be trying to illegally crack software by any chance would you?
                  • 6. Re: Reading local files from PLSQL
                    989683
                    Not at all...I guess u asked this question just coz of that Install.txt file..which is in my system oly..the FTP i have used is my own concerns FTP..I don need to illegally crack it...there were no txt files than that install.txt file..so i tried accessing that...
                    • 7. Re: Reading local files from PLSQL
                      989683
                      I tried copying a file from an FTP to my oracle server drives..and i can do it with the code posted above....I need to put it into my local machine's Drive from an FTP or oracle server..this is what i was looking for..
                      • 8. Re: Reading local files from PLSQL
                        BluShadow
                        986680 wrote:
                        Not at all...I guess u asked this question just coz of that Install.txt file..which is in my system oly..the FTP i have used is my own concerns FTP..I don need to illegally crack it...there were no txt files than that install.txt file..so i tried accessing that...
                        It was the ArcGis Crack software that was the concern.
                        I tried copying a file from an FTP to my oracle server drives..and i can do it with the code posted above....I need to put it into my local machine's Drive from an FTP or oracle server..this is what i was looking for..
                        So, you need to PL/SQL code to FTP to your local machine and "put" the file on it. Of course your local machine will have to have the relevant FTP services running on it to accept incoming FTP requests.
                        • 9. Re: Reading local files from PLSQL
                          989683
                          Not necessarily from FTP..it can be from a server to a local machine...I mainly need PLSQL code for getting a file from a server machine and to put into a local machine.
                          • 10. Re: Reading local files from PLSQL
                            BluShadow
                            986680 wrote:
                            Not necessarily from FTP..it can be from a server to a local machine...I mainly need PLSQL code for getting a file from a server machine and to put into a local machine.
                            Ok, the reason people are suggesting FTP is because you have to understand... PL/SQL is a process running on the server. That process can only access file systems that are visible to the server itself, so typically anywhere on the servers own filesystem, plus any mapped/networked drives it can access. Usually in a client server architecture you would not have client computers mapped on servers, but rather the other way around.. the servers may be mapped as networked paths on the client computers, if those clients need access to the servers in that way. PL/SQL as a process cannot magically jump across the network to a client computer, break past all that client computer's security to write files on that client computers hard disk. Standard network and computer security prevents that.
                            That's why there are protocols for accessing other computers across a network, such as HTTP and FTP.

                            So, typically, if a client needs a file from the server you wouldn't have the server trying to push the file to the client, but rather the client requesting the file from the server and writing the file itself to it's own local file system.

                            Thus, your approach to try and get PL/SQL to push files from the server to the client is generally a wrong approach. Yes, you can get PL/SQL to use the TCP_IP package to implement FTP (or use a 3rd party FTP package) to get it to FTP the package from the server to the client, but it wouldn't be standard good design to be doing it that way.
                            • 11. Re: Reading local files from PLSQL
                              989683
                              ok thanks blushadow....if PLSQL code cant copy/paste a file from server to client machine what could be the best way to accomplish this task of getting a file from a server to client in any other way in oracle??

                              and rp0428 suggested about DBMS_SCHEDULER.PUT_FILE()..would like to know more abt it...
                              • 12. Re: Reading local files from PLSQL
                                BluShadow
                                986680 wrote:
                                ok thanks blushadow....if PLSQL code cant copy/paste a file from server to client machine what could be the best way to accomplish this task of getting a file from a server to client in any other way in oracle??

                                and rp0428 suggested about DBMS_SCHEDULER.PUT_FILE()..would like to know more abt it...
                                Documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#BABHDFFF
                                • 13. Re: Reading local files from PLSQL
                                  989683
                                  Referred Google abt dbms_schedular...couldn't understand clearly if my task can be done with schedular