5 Replies Latest reply: May 4, 2013 12:00 AM by 1006904 RSS

    pl/sql block

    1006904
      DECLARE
                V_BFILE BFILE;
                V_BLOB BLOB;
                V_LEN NUMBER;
                V_CHECK NUMBER;
                path varchar2(1000);
                path1 varchar2(1000);
                v_file_name varchar2(100):='New Microsoft Excel Worksheet.xlsx';
      BEGIN
      
              V_BLOB := EMPTY_BLOB();
         
              IF TRIM(SUBSTR(:P2_DOWNLOAD,LENGTH(:P2_DOWNLOAD)-1))='**' THEN  
          
                                   v_file_name := TRIM(SUBSTR(:P2_DOWNLOAD,1,LENGTH(:P2_DOWNLOAD)-2));
                                   path := get_uploadd_download_path;
                                   htp.p('new1'||path);
                                   path1 := path||:P2_ID;
                                   htp.p('new2'||path1);
      
                                   EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY MK_DIR1  as '''|| path1 ||''' ';
                                   V_BFILE := Bfilename('MK_DIR1',v_file_name);
          
                    IF Dbms_Lob.Fileexists(V_BFILE)=1 THEN
                            V_LEN := dbms_lob.getlength(V_BFILE);
                    END IF;
                    DBMS_OUTPUT.PUT_LINE(V_LEN);
          
                     dbms_lob.createtemporary(V_BLOB,TRUE,dbms_lob.call);
                     dbms_lob.open(V_BLOB,dbms_lob.LOB_READWRITE);
          
                      dbms_lob.fileopen(V_BFILE,dbms_lob.file_readonly);  
                      dbms_output.put_line('fileopen');              
          
                      DBMS_LOB.LOADFROMFILE (V_BLOB, V_BFILE, V_LEN);
                        --dbms_output.put_line('load from file');
                      DBMS_OUTPUT.PUT_LINE( dbms_lob.getlength(V_BLOB));
                      dbms_lob.fileclose(V_BFILE);  
        
                      owa_util.mime_header('application/text',FALSE);   
                      htp.p('Content-length: '|| V_LEN);
                      HTP.p ('Content-Disposition:  attachment; filename="'|| v_file_name);              
                      owa_util.http_header_close;    
                      WPG_DOCLOAD.DOWNLOAD_FILE(V_BLOB);    
            END IF;
                          :P2_DOWNLOAD := null;
      
      exception 
        when others then
          owa_util.http_header_close;
      
      END;
      
      {code}
      
      
      
      Thiis is my code which i am using it to download the file... in that sequency i am trying to open file , read file and then download file.
      
      but i am facing error when download process is start....
      
      Its stop download process..
      plz help me out.. 
      
      tell my mistakes ..
      thank you.. 
      
      Mahek...
      
      Edited by: 1003901 on May 3, 2013 2:46 AM
      
      Edited by: 1003901 on May 3, 2013 10:32 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
        • 1. Re: pl/sql block
          Marwim
          Hello Mahek,

          welcome to the forum.

          Your question is missing some important information
          {message:id=9360002}

          Most of all we need the error message you get.

          Regards
          Marcus
          • 2. Re: pl/sql block
            AlbertoFaenza
            Hi Mahek,

            welcome to the forum.

            When you put some code or output please enclose it between two lines starting with {noformat}
            {noformat}
            
            i.e.:
            {noformat}
            {noformat}
            SELECT ...
            {noformat}
            {noformat}
            
            This thread: {thread:id=2326594} seems describing exactly the same problem you are having.
            Have a look and see if it can help.
            
            Regards.
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: pl/sql block
              1006904
              Hello,

              Actually i am new user so please avoid some mistakes.

              Another thing is i am not getting any error.. and just the content of file is come up on browser..

              And downloading process stop working..

              Thank you,

              Mahek.
              • 4. Re: pl/sql block
                Billy~Verreynne
                1003901 wrote:
                ..snipped..
                owa_util.mime_header('application/text',FALSE);   
                htp.p('Content-length: '|| V_LEN);
                HTP.p ('Content-Disposition:  attachment; filename="'|| v_file_name);              
                owa_util.http_header_close;    
                WPG_DOCLOAD.DOWNLOAD_FILE(V_BLOB);    
                {code}
                Why do you send *binary* data (BLOB) as Mime type *text* ??
                • 5. Re: pl/sql block
                  1006904
                  hello sir...

                  actually, i have to improve the download code which was done by my senior.... my senior pass static path to "create or replace directory".... if i keep static path then download process is do properly....

                  but when i pass path with one variable then download process is stop.... it don't give me any type of error MSG.... just download process is stop and in browser file contains is shown...

                  why it is happens i don't know....
                  and why my senior put "owa_util.mime_header('application/text',FALSE);"... i don't know.... so please give me guidance why this happend

                  thank you,
                  mahek