Can anyone suggest me how i can write the path to store the output file in which my stored procedure outputs in my oracle DB?

Question

suppose the IP address of my FTP server is xx.xxx.xx.xx and i need the output file to be stored in D:/example. I need to esnure that the path i give is in my FTP server. How can i include that in my fopen function, like a path which points to the example in my FTP server.

Answer 1

Generally speaking, this is how it goes:

• there's a database server
• there is a directory on one of its disks
• that directory will be used in create directory command which creates a directory, Oracle object
• it will be used as a target for your file-related operations. For example:
  • it'll contain CSV files which are source of external tables
  • .dmp files, result of data pump export, will be stored there (the same goes for import)
  • UTL_FILE will create files in that directory

All that means that your idea of creating a file on a FTP server might not work just as easy.

However, there's a way : if you create directory (Oracle object) using UNC (Universal Naming Convention) which points to a directory on the FTP server, the file might be created there. Do some research about it; I know I once did that (put files onto an application server), but that was long time ago and I don't remember everything I did.

Another option you might consider is DBMS_SCHEDULER package. Suppose you create a file on the database server (which is the simplest option; if you do it right, it is more or less trivial). Once the procedure (which creates the file) is done, call DBMS_SCHEDULER.CREATE_JOB using the executable job type and call an operating system batch file that will copy the file from the database server to the FTP server.

That's all I can say about it; at least, you have something to research & think about.