Skip to Main Content
  • Questions
  • Printing a report to file in .pdf format

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jeffrey.

Asked: January 31, 2001 - 5:08 pm UTC

Last updated: August 31, 2006 - 1:16 pm UTC

Version: Report Builder 6.0.8.8.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I really do not have a lot of experience in regards to using adobe products so when I got an assignment of making a report and saving the output in .pdf format, I was totally clueless. I would appreciate it if you can give me some help in this.

Jeff

and Tom said...

Use the desformat parameter to reports:

DESFORMAT (RWBLD60)

Description In bit-mapped environments, DESFORMAT specifies the printer driver to be used when DESTYPE is FILE. In character-mode environments, it specifies the characteristics of the
printer named in DESNAME.

Syntax
[DESFORMAT=]desformat

Values

Any valid destination format not to exceed 1K in length. Examples of valid values for this keyword are hpl, hplwide, dec, decwide, decland, dec180, dflt, wide, etc. Ask your System
Administrator for a list of valid destination formats.

PDF Means that the report output will be sent to a file that can be read by a PDF viewer. PDF output is based upon the
currently configured printer for your system. The drivers
for the currently selected printer is used to produce the
output; you must have a printer configured for the machine
on which you are running the report.

HTML Means that the report output will be sent to a file that can be read by an HTML 3.0 compliant browser (e.g., Netscape 2.2).

HTMLCSS Means that the report output sent to a file will include stylesheet extensions that can be read by an HTML 3.0 compliant
browser that supports cascading style sheets.

HTMLCSSIE Means that the report output sent to a file will include style sheet extensions that can be read by Microsoft Internet Explorer 3.x.

RTF Means that the report output will be sent to a file that can be read by standard word processors (such as Microsoft Word).
Note that when you open the file in MS Word, you must
choose View Page Layout to view all the graphics and
objects in your report.

DELIMITED Means that the report output will be sent to a file that can be read by standard spreadsheet utilities, such as Microsoft Excel. Note that if you do not choose a delimiter, the default delimiter is a TAB..



Rating

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pdf reports

Munz, March 13, 2002 - 9:40 am UTC

Tom:

1. AS a followup to this, is there any way to have PL/SQL create a PDF report for you and then print the report via link.

I want to print a shipping address label from the database but I do not have oracle reports running on AIX server.

2. Would html print work for labels?

3. would you convert html to pdf format but then user will have to run acrobat and do that himself. I amtrying to get this automate so that when a user creates a shipping transcation it shoots the address to the printer.

4. You said above that pdf file is created using the drived for your personal printer. But how can pdf files get distributed to the web since each user has a different printer.

THank you,


Tom Kyte
March 13, 2002 - 11:12 am UTC

1) sure, if you know how to generate a PDF file that is. I don't know the format, but if you can discover it (the format), yes.

2) don't know.

3) don't know.

4) the above refers to the client server reports, its not the web version. the web version would download the pdf to the client browser, whence they can go file/print where ever.

Another possibility

Tony, March 13, 2002 - 11:41 am UTC

Jeffrey,

I recently had the same type of request involving dynamic generation of stored resumes into PDF. I solved the problem by pulling out the data in XML format (using the XDK) and using the rendering capabilities within Apache FOP.

You define the page with XSLT and have control over virtually every aspect of the layout. This may not be the best solution for you, but probably worth checking out...

xml.apache.org/fop/

pdf output from Java, using PDFLib

Harrison Picot, March 14, 2002 - 3:31 am UTC

Hi:

If you look on the Adobe site, there is a complete (5 meg pdf file) PDFreference explaining how to create a pdf files that current versions of acrobat can read. It is not that hard. An easier way to create pdf files from Java (or other language, but Java is likely where you will want to retreive your data from the database, so I mention it) is the PDFLib from Thomas Merz which you can learn more about at </code> http://www.pdflib.com/ <code> The java interface was largely the work of Paul Hazlett who I mention because when you help out for free, you should get some recognition.

A web page returning a pdf file with data from an Oracle database, seems to me anyway, an excellent way to save mailing a large detailed bill, and can keep it on line where you can find it. PDF, of course, can format data much better than html. The files you will create will be text files and pl/sql can create them just as easily as any other method, if you emit boilerplate, the results of your query, and some more boilerplate. I enclose an example of a small pdf file that creates two pages and an index (thumbnails, all the good stuff) in 1.6k. You can flesh it out into a two page report easily, and, more to the point, see that it is not that hard. Because it is all character, it is really compatible with PL/SQL, Java, C or whatever you want to use.

Cheers.

%PDF-1.3//9
1 0 obj
<< /Type /Catalog
/Pages 6 0 R
/Outlines 2 0 R
>>
endobj//74
2 0 obj
<< /Type /Outlines
/First 3 0 R
/Last 5 0 R
/Count 3
>>
endobj//145
3 0 obj
<< /Title (Mon First Document)
/Parent 2 0 R
/First 4 0 R
/Last 4 0 R
>>
endobj//233
4 0 obj
<< /Title (Mon Page Une)
/Parent 3 0 R
/Next 5 0 R
/Dest [7 0 R /Fit]
>>
endobj//322
5 0 obj
<< /Title (Mon Page deux)
/Parent 3 0 R
/Prev 4 0 R
/Dest [9 0 R /Fit]
>>
endobj//412
6 0 obj
<< /Type /Pages
/Kids [ 7 0 R
9 0 R]
/Count 2
>>
endobj//477
7 0 obj
<< /Type /Page
/Parent 6 0 R
/MediaBox [ 0 0 612 792]
/Contents 8 0 R
/Resources << /ProcSet [/PDF /Text]
/Font << /Myfont 11 0 R >>
>>
>>
endobj//632
8 0 obj
<< /Length 80 >>
stream
BT
/Myfont 18 Tf
100 600 Td
(This is Page one.) Tj
ET
endstream
endobj//735
9 0 obj
<< /Type /Page
/Parent 6 0 R
/MediaBox [ 0 0 612 792]
/Contents 10 0 R
/Resources << /ProcSet [/PDF /Text]
/Font << /Myfont 11 0 R >>
>>
>>
endobj//891
10 0 obj
<< /Length 80 >>
stream
BT
/Myfont 18 Tf
100 600 Td
(This is Page Two.) Tj
ET
endstream
endobj//995
11 0 obj
<< /Type /font
/Subtype /Type1
/Name /Myfont
/BaseFont /Arial
/Encoding /MacRomanEncoding
>>
endobj//1104
xref
0 12
0000000000 65535 f
0000000009 00000 n
0000000074 00000 n
0000000145 00000 n
0000000233 00000 n
0000000322 00000 n
0000000412 00000 n
0000000467 00000 n
0000000632 00000 n
0000000735 00000 n
0000000891 00000 n
0000000995 00000 n
trailer
<< /Size 12
/Root 1 0 R
>>
startxref
1094
%%EOF


Doubt

Aline, May 14, 2002 - 10:56 am UTC

I've read that when generating a rtf file from Reports we have to configure the MS Word page setup to see all the content of the report.
But a oracle support technician said to my client that is possible to see all the content from a rtf file in Word, without have to configure this.
I'ts really possible? I haven't found how to do this.



I am also in same problem

Bhari, June 09, 2002 - 9:57 pm UTC

Still i am wondering how i can acheive this .My case is i want to run a report in the BAckend without user interaction between particular intervals .I tried to put this to AskTom but it looks like full .Since this is a urgent requirement for me i want to get some help from this or atleast by the viewer's experience if not Mr.Tom .

Tom Kyte
June 10, 2002 - 7:08 am UTC

read up on the reports server -- it has facilities that do exactly this (run jobs in the background - on scheduled intervals)

hope this helps!

Yogeeraj, June 10, 2002 - 8:37 am UTC

Hello,

On my HP-UX box, i have one script that is scheduled (cron) to run at specific time which says:

$IAS_HOME/6iserver/bin/rwcli60 userid=query_user/query_user@mydb server=Repserver Repo
rt=myreport.rdf destype=file desname=/deploy/myreport-`dat
e +%d%m%y-%H%M`.pdf

I can also do it by scheduling it using the Windows Based Reports Queue Manager Utility.

Cheers
Yogeeraj

You might also look into DocBook

Daryl, June 10, 2002 - 4:49 pm UTC

The original poster didn't indicate whether he was using Oracle Reports. However, it's pretty easy to get Oracle 8i and 9i to generate XML. If you can make XML, you can make DocBook input. DocBook is a markup language that makes it possible to create documents that can ultimately generate final output in HTML and lots of other formats, including PDF.

Take a look at:
</code> http://www.docbook.org/ http://openjade.sourceforge.net/ http://www.netfolder.com/DSSSL/index.html http://www.tldp.org/HOWTO/mini/DocBook-Install/ <code>



Cascading Style Sheets

Matt, June 19, 2003 - 2:45 am UTC

I searched for Cascading Style Sheets and this thread was all that came up. Hopefully this is the best place for this question...

What support does PL/SQL Server Pages provide for Cascading Style Sheets?

I need to generate a number of web pages and want to manage the style and formatting of these whilst using PSP.

THanks in advance,



Tom Kyte
June 19, 2003 - 8:02 am UTC

since CSS is just a tag basically in the HTML document - psp 100% supports it, just do a view source on this page, you'll see my callout for it.

it is just printing out of HTML

Yong Wu, June 20, 2003 - 12:46 pm UTC

I used to use htmldoc to convert html to pdf.

</code> http://www.easysw.com/htmldoc/ <code>

Here is a way to generage pdf

1) write html file in pl/sql on server
2) pass the file handle to htmldoc to pdf using perl cgi


automating the process of generating PDF's

A reader, February 18, 2004 - 8:05 pm UTC

Hi Tom,
I have a situation where a report is generating a pdf file and I need to store the pdf file as a blob in the DB. The process of generating the PDF file and storing the PDF file needs to be automated through PL/SQL.

This is what I was planning on doing.

1) Create a bat file (windows) and run the bat file from within PL/SQL
---run.bat start-----
RWRUN60.EXE REPORT=SummaryReport.RDF P_Parameter1=123 USERID=report_user/report_pwd@abc.xyz.com server=Rep60_XYZ BATCH=YES DESFORMAT=PDF DESTYPE=FILE DESNAME=123.PDF
---run.bat end-----
2) Read the stored PDF file and write it to the blob

My questions
1) I would like to know if there are any PL/SQL packages for generating byte streams and storing the byte streams as a blob in the database(instead of generating the PDF file, storing it on the DB server and then saving it as a blob).
2) Your suggestions on how this can be done in a better way.

Thank you

Tom Kyte
February 19, 2004 - 8:24 am UTC

1) nope

2) you could use dbms_lob.loadfromfile to do step #2 instead of a custom program

Generating PDF's

Ben, February 19, 2004 - 9:27 am UTC

I need to do the same thing for a clinical research project database - at specified intervals, I need to generate a letter that would be sent to the patients, and store that document in the database.

My approach was to create a procedure to be executed weekly through the dbms_job package. Among other things, that PL/SQL procedure executes a host call to launch a Perl script, which generates the letter in PDF format, using the PDF::API2 library, and loads the letter up in the database.

PDF via FOP

Olga, March 17, 2004 - 5:56 am UTC

I use XML DB and the Apache FOP Framework for generating PDF. It works very well!

1. Convert the Query Output into a XML Document (with PL/SQL-XDB-Functions)

2. Use a (in DB or file system stored) XSL-Stylesheet to generate a XSL-FO-Document in the database (with PL/SQL-XDB-Functions)

3. Call a servlet via UTL_HTTP. The servlet reads the XSL-FO, generates the pdf and and stores it in a BLOB in the database.

It works very well!

I tried to import the FOP-libraries into the database, so that I could use JavaStoredProcedures instead of the servlet. But after importing 7000(!) Classes I got some errors I couldn't handle. Perhaps anybody has done it before and could give me a solution?




TO: A reader from Houston

Olga, March 17, 2004 - 6:04 am UTC

I want to do the same in this way:

1. Set up the report-server that you could request a report via an url in the browser

2. Get the response from a http-reports-request with UTL_HTTP

3. Write the UTL_HTTP-Output into a BLOB.

Bye,

Olga


XML DB and pdf Reports

denni50, April 29, 2005 - 9:25 am UTC

Tom

We have a third party report generator that I use to
process daily and monthly reports for mgt.

The reports get saved in .pdf format and emailed to
mgt. They are requesting that Reports be placed in a
more centralized area(we recently purchased a new IBM
e series server and I am building that into a DSS system).

One top executive is stationed in Atlanta and wants
to be able to login and view reports more efficiently.

We have xmldb option with 9iR2...have no experience with it but am researching possibilities on using xmldb for storing Report data from pdf files(if that is even a possibility).

Any suggestions/recommendations on what is the best way
to setup/convert pdf reports to html(or any other web based
format) is appreciated.

thanks








Tom Kyte
April 29, 2005 - 3:25 pm UTC

I asked Sean Dillon, my XML technologist, to take a look at this and here's what he had to say:
--

Hi there Denni50,

You have a variety of options if you're interested in using XML DB. Let me tell you how I might approach it and give you some options.

If you don't want to reinvent your entire process, you might consider utilizing XML DB's XML Repository to store the .PDFs on your E Series server. This way, your report generator stays the same. The .PDFs stay the same. Reporting frequency stays the same but the storage location to those reports becomes centralized and the accessibility becomes limitless.

The XML Repository is an Oracle9iR2 or greater feature that allows you to store not only XML documents, but any other kind of document inside the database using standards-based interfaces such as FTP, HTTP and WebDAV. You would take your documents from the report generator and store them in a folder, for instance, in the repository and then people could connect to the repository from their browser to view the reports. Since the repository is inside the database, you can enforce security through standard database security or use the repository's access control list features.

Another option you might look into is (provided Oracle had visibility to all the data) create XMLType views and publish them on the web using the DBUri servlet. The DBUri servlet is a HTTP-based servlet that runs alongside the database and can reach into the database to get database content and publish it to a web browser. You would create a view that queried your tables/data sources to generate an XML document. You could then build a URL w/ the DBUri servlet that could access that XML document from a web browser.

Now that you have web browser access to the XML documents, you would have to use XSLT FOP to transform an XML document into a PDF document using XSLT. That way the reports would happen on the fly, and the browser could transform the XML document into a PDF for viewing on the client side. This approach, obviously, is more work than the former... but could eliminate the need to generate each and every PDF and store them somewhere centrally where people may or may not use/see them, etc.

Hope that helps,

_smd_

thanks Tom and Sean

denni50, April 29, 2005 - 3:51 pm UTC

I kinda suspected I could use XML from what I've researched
and read...however I'm at the stage(taken from Tom's blog):

<quote>
2) Things you know that you don't know you know yet (you have the bits and pieces, just haven't put them all together)

3) Things you don't know that you know you don't know. (things you haven't used yet in real life)
<quote>

I have a test db I can fiddle with, using your options,
and figure out how to get this system up and running.

thanks!



PDF Template - Harrison Picot March 2001

Srikanth. S, April 29, 2005 - 4:32 pm UTC

I was hoping to get a template of this kind, like the one I got for creating SYLK format files using PL/SQL. Now, I can use this as a starting point to research on how to create PDF files using this template and Oracle data sources. Like a mail merge. Thanks buddy!

LL, May 01, 2005 - 1:56 am UTC

Perhaps the possibilities offered by PLPDF is more suited for your type of enhanced reporting.

Take a look at </code> http://www.plpdf.com/ <code>


generating PDF from XML -DB

Suhail, June 20, 2005 - 11:51 am UTC

Tom,

Could I get some insight knowledge about how to generate PDF from XML doc stored in XMLDB ( using XSLT FOP or any other method).

Thank you.

Tom Kyte
June 20, 2005 - 1:01 pm UTC

sort of outside the scope of the database.... we'll give you the XML, turning that into pdf is "outside" and not something I'm intimately with.

Oracle XSLT

Suhail, June 20, 2005 - 2:07 pm UTC

I beleive Oracle support Oracle XSLT processor, what is this about , does it not transform XML to a target format ( HTML, PDF etc)?

Suhail

Tom Kyte
June 20, 2005 - 2:53 pm UTC

do you have a style sheet to convert your XML into PDF??

if you do, we can certainly apply it.

to PDF

Suhail, June 22, 2005 - 10:04 pm UTC

yes , I do have style sheet but donot know how to convert it to PDF. I have XML file created in XMLDB , I have Style sheet stored in XMLDB.

Now , I am stuck here ,no idea how to proceed , I would like to generate PDF preferably from PL/SQL environment.


Tom Kyte
June 24, 2005 - 8:08 am UTC

I asked Sean Dillon to take a look at this, here's what he had to say:
--

Hi Suhail,

Well, there are a number of ways to approach this one. The most straight forward, since you have XML in XML DB and you have a stylesheet stored in XML DB, is to just use the XMLType member method "transform()". The return type for this method, however, is XMLType so I'm not sure if FOP generates valid XML or not (I'm assuming it does not as it's a PDF document, but I'm not sure). If it doesn't you'll have to look to the PL/SQL supplied packages of which there is a DBMS_XSLPROCESSOR package you can use.

When Tom says the PDF transformation is kind of out of the scope of the database, what he means is you'll have to use some other technology to perform this transform. For instance, we have tools like Oracle Reports, etc. that do PDF formatting. Given you're using XML and XSLT, you probably already know but just in case you don't, PDF generation in XML/XSLT is done using XML FOP. </code> http://xml.apache.org/fop/ <code>
So, provided your XSLT is based on FOP you can do this transform and get a PDF document out the other end.

Hope that helps.

_smd_

generating PDF from PL/SQL

Suhail, June 24, 2005 - 9:09 am UTC

Thank you Tom and Sean for taking time to answer my question. I looked into some code at following link about XMLType member method "transform()".

</code> http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10790/xdb08tra.htm#sthref843

I also looked about "DBMS_xslprocessor" at the following link 
http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10790/xdb10pls.htm#i1043472 <code>

It seems to me that the best way to go would be to use dbms_xslprocessor.processXSL method. I want to use Apache's FOP software to generate my PDF. My Apache's FOP works greate from command line, here is an example:


C:\FOP>fop -xsl emptablefo.xsl -xml emp.xml -pdf foo.pdf
[INFO] Using org.apache.xerces.parsers.SAXParser as SAX2 Parser
[INFO] FOP 0.20.5
[INFO] Using org.apache.xerces.parsers.SAXParser as SAX2 Parser
[INFO] building formatting object tree
[INFO] setting up fonts
[INFO] [1]
[ERROR] unknown font Garamond,normal,bold so defaulted font to any
[ERROR] unknown font Garamond,normal,bold so defaulted font to any
[WARNING] table-layout=auto is not supported, using fixed!
[INFO] Parsing of document complete, stopping renderer


It generates well formated PDF. My next question would be how would I call Apache's FOP from "dbms_xslprocess" or from PL/SQL. Do I have to load the FOP's jar file into the database and then create some kind of PL/SQL wrapper which then can be called after the dbms_xslprocess. Is this the right apparoach? How about the performance, user would like to generate the PDF from browsers on fly .

Regards






DBMS_XMLPROCESSOR? There's a DBMS_XMLPROCESSOR?

Dan Kefford, June 24, 2005 - 11:02 am UTC

Tom, Sean...

Is DBMS_XMLPROCESSOR a 10g-ism only? We're at 9.2.0.5 here.

It would be way cool to be able to use this in 9i database. Presently I'm relying on Oracle Reports Server 6i, and a Reports binary, to generate a pdf in a Web application (100% developed using the PL/SQL toolkit) that I currently support. I chose the pdf format because page breaks in the printed format are essential, no if's and's or but's.

The disadvantages to this setup are that I am not able to pass credentials from my Web application, thus requiring users to log into another environment, and the fact that it is a second piece of infrastructure, another point of failure and another technology to support.

Man... it would just rock to be able to generate XML on the fly from a simple query, apply a style sheet to set up for page breaks, and then somehow invoke FOP to generate a pdf, all within the PL/SQL environment.

As an aside, I _did_ look into XSQL pages, since XSQL pages can easily be transformed using FOP, but I could not figure out how to properly secure them, so that only authorized users could access them and that they only saw what they were authorized to see.



Tom Kyte
June 24, 2005 - 2:02 pm UTC

Hi Dan, there is no DBMS_XMLPROCESSOR package. It's the dbms_XSLprocessor package you're after. That being said, looking through OTN for XSL & FOP examples, I found some stuff you should look into. The XSQL pages are a XML publishing framework that runs on the Oracle database and the XSQL servlet. It's able to do the FOP publishing in a recent version of the XML Developer's Kits (XDK's), which are backwards compatible w/ 9.2.0.5. Go to </code> http://otn.oracle.com/tech/xml/ <code>and search for XML FOP. You'll find lots of information there on how to do these transformations

Re: DBMS_XMLPROCESSOR? There's a DBMS_XMLPROCESSOR?

Dan Kefford, June 24, 2005 - 3:12 pm UTC

Thanks for your reponse.

DOH! That was a typo... I meant to say/type DBMS_XSLPROCESSOR.

Like I said, I _did_ experiment with XSQL pages, but I could not figure out how to properly secure them. (In fact, even though I went through all the MetaLink articles and examples, I still was not able to get all the puzzle pieces together to generate a pdf.)

I had a tight deadline at the time that I was researching this, so I wound up settling on the current infrastructure.

One of these days, when I get some more bandwidth, I will reinvestigate this.




loading FOP's jar to DB

A reader, June 24, 2005 - 3:24 pm UTC

Are you suggesting that loading Apache's FOP jar file to database is not a good option , rather I shouldlook for xSQL framework. I found this article at OTN and found that they suggest the jar to be loaded to database.

</code> http://www.oracle.com/technology/sample_code/tutorials/cmsxdb/basic/files/setup.htm <code>

This way I can call the PL/SQL wrapper from any where and security can also be maintained by FGAC while creating an XML.

Suhail

Tom Kyte
June 24, 2005 - 6:45 pm UTC

I don't think Sean or I said that. If you can load it up and get it running, it would work.

Oracle report to Word

Ebenezer Tinosekwa, August 31, 2006 - 12:03 pm UTC

Thanx for the discussion on converting to pdf, this has be very helpful. But guys I need some assistance on generating a doc format from an RDF. I have a report which a generate to RTF and ask my user to open in Word for editing but they are compaining because of the frames that exist in RTF. Is there a way of having the output in doc format?


Tom Kyte
August 31, 2006 - 1:16 pm UTC

"of the frames"?? what is that.

Why not use HTML, bit easier than producing either of RTF or DOC formats.

Oracle reports in rtf editable

Anurag, October 15, 2008 - 2:07 am UTC

Hi Tom,

I need to generate report in rtf format and then want to insert tables from different word file. Which I plan to do manually. However, when I am generating report to rtf format, I am not able to insert table or text at beginning/end of page. Its all filled with text boxes. Could u pls help.

Thanks in advance.