RE: How to retrieve the pdf file stored as BLOB in Oracl e 9i
"David, Romeo B. (Govt)" <[email protected]> Fri, 28 Feb 2003 18:04:39 -0500
| Newsgroups | gmane.comp.db.oracle.devel |
|---|---|
| Message-ID | <LYRIS-1796914-893147-2003.02.28-23.03.29--gcdod-oracle#[email protected]> |
Look at AskTom.oracle: http://asktom.oracle.com/pls/ask/f?p=4950:8:52624307370274208::NO::F4950_P8_ DISPLAYID,F4950_P8_CRITERIA:232814159006, -----Original Message----- From: Sreedevi [mailto:[email protected]] Sent: Friday, February 28, 2003 2:19 PM To: Oracle Subject: [oracle] How to retrieve the pdf file stored as BLOB in Oracle 9i Hi, I'm trying to search some pdf documents using Oracle Text search and PSP (PL/SQL Server Pages). I was able to search the pdf files. But not able to display the document content. Using Oracle 9i Release 2, Oracle 9i AS Portal. I loaded a pdf file as a BLOB object in the database. I need to retrieve the blob and display the content as a .pdf document in the browser. Right now the blob is coming out as binary file onto the screen. Is it possible to display the content as plain text/HTML or as PDF doc? Any help or code samples are highly appreciated. Thanks. Below is my code: ------------------------ search_pdfservices.sql ------------------------ set define off create or replace package search_pdfServices as procedure showPDFDoc (p_id in numeric); end; / show errors; create or replace package body search_pdfServices as procedure showPDFDoc (p_id in numeric) is v_blob_selected BLOB; v_read_amount integer; v_read_offset integer; v_buffer RAW(2000); begin select text into v_blob_selected from search_pdf_table where tk = p_id; v_read_amount := 2000; v_read_offset := 1; begin loop dbms_lob.read(v_blob_selected,v_read_amount,v_read_offset,v_buffer); htp.print(v_buffer); v_read_offset := v_read_offset + v_read_amount; v_read_amount := 2000; end loop; exception when no_data_found then null; end; exception when others then -- null; wwerr_api_error_ui.show_html; end showPDFDoc; end; / show errors set define on ----------------------------- sample_pdf.psp ----------------------------- <%@ plsql procedure="search_pdf" %> <%@ plsql parameter="query" default="null" %> <%! v_results numeric := 0; %> <html> <head> <title>search_pdf Search </title> </head> <body> <% If query is null Then -- This part of the script allows a person -- to enter data on an HTML form. %> <center> <form method=post action="/pls/portal/portal.search_pdf"> <b>Search for: </b> <input type=text name="query" size=30> <input type=submit value=Search> </center> <hr> <% Else %> <p> <%! color varchar2(6) := 'ffffff'; %> <center> <form method=post action="/pls/portal/portal.search_pdf"> <b>Search for:</b> <input type=text name="query" size=30 value="<%= query %>"> <input type=submit value=Search> </form> </center> <hr> <p> <% -- select statement for doc in ( select /*+ FIRST_ROWS */ rowid, tk, title, score(1) scr from search_pdf_table where contains(text, query,1) >0 order by score(1) desc ) loop v_results := v_results + 1; if v_results = 1 then %> <center> <table border="0"> <tr bgcolor="#6699CC"> <th>Score</th> <th>Title</th> </tr> <% end if; %> <tr bgcolor="#<%= color %>"> <td> <%= doc.scr %>% </td> <td> <%= doc.title %> [<a href="/pls/portal/portal.search_pdfServices.showPDFDoc? p_id=<%= doc.tk %>">PDF</a>] </td> </tr> <% if (color = 'ffffff') then color := 'eeeeee'; else color := 'ffffff'; end if; end loop; %> </table> </center> <% end if; %> </body></html> --- Change your mail options at http://p2p.wrox.com/manager.asp or to unsubscribe send a blank email to %%email.unsub%%. --- Change your mail options at http://p2p.wrox.com/manager.asp or to unsubscribe send a blank email to [email protected].