10 Replies Latest reply on May 30, 2012 7:37 PM by Billy~Verreynne

    procedure to upload or download file from database

    Daniel015
      open and download file from database in adf using pl/sql
      how can i check directory to upload a file something like this this in pl/sql this was in forms

      procedure download(i_dref integer, i_file varchar2) is

      file varchar2(50);
      dlin_id integer;
      l_success boolean;
      begin
           if not right_file (i_dref ,i_file ) then
                select file_name,dlin.id
                into file, dlin_id
                from sms_document_links dlin
                ,sms_document_references dref
                where dref.id = i_dref
                and dref.dlin_id = dlin.id
                ;

      l_success := webutil_file_transfer.DB_To_Client
      ( dir ||'\'|| file,
      'SMS_DOCUMENT_LINKS',
      'DOCUMENT_IMAGE',
      'id = '||dlin_id);
      end if;          
      end download;
        • 1. Re: procedure to upload or download file from database
          Billy~Verreynne
          PL/SQL code executes inside an Oracle server process.

          For a file upload, where does this process read the file from? It runs on the sever platform and only has file system access to whatever the oracle o/s account has on the server.

          For a file download, where does this process write the file to? It runs on the sever platform and only has file system access to whatever the oracle o/s account has on the server.

          Basic client-server principles.

          Where does the server get a file or data from? The client.

          Where does the server send a file or data to? The client.

          In today's web-based client server, this is easily done using a web browser as a client. Standard upload HTML form is used to upload (via the Apache server's mod_plsql module) into a database table. Standard download is done by a web-enabled PL/SQL procedure reading the file (as a LOB) from the files table and streaming that to the client (e.g. as a CSV file or video file, etc).
          • 2. Re: procedure to upload or download file from database
            Daniel015
            For a file upload,
            where does this process read the file from?= i read the file from my home directory e.g c:\mydocument

            It runs on the sever platform and only has file system access to whatever the oracle o/s account has on the server.

            For a file download, where does this process write the file to?=the file is been read from the database i just what to view the blog i have downloaded in my database
            • 3. Re: procedure to upload or download file from database
              Billy~Verreynne
              Tshifhiwa wrote:
              For a file upload,
              where does this process read the file from?= i read the file from my home directory e.g c:\mydocument
              This means the client s/w that you use to connect to the database, must upload the file to the database.
              For a file download, where does this process write the file to?=the file is been read from the database i just what to view the blog i have downloaded in my database
              View the file (LOB) in the database, how? The client s/w you use to connect to the database, is tasked with rendering the output it receives from the database for you to view. So what exactly should the client s/w do when it gets a 100MB "file" (LOB) from the database?

               
              The issue here with uploading and downloading is pretty much a client issue. You need to decide what client s/w you will be using, how you want the client s/w to submit files to the database for uploading, and how you want the client to act when it receives a file from the database.
              • 4. Re: procedure to upload or download file from database
                Daniel015
                hi i was using the folowing package to upload my image in form how can i do the same in adf


                i what to use this java class this example from www where does the uploadfile method define java upload

                public void uploadfile(){
                uploadedFile = file;
                BlobDomain file = null;
                file = FileOperations.writeToBlobDomain(uploadedFile);


                }

                public class FileOperations {
                public static synchronized BlobDomain writeToBlobDomain(UploadedFile file)
                throws SQLException, IOException {
                InputStream in = file.getInputStream();
                BlobDomain blobDomain = new BlobDomain();
                OutputStream out = blobDomain.getBinaryOutputStream();
                byte[] buffer = new byte[8192];
                int bytesRead = 0;
                while ((bytesRead = in.read(buffer, 0, 8192)) != -1) {
                out.write(buffer, 0, bytesRead);
                }
                in.close();

                return blobDomain;
                }
                public static synchronized void downloadFile( String fileName, BlobDomain blobDomain )
                {

                FacesContext facesContext = FacesContext.getCurrentInstance();
                ExternalContext extContext = facesContext.getExternalContext();

                Long length = blobDomain.getLength();
                String fileType = MimeTypes.getMimeType(fileName);
                HttpServletResponse response = (HttpServletResponse) extContext.getResponse();
                response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
                response.setContentLength((int) length.intValue());
                System.out.println(fileType);
                response.setContentType(fileType);
                try {
                InputStream in = blobDomain.getBinaryStream();
                OutputStream out = response.getOutputStream();

                byte[] buf = new byte[1024];
                int count;
                while ((count = in.read(buf)) >= 0) {
                out.write(buf, 0, count);
                }

                in.close();
                out.flush();
                out.close();
                facesContext.responseComplete();
                } catch (IOException ex) {
                System.out.println(ex.getMessage());
                ex.printStackTrace();
                }
                }
                }

                public class MimeTypes {

                public static String getMimeType(String fileName){
                String mime = null;
                String ext = fileName.toLowerCase();
                System.out.println("In MimeTypes:"+ext);
                if(ext.endsWith(".pdf")) { mime = "application/pdf"; }
                else if(ext.endsWith(".doc")) { mime = "application/msword"; }
                else if(ext.endsWith(".ppt")) { mime = "application/vnd.ms-powerpoint"; }
                else if(ext.endsWith(".rar")) { mime = "application/octet-stream"; }
                else if(ext.endsWith(".zip")) { mime = "application/zip"; }
                else if(ext.endsWith(".jpg")) { mime = "image/jpeg"; }
                else if(ext.endsWith(".tif")) { mime = "image/tif"; }
                else if(ext.endsWith(".csv")) { mime = "application/csv"; }
                else if(ext.endsWith(".pdf")) { mime = "application/pdf";}
                else if(ext.endsWith(".txt")) { mime = "application/text";}
                else if(ext.endsWith(".gif")) { mime = "image/gif";}
                else if(ext.endsWith(".xls")) { mime = "application/excel";}
                return mime;
                }

                Edited by: Tshifhiwa on 2012/05/29 1:48 PM
                • 5. Re: procedure to upload or download file from database
                  Daniel015
                  hi i was using the folowing package to upload my image in form how can i do the same in adf


                  i what to use this java class this example from www where does the uploadfile method define java upload

                  public void uploadfile(){
                  uploadedFile = file;
                  BlobDomain file = null;
                  file = FileOperations.writeToBlobDomain(uploadedFile);


                  }

                  public class FileOperations {
                  public static synchronized BlobDomain writeToBlobDomain(UploadedFile file)
                  throws SQLException, IOException {
                  InputStream in = file.getInputStream();
                  BlobDomain blobDomain = new BlobDomain();
                  OutputStream out = blobDomain.getBinaryOutputStream();
                  byte[] buffer = new byte[8192];
                  int bytesRead = 0;
                  while ((bytesRead = in.read(buffer, 0, 8192)) != -1) {
                  out.write(buffer, 0, bytesRead);
                  }
                  in.close();

                  return blobDomain;
                  }
                  public static synchronized void downloadFile( String fileName, BlobDomain blobDomain )
                  {

                  FacesContext facesContext = FacesContext.getCurrentInstance();
                  ExternalContext extContext = facesContext.getExternalContext();

                  Long length = blobDomain.getLength();
                  String fileType = MimeTypes.getMimeType(fileName);
                  HttpServletResponse response = (HttpServletResponse) extContext.getResponse();
                  response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
                  response.setContentLength((int) length.intValue());
                  System.out.println(fileType);
                  response.setContentType(fileType);
                  try {
                  InputStream in = blobDomain.getBinaryStream();
                  OutputStream out = response.getOutputStream();

                  byte[] buf = new byte[1024];
                  int count;
                  while ((count = in.read(buf)) >= 0) {
                  out.write(buf, 0, count);
                  }

                  in.close();
                  out.flush();
                  out.close();
                  facesContext.responseComplete();
                  } catch (IOException ex) {
                  System.out.println(ex.getMessage());
                  ex.printStackTrace();
                  }
                  }
                  }

                  public class MimeTypes {

                  public static String getMimeType(String fileName){
                  String mime = null;
                  String ext = fileName.toLowerCase();
                  System.out.println("In MimeTypes:"+ext);
                  if(ext.endsWith(".pdf")) { mime = "application/pdf"; }
                  else if(ext.endsWith(".doc")) { mime = "application/msword"; }
                  else if(ext.endsWith(".ppt")) { mime = "application/vnd.ms-powerpoint"; }
                  else if(ext.endsWith(".rar")) { mime = "application/octet-stream"; }
                  else if(ext.endsWith(".zip")) { mime = "application/zip"; }
                  else if(ext.endsWith(".jpg")) { mime = "image/jpeg"; }
                  else if(ext.endsWith(".tif")) { mime = "image/tif"; }
                  else if(ext.endsWith(".csv")) { mime = "application/csv"; }
                  else if(ext.endsWith(".pdf")) { mime = "application/pdf";}
                  else if(ext.endsWith(".txt")) { mime = "application/text";}
                  else if(ext.endsWith(".gif")) { mime = "image/gif";}
                  else if(ext.endsWith(".xls")) { mime = "application/excel";}
                  return mime;
                  }

                  Edited by: Tshifhiwa on 2012/05/29 1:48 PM
                  • 6. Re: procedure to upload or download file from database
                    Billy~Verreynne
                    Tshifhiwa wrote:
                    hi i was using the folowing package to upload my image in form how can i do the same in adf
                    You are showing Oracle Forms (client) code. And then Java client code.

                    Neither has anything to do with the subject matter of this forum - the server side languages called PL/SQL and SQL.

                    Nor did you indicate what the client is and what it expects from the Oracle database?

                    Is it a web browser? Is it Windows Excel? Is it a Visual Basic client? Is it a Perl/Python/PHP client?

                    What is it?

                    If the client is a web browser, and a direct Oracle connection is desired, then no client code is needed - only server-side PL/SQL code, executed via an Apache web server module called mod_plsql.

                    If you want to use a Java client, then you need to tell us how this Java client intends to access the Oracle database. Is this a fat client JDBC connection - client connecting directly to the dataase? Is this a thin client connecting via Java middleware (application tier like JBoss or Weblogic)? How does this Java code expect to pass the file to, and receive the file from, the Oracle database? As binary chunks? As a uuencoded Mime data stream? Something else?
                    • 7. Re: procedure to upload or download file from database
                      Daniel015
                      yes is a web browser am using weblogic and java jdbc to pass the file ,am using jdeveloper 11g
                      • 8. Re: procedure to upload or download file from database
                        Billy~Verreynne
                        In that case, refer to the Oracle® Database SecureFiles and Large Objects Developer's Guide.

                        It would however be much simpler using no Java and no Weblogic - and simply the mod_plsql Apache module, that provides a PL/SQL call interface.

                        The web browser can then upload a file (standard HTML upload file form) - via a single PL/SQL procedure. And the web browser can download the file via a single PL/SQL procedure. (the web browser URL contains the name of a PL/SQL web enabled procedure to call, and the URL query string contains the parameters to pass)

                        Using mod_plsql means significantly less moving parts, less complexity, better performance, and easier development and maintenance (no large and complex Java layer in between). In addition it is cheaper as no middleware software and hardware are needed.

                        Oracle provides a full blown framework and run-time engine for mod_plsql Apache applications, written fully in PL/SQL and SQL only. It is called Apex (Application Express) - and used by a significant number of Oracle customers (in the place of J2EE) for everything from small departmental web applications, to very large corporate web applications.
                        • 9. Re: procedure to upload or download file from database
                          Daniel015
                          will i be able to use mod_plsql in my adf java application am not using apex
                          • 10. Re: procedure to upload or download file from database
                            Billy~Verreynne
                            mod_plsql is an Apache module that provides a PL/SQL call interface to Oracle. Simplistically, it converts a URL into a stored procedure call - and convert the HTML buffer created by that procedure into a MIME stream, and returns via HTTP(S) response to web browser client.

                            This is not Apex specific. Yes, this is used to run Apex based PL/SQL stored procedure code. But it can run any configured web-enabled PL/SQL code.

                            So instead of a normal OCI or JDBC session that the client creates to execute stored procedures, the client makes a HTTP call (via Apache and mon_plsql) to execute the stored procedure.

                            So if your client code supports calls via HTTP - then yes, it can call PL/SQL code via the Apache mod_plsql module.