Oracle Reports
Unofficial Oracle Reports Certification Review
Equity Press
Compiled By Mark Schmitz
Oracle Reports
ISBN 978-1-60332-066-5
Smashwords Edition
Copyright© 2008 Equity Press and ORACOOKBOOK all rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without either the prior written permission of the publisher or a license permitting restricted copying in the United States or abroad.
The scanning, uploading and distribution of this book via the internet or via any other means without the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions, and do not participate in or encourage piracy of copyrighted materials.
The programs in this book have been included for
instructional value only. They have been tested with
care but are not guaranteed for any particular purpose.
The publisher does not offer any warranties or
representations does not accept any liabilities with
respect to the programs.
Trademarks: All trademarks are the property of their respective owners. Equity Press is not associated with any product or vendor mentioned in this book.
Please visit our website at www.oracookbook.com
© 2008 Equity Press all rights reserved.
Question 1: Report on pre-printed stationery with different positions of detail records
Question 2: Printing chr(12) in oracle reports
Question 3: Null values in parameter
Question 4: How to show current time in report
Question 5: Currency Formatting In Reports
Question 6: 2 Records in 1 Row
Question 9: Text at the end of the report
Question 10: Display part of a text in BOLD
Question 11: REP-1247: Report contains uncompiled PL/SQL
Question 12: Oracle Reports 6i on 8i rdbms
Question 13: Report displays two lines instead of one line
Question 14: Formatting in reports
Question 15: Run Multiple Reports in Oracle Reports 6/6i
Question 16: How to print vertical records horizontally in single record
Question 17: Placeholder column
Question 18: Error Identifier 'GET_PRECISION' must be declared
Question 19: Report builder - Text assignment
Question 21: Correct solution to generate report to excel sheet
Question 22: calling report10g through form10g
Question 23: Report print hanging
Question 24: Creating a formula in Oracle reports - error
Question 26: Oracle reports builder
Question 27: Help in check printing
Question 28: Oracle Report Output size issue (need 0 bytes?)
Question 29: Automatic update reports if column added in database
Question 30: How do I begin to create a letter
Question 31: REP-1108 Report error
Question 32: Print formula column onto the last page
Question 33: Understanding & Variables in SQL for a report
Question 34: Error in the Report Execution
Question 35: Print Particular Section
Question 36: Reports source from more than one database
Question 37: How to draw lines between the records of report output
Question 38: Can we create our own triggers in Reports?
Question 39: Group by and summary
Question 40: from 000 to 999 parameter
Question 41: Maximum Length Of url in report 10g
Question 42: Display a message when report returns no data
Question 43: Printing images on ID cards
Question 44: How to handle exceptions in reports
Question 47: Two reports on single page
Question 48: Dynamically Changing Labels in Report 6i
Question 49: Group above report on HIERARCHY
Question 50: Parse a string and convert the text
Question 51: Print report in MS-Word
Question 53: runrep: not found
Question 54: Report Formatting
Question 55: Please see this error REP-1401 or ORA-01403
Question 56: Cascading prompt style LOV in report parameter form
Question 57: Insert parameter in report
Question 58: “Continued” message
Question 59: Trying to put two reports into one
Question 60: Print Selected Page from Report
Question 61: Dynamic number format
Question 63: Sequence in report runtime
Question 66: REP-1419:'beforereport': PL/SQL program aborted
Question 67: Remove space in 2nd page in group above reports
Question 69: Variable 'RT' and print 'Return from Supplier'
Question 70: TAB delimiter report is not functioning
Question 71: How to remove extra space in report output
Question 72: Report output to teletype printer
Question 73: Report in several pages
Question 74: Make a piece of text (not a field) BOLD or ITALIC
Question 75: Currency formatting
Question 76: Dynamic LOV in Report Parameter Form
Question 77: How to sort Report by formula column
Question 78: How to start a new page when groups change in Oracle report
Question 79: Implementing form letters
Question 80: How can I use formula column and place holder column?
Question 81: Display Dynamic image in report 6i
Question 82: Reference of Report Variables
Question 84: Make all columns the same height regardless of data?
Question 85: Report Output to Text File not working properly
Question 86: Set individual preferences for a specific user
Question 88: REP-1435: ‘cf_1formula’: Value of column ‘CP_1’ was truncated
Question 89: Data is getting printed in the next line
Question 90: How to show placeholder column in my report
Question 91: What is the difference between placeholder and formula column?
Question 92: Run custom report in oracle applications
Question 93: DataSource for Reports using title and segment
Question 94: How to display number in money format in reports 6i
Question 95: Print only odd page numbers in the report
Question 96: Problem with execute immediate in Report 6i
Question 97: How to create global temp table
Question 98: Header and Footer
Oracle Reports is a piece of software for developing reports against the data stored in an Oracle database. Oracle Reports consists of Oracle Reports Developer (a component of the Oracle Developer Suite) and Oracle Application Server Reports Services (a component of the Oracle Application Server).
The reports from Oracle Reports can printed directly or saved in the following formats:
- HTML
- RTF
- XML
- Microsoft Excel
Question 1: Report on pre-printed stationery with different positions of detail records
I need to build a report which will be printed on pre-printed sheets. In other words, the blank spaces on the sheet for name, address details will be provided by the report. It is a master-detail report with the following restrictions
1) Only 5 detail records should be shown on the report (regardless of how many are in the database). Will setting max records fetched to 5 be the solution?
2) The detail records should be placed on specific location on the pre-printed sheets, as follows:
-detail record 1 and 2 on page 3 of the report
-detail records 3, 4, 5 on page 4 of the report
A: Besides setting the "Maximum Records per Page" property of the repeating frame, perhaps you might be interested in opening the query Property Inspector (meaning: in a Data Model Editor, right-click on query and open its properties) and reading more about "Maximum Rows to Fetch" property.
Or, write a query, use a proper ORDER BY clause and have the ROWNUM set to a desired value in querie's WHERE clause. Something likeSQL> select ename from
2 (select ename from emp order by ename)
3 where rownum <= 5;
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
SQL>
As of your second question, it seems that you'll have to create "empty" pages; you might try to do that by drawing a large, empty rectangle and anchor "useful" objects to it in order to display them at the desired location.
Question 2: Printing chr(12) in oracle reports
I am trying to print a text which has chr(10) and chr(12) in Oracle Reports6.0.
I get new line with chr(10) but chr(12) gives a small square box and it does not give a page break.
Can anybody help?
A: It’s chr(13) for page break not chr(12)
Question 3: Null values in parameter
I have found one problem in my report; I am using "nvl" like this.
select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and claimstatus=nvl(:status,claimstatus)
The problem is that there are some null values in claimstatus and when I run report and no value input at claimstatus parameter then the reports discard all records that contains claimstatus values null. How can I get rid of this problem?
Can I assign run time value of claimstatus? If yes then how can I do this?
A: Use the following:
select a.warr_cd,b.prod_nm
from sv_warr a,sl_prod b
where a.prod_cd=b.prod_cd
and ((claimstatus is null) or (claimstatus is not null and claimstatus = :status))
Question 4: How to show current time in report
How do you show the live time in report? It should be updated automatically at run time view...
A: Select "Insert" menu - "Date and time", choose where to put it as well as a desired format and that should do it.
Question 5: Currency Formatting In Reports
I have a report where in I need to format based on the currency code and need to have 2 decimal places of precision.
For e.g.: USD ===> 2,300.00
EUR ===> 2.300.00
A: I'm afraid that there's no a "simple" solution. A default number format, such as NNGNNNDNN (where 'G' represents a "group" separator and 'D' a decimal point character) will be fine as long as you don't have to mix EUR and USD values. In other words: if you have a report and it is run in New York, USA, it will be fine. If you run it in Paris, France, it will be fine as well - you'll just have to ask a DBA to correctly set NLS_NUMERIC_CHARACTERS.
However, if you are running this report in Dubrovnik, Croatia, where tourists every year spend both Euros and Dollars, your customers will need to display both EUR and USD in the same report, and NLS_NUMERIC_CHARACTERS will not help.
You'll have to create additional table in order to map group separator and decimal character. Something like this (a stupid example; don't pay too much attention to it):
etc…
Now, how to properly format numeric values? The easiest way would probably be to find common groups and use such a code (in a formula column, which would return a CHARACTER, formatted as desired):
retval VARCHAR2(20);
BEGIN
IF currency IN ('EUR', 'GBP', 'HUF') THEN
retval := TO_CHAR(:some_value, '999.999.990,00');
ELSIF currency IN ('USD', 'ABC', 'DEF') THEN
retval := TO_CHAR(:some_value, '999,999,990.00');
ELSIF
...
END IF;
RETURN (retval);
END;
Question 6: 2 Records in 1 Row
My Requirement is:
First invoice should be printed in the first 3 columns and the second invoice should be printed from 4th to 6th column. Third invoice should be printed in the second line from 1st to 3rd column. Fourth invoice should be printed in the second line from 4th to 6th column and so on
Inv_No1 Inv_Date1 Inv_Amt1 Inv_No2 Inv_Date2 Inv_Amt2
Inv_No3 Inv_Date3 Inv_Amt3 Inv_No4 Inv_Date4 Inv_Amt4
Inv_No5 Inv_Date5 Inv_Amt5 Inv_No6 Inv_Date6 Inv_Amt6
How do I do this?
A: Set the repeating frame's Print Direction property to Across/Down.
How do I remove duplicate fields in my report? See the example below:
From the example if you observe except for the worktype code 15.1 the rest of all the codes are repeating the same type of work descr. How do I get rid of the duplicates for all other worktype codes except for 15.1?
A: Two simple options: use DISTINCT or GROUP BY:
SQL> select DISTINCT firm_name, worktype_code, worktype_descr
2 from test
3 order by to_number(worktype_code);
SQL> select firm_name, worktype_code, worktype_descr
2 from test
3 group by firm_name, worktype_code, worktype_descr
4 order by to_number(worktype_code);
SQL>
Or, if you want to do it using Report Builder's capabilities, create a GROUP based on 'firm_name' and 'worktype_code'.
I need to create a report and for this I first need to do the ETL part and I need to use a view from another database. How do I import a view in to the canvas?
A: Use a view as a data source in SELECT statement of the report's query (or one of available PL/SQL objects - report triggers, formula columns, etc.).
Question 9: Text at the end of the report
How do I display a message like "END OF THE REPORT" at the last page of the report?
A: The easiest way is to put that text at the end of the report in the Paper Layout. Another way is creating that text in the Trailer section of the report.
Question 10: Display part of a text in BOLD
I am designing one report in report 2.5, my requirement is to display some specific characters in bold, that characters are return by column function.
A: Use the format trigger to bold some specific text. Create additional field and place it exactly on the same location where the function field is. Make it bold and just select the field, right mouse click and go to the plsql. Write the required condition there i.e.
if :sal < 10000 then
return true;
else
return false;
end if;
In case of true the bold field will be visible otherwise not.
Question 11: REP-1247: Report contains uncompiled PL/SQL
I am getting this error message "REP-1247: Report contains uncompiled PL/SQL" whenever I run the Page Layout in Oracle Reports Developer. Is anyone familiar with this problem?
A: You get this error because you're trying to run a report (and yes by clicking on page layout, means run the report) but you have edit pl/sql without compiling it. Simply compile all and run again.
Question 12: Oracle Reports 6i on 8i rdbms
I am working on a requirement where I am returning a string to Oracle reports which is more than 4000 characters long. As we know reports 6i has a limitation and it does not allow the display characters to be more than 4000.