This discussion is archived
5 Replies Latest reply: May 3, 2013 10:00 PM by 1006904 RSS

pl/sql block

1006904 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points