1.
What
is a Lexical Parameter?
Lexical parameters are used to substitute multiple values at runtime and
are identified by a preceding ‘&’. Lexicals can consist of as little a one
line where clause to an entire select statement
Lexical Parameters are used to
execute query dynamically.
Example: An example of a lexical parameter usage in a select statement is
as follows
Select
* from emp, deptno
&where.
In the properties of the 'where' user
parameter, make sure that the data type of the 'where' user parameter is set as
character. If you know the maximum
length that your where clause is going be, you can set the width of the where
parameter to be slightly greater than that number. Otherwise, set it to some number like
100.
If your lexical parameter ('where')
width is not enough to hold the where condition assigned to it, you will
receive one of the following errors depending on your Reports version.
REP-0450 -
Unhandled exception,
and ORA-6502-
PL/SQL numeric or value error.
or
REP-1401 -
Fatal PL/SQL error in afterptrigger
and
ORA-6502-PL/SQL numeric or value error.
2.
What
is a Bind Variable?
Bind parameters are used to
substitute single value at runtime for evaluation and are identified by a
preceding ‘:’. An example of a bind
parameter in a select statement is provided below, where :P_EMP is the bind parameter reference.
Select
ename,empno
From emp
Where
empno= :P_EMP
These are used as tokens while registering concurrent program.
3.
Difference
between lexical and bind variable?
Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to
replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY,
and START WITH clauses of queries. Binds
may not be referenced in the FROM clause.
An example is:
SELECT ORDID,TOTAL
FROM ORD
WHERE CUSTID = :CUST
Lexical references are placeholders for text that you embed in a SELECT
statement. You can use lexical
references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY
, ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in
PL/SQL. Before you reference a lexical
parameter in a query you must have predefined the parameter and given it an
initial value. An example is:
SELECT ORDID, TOTAL
FROM &ATABLE
4.
How
many types of Triggers are there and what are they? Tell their sequence of
execution.
Report triggers execute PL/SQL functions at specific times during the
execution and formatting of your report.
Using the conditional processing capabilities of PL/SQL for these
triggers, you can do things such as customize the formatting of your report,
perform initialization tasks, and access the database. To create or modify a report trigger, use
Report Triggers in the Object Navigator.
Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report
triggers (you cannot create new global report triggers):
Before Parameter Form trigger
After Parameter Form trigger
Before Report trigger
Between Pages trigger
After Report trigger
Before Report trigger and After Report trigger should be declared
compulsory. In the Before Report trigger we declare the srw.user_exit(‘ fnd
srwinit’) user exist and in the After Report trigger srw.user_exit (‘fnd srwexit’)
The sequence/order of events when a report is executed is as follows:
Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).
2 After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter
Form).
3 Report is "compiled."
4 Queries are parsed.
5 Before Report trigger is fired.
6 SET TRANSACTION READONLY is executed (if specified via the READONLY argument
or setting).
7 The report is executed and the Between Pages trigger fires for each page except the last one.
(Note that data can be fetched at any time while the report is being formatted.) COMMITs
can occur during this time due to any of the following--user exit with DDL, SRW.DO_SQL
with DDL, or if ONFAILURE=COMMIT, and the report fails.
8 COMMIT is executed (if READONLY is specified) to end the transaction.
9 After Report trigger is fired.
10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the
ONSUCCESS argument or setting.
Cautions
=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which the
report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid
throughout the execution of the report. In steps 7 through 9, avoid DML statements that
would modify the contents of the tables on which the report is based. Queries may be
executed in any order, which makes DML statements unreliable (unless performed on tables
not used by the report).
2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDL
statement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If you
are using READONLY, this will prematurely end the transaction begun by SET
TRANSACTION READONLY.
Report trigger restrictions
=============================
1. If you are sending your report output to the Runtime Previewer or Live Previewer, you
should note that some or all of the report triggers may be fired before you see the report
output. For example, suppose that you use SRW.MESSAGE to issue a message in the
Between Pages trigger when a condition is met. If there are forward references in the report
(e.g., a total number of pages displayed before the last page), Report Builder may have to
format ahead to compute the forward references. Hence, even though you have not yet seen
a page, it may already have been formatted and the trigger fired.
2. In report triggers, you can use the values of report-level columns and parameters. For
example, you might need to use the value of a parameter called COUNT1 in a condition
(e.g., IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.
3. In the Before and After Parameter Form, and Before and After Report triggers, you can set
the values of parameters (e.g., give them a value in an assignment statement, :COUNT1 =
15). In the Before and After Report triggers, you can also set the values of report-level,
placeholder columns.
4. In the Between Pages trigger, you cannot set the values of any data model objects. Note also
that the use of PL/SQL global variables to indirectly set the values of columns or parameters
is not recommended. If you do this, you may get unpredictable results.
5. If you run a report from Report Builder Runtime (i.e., not the command line or
SRW.RUN_REPORT), you should commit database changes you make in the Before
Parameter Form, After Parameter Form, and Validation triggers before the report runs.
When running in this way, these triggers will share the parent process’ database connection.
When the report is actually executed, however, it will establish its own database connection.
6. A lexical reference cannot be used to create additional bind variables after the After
Parameter Form trigger fires. For example, suppose you have a query like the following
(note that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you
must specify the value in the After Parameter Form trigger or earlier. You would get an
error if you supplied the following value for the parameter in the Before Report trigger. If
you supplied this same value in the After Parameter Form trigger, the report would run.
WHERE SAL = :new_bind
Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).
2 After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter
Form).
3 Report is "compiled."
4 Queries are parsed.
5 Before Report trigger is fired.
6 SET TRANSACTION READONLY is executed (if specified via the READONLY argument
or setting).
7 The report is executed and the Between Pages trigger fires for each page except the last one.
(Note that data can be fetched at any time while the report is being formatted.) COMMITs
can occur during this time due to any of the following--user exit with DDL, SRW.DO_SQL
with DDL, or if ONFAILURE=COMMIT, and the report fails.
8 COMMIT is executed (if READONLY is specified) to end the transaction.
9 After Report trigger is fired.
10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the
ONSUCCESS argument or setting.
Cautions
=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which the
report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid
throughout the execution of the report. In steps 7 through 9, avoid DML statements that
would modify the contents of the tables on which the report is based. Queries may be
executed in any order, which makes DML statements unreliable (unless performed on tables
not used by the report).
2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDL
statement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If you
are using READONLY, this will prematurely end the transaction begun by SET
TRANSACTION READONLY.
Report trigger restrictions
=============================
1. If you are sending your report output to the Runtime Previewer or Live Previewer, you
should note that some or all of the report triggers may be fired before you see the report
output. For example, suppose that you use SRW.MESSAGE to issue a message in the
Between Pages trigger when a condition is met. If there are forward references in the report
(e.g., a total number of pages displayed before the last page), Report Builder may have to
format ahead to compute the forward references. Hence, even though you have not yet seen
a page, it may already have been formatted and the trigger fired.
2. In report triggers, you can use the values of report-level columns and parameters. For
example, you might need to use the value of a parameter called COUNT1 in a condition
(e.g., IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.
3. In the Before and After Parameter Form, and Before and After Report triggers, you can set
the values of parameters (e.g., give them a value in an assignment statement, :COUNT1 =
15). In the Before and After Report triggers, you can also set the values of report-level,
placeholder columns.
4. In the Between Pages trigger, you cannot set the values of any data model objects. Note also
that the use of PL/SQL global variables to indirectly set the values of columns or parameters
is not recommended. If you do this, you may get unpredictable results.
5. If you run a report from Report Builder Runtime (i.e., not the command line or
SRW.RUN_REPORT), you should commit database changes you make in the Before
Parameter Form, After Parameter Form, and Validation triggers before the report runs.
When running in this way, these triggers will share the parent process’ database connection.
When the report is actually executed, however, it will establish its own database connection.
6. A lexical reference cannot be used to create additional bind variables after the After
Parameter Form trigger fires. For example, suppose you have a query like the following
(note that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you
must specify the value in the After Parameter Form trigger or earlier. You would get an
error if you supplied the following value for the parameter in the Before Report trigger. If
you supplied this same value in the After Parameter Form trigger, the report would run.
WHERE SAL = :new_bind
5.
What
is a Format Trigger?
Format triggers are PL/SQL functions executed before the
object is formatted. The trigger can be
used to dynamically change the formatting attributes of the object. The function must return a Boolean value
(TRUE or FALSE). Depending on whether
the function returns TRUE or FALSE, the current instance of the object is
included or excluded from the report output.
You can access format triggers from the Object Navigator, the Property
Palette, or the PL/SQL Editor.
A format trigger is a PL/SQL function executed before an
object is formatted. A trigger can be
used to dynamically change the formatting attributes of the object.
6.
What
is Anchoring?
It is a feature thru which we can control the position
of the boiler plate or data fields in layout.
Anchors are used to determine the vertical and
horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol is
attached to the parent object.
When you create a default layout, Reports will create
some of its own implicit anchors. These
are not visible. There may be occasions when you want to create your own
explicit anchors to force objects to be positioned together or to conditionally
specify when the object prints.
You create an
explicit anchor as follows:
1. Select the Anchor tool in the Layout Tool
Palette.
2. Click on an edge of the Child object.
3. Move the cursor to the edge of the Parent
object and double click to fix the anchor.
You can
position the anchor at any distance down the edge of the object. The distance is a percentage of the total
length of the edge. You can adjust this
position in the anchor property sheet.
Examples of
using explicit anchors:
ANCHORING
BOILERPLATE TO A FRAME
--------------------------------
You may want to
display some boiler plate to the right of, and half way down a vertical list of
records.
In this case,
you would create an anchor from the child boilerplate to the parent, group or
repeating frame. Ensure the parent end
point is 50% down the right edge of the frame.
ANCHORING
CONDITIONAL OBJECTS
----------------------------
To adjust the
position of a layout object if the anchoring parent does not display, you can
define your explicit anchor as collapsible either horizontally or
vertically. The child layout object then
collapses, to suppress additional spacing, if the parent object does not print.
An example of
where you might use this would be on Mailing Labels.
Mailing Labels often include optional fields to allow
variable number of lines in an address.
You may want to suppress the fields that are null, so that the address
in the labels does not have gaps between the lines.
For example:
f_name
f_address1
f_address2
f_address3
f_address4
where
f_address2 is an optional field.
1. Select f_address2 in the layout editor and go
into the property sheet.
2. In Reports
V2.5, under the general layout tab, click on the Format Trigger
Edit button
to create the following format trigger.
In other
versions of Reports, under advanced layout, click on the Format
Trigger to
create the following format trigger.
FUNCTION
f_address2 RETURN BOOLEAN IS
BEGIN
IF
:address2 IS NULL THEN
RETURN
(FALSE);
ELSE
RETURN
(TRUE);
END IF;
END;
3. Then create
an anchor from f_address3 (the field below) upto to f_address2 (the optional
field). In the anchor properties place a
check in the collapse vertically check box.
4. Create
another anchor, this time from f_address4 to f_address3, again setting it to
collapse vertically. This process needs
to be done for all the fields below the optional field to avoid any unwanted
spaces.
7.
What
is Frame and Repeating Frame?
Frames are used to surround other objects and protect them from being
overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a
group, to surround column headings, or to surround summaries.
Repeating frames are place holders for records. Repeating frames print once for each record
of a group and control record-level formatting.
Reports will generate one repeating frame for each group when you create
a default layout.
Reports will place containers of columns inside of the
frames. Each repeating frame retrieves
only one row in its fetch cycle for any one repetition. Until it is constrained by another frame, it
will repeat itself until the while loop condition can no longer be satisfied.
We give group
in data model as source to repeating frame.
8.
What
are Confined Mode and Flex Mode?
Confined mode allows objects to be locked into the place in the layout.
Objects are maintained within their containers.
CONFINE mode is not for a specific
object, but applies to all objects on the layout when it is enabled
(locked).When it is turned off (unlocked), you are allowed to move an object
outside its surrounding frame. When it is turned on (locked), you are unable to
move an object outside its surrounding frame.
This is to prevent unnecessary 'Frequency Errors'.
Flex mode preserves the layout
structure while allowing expanding and shrinking of the layout.
FLEX mode, when enabled, allows
surrounding frames to grow as an object is resized or moved. Only one object at a time can be moved either
vertically or horizontally, not diagonally.
9.
What
are User Exits?
You build user exits when you want to pass control
from Report Builder to a program you have written, which performs some
function, and then returns control to Report Builder.
You can write
the following types of user exits:
* ORACLE Precompiler user exits
* OCI (ORACLE Call Interface) user
exits
* Non-ORACLE user exits.
User exits can perform the following tasks:
* Perform complex data
manipulation
* Pass data to Report Builder from
operating system text files
* Manipulate LONG RAW data
* Support PL/SQL blocks
* Control real time devices, such as
a printer or a robot
You can use
user exits for other tasks, such as mathematical processing.
However, it is recommended that you perform such tasks
with PL/SQL within Report Builder itself.
Ex: FNDSRWINIT, FNDSRWEXIT.
10.
How
do I Register a Custom Report?
Step 1: Register a concurrent program executable
Navigate
to the Define Executable form (AOL Reference manual pg 9-84)
This
determines the type of program being run,ie an Oracle Report. Fill in the
executable name, application and execution method. For the Execution File, fill
in just the filename. The concurrent manager will look in the appropriate
directory under the application's top directory.
For
spawned programs, the file must be in the bin directory, for Oracle Reports the
rdf file must be in the srw directory.
For PLSQL
concurrent programs, put the name of the stored procedure.
Step
2: Define the concurrent program
Navigate to the Define Concurrent Program form (AOL Reference manual pg
9-87)
This
form links a concurrent program to the executable you just defined, as well as
defines the programs parameters, incompatibilities, and other options.
Enter the concurrent program name, application, short
name and description. Check Standard Submission if you want to be able to
submit this program from the Standard Report Submission form.
Enter the name of the executable you defined and any
report information if necessary. Also define any parameters your program needs
here and any incompatibilities.
Step 3:
Add the concurrent program to a Report Group
First
you will need to find the name of the Report Group to use.
Go to
Security->Responsibility and query the responsibility you want to run the
program with.
It
should show a Report Group name. Query this name in
Security->Responsibility->Report
Add your new program to
the list of available programs. Now when you go to submit a request with this
responsibility, you will be able to submit your custom program.
11.
What
is a Token?
Token is used to attach a bindvariable to a report parameter while
registering the report as concurrent program.
12.
What
is the use of ‘Send to Back’ and ‘Bring to Front’?
To change the order in which objects are layered on top of each other.
Send to Back to move the object behind all other objects.
Bring to Front to move the object in front of all other objects.
13.
If
2nd parameter value is based on 1st parameter then how do
u declare it?
Let v2 be the value set definition of 2nd parameter and v1 be
the value set definition for the first parameter then
In the value set definition of v2 = value $FLEX$.v1
14.
What
are Summary Column, Place holder Column, and Formula Column?
A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you
can create the following summaries: sum,
average, count, minimum, maximum, % total.
You can also create a summary column manually in the Data Model view,
and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.
A placeholder is a column for which you set the data type and value in
PL/SQL that you define. You can set the
value of a placeholder column in the following places. A place holder column
stores a value which we can refer in the layout.
A formula column performs a user-defined computation on another column(s)
data, including placeholder columns. Formula columns should not be used to set
values for parameters.
15.
How
do u hide fields in a Report?
Ans: Using the Format Trigger
we can hide the fields.
/* Suppose that you are building a master/detail report
** and, if no detail records are retrieved for a master
** record, you do not want the boilerplate labels to
** appear. To do this, you first
create a summary
** column called MYCOUNT with a Function of Count in
** the source group of the master repeating frame.
** In the format trigger for the group frame that
** surrounds the detail repeating frame and its labels,
** you enter the following:
*/
function my_formtrig return BOOLEAN is
begin
if :mycount = 0 then
return (false);
else
return (true);
end if;
end;
16.
What
kinds of reports u have worked on?
17.
Name
Custom Reports and…--------------------------------------
18.
How
many types of Report formats we have?
Custom Reports and Standard reports
19.
What
is the minimum number of groups required for a Matrix type report?
To create a matrix report, you need at least four groups: one group must
be a cross-product group, two of the groups must be within the cross-product
group to furnish the "labels," and at least one group must provide
the information to fill the cells. The
groups can belong to a single query or to multiple queries.
A matrix (cross tab) report
contains one row of labels, one column of labels, and information in a grid
format that is related to the row and column labels. A distinguishing feature of matrix reports is
that the number of columns is not known until the data is fetched from the
database.
View the video report builder help
20.
What
is the difference between Bitmap and Character based reports? Explain in
detail.
Bitmap vs. Character-Mode Report Design
Here is an
example to help explain how Oracle Reports are designed and printed in both the bitmap and
character-mode environments.
Assume you
wish to print "Cc" where "C" is a different font and a
larger point size than "c" and is in boldface type (where
"c" is not).
In Oracle
Reports Designer, bitmap mode, you can make "C" bold and in a different font and point size than
"c". This is because you are
generating postscript output. Postscript
is a universal printer language and any postscript printer is able to interpret
your different design instructions.
In Oracle
Reports Designer, character mode, the APPLICATIONS STANDARDS EQUIRE the report
to be designed in ONE FONT/ ONE CHARACTER SIZE.
Character mode reports generate ASCII output. In ASCII you cannot dynamically change the
font and character size. The standard is in effect so a report prints as
identically as possible from both conventional and postscript printers.
Bitmap vs.
Character-Mode Report Printing
These
sequences contrast the two printing environments. In postscript, "C"
can be in a different font and point size than "c". Both or either could also be bold, for
example.
In ASCII,
"C" must be in the same font and character size as
"c". Both or either could also
be bold, for example.
Oracle
Reports
Designer
|
| |----- ar20runb ------ Postscript
---- Postscript
--- "Cc"
| | executable language printer output
| |
"Cc"---
|
|
|----- ar20run ----*-- ASCII
--------- Printer
------ "cc"
executable | characters output
|
|
SRW
driver
(for
bold, underline,
page
break escape sequences)
21.
What
Printer Styles are used for? Did you develop any printer styles?
Srw.driver
22.
How
do you fix a performance problem in a Report?
Check Report main query and fine tune it.
Create indexes on columns used in where condition (eliminate full table
scan)
Enable Trace(set trace on in before report and set trace off in after
report)
Before Report:
srw.do_sql('alter session set
sql_trace=true');
After Report:
srw.do_sql('alter session set sql_trace=false');
Trace file will be generated at location:
select value from
v$parameter
where name = 'user_dump_dest';
To better see execution plans in a trace file, you
need to format the
generated trace file with tkprof statement.
23.
What
is the significance of p_conc_request_id?
P_conc_request_id is declared as the user parameter for reports which
will get org specific data. P_conc_request_id datatype is character and
length is 15.
24.
How
to call a stored procedure in the report? What is the use of that?
Package.prcedure
25.
The
differences between forms 4.5 and forms 6i?
26.
How
do you set ORG_ID in a SQL*Plus session?
Call
the Below Anonymous pl/sql block.
BEGIN
fnd_client_info.set_org_context(‘204');
END;
Or
exec dbms_application_info.set_client_info(‘org_id’);
27.
The
differences between reports 2.5 and 6i?
28.
While
registering a report and a pl/sql block we pass some parameters, for any pl/sql
block we pass two additional parameters. Can u list them?
p_errorcode and p_errorbuffer as out parameters in main procedure.
It requires 2 IN
parameters for a PL/SQL procedure that's registered as a concurrent program in
Apps. They are
1. errcode IN
VARCHAR2
2. errbuff IN
VARCHAR2
29.
How
we can call from form to form, form to report?
Calling a Form from another Form:
FND_EXECUTE(…);
NOTE: The calling and called Forms must be registered with Applications.
Calling a Report from a Form: FND_REQUEST.SUBMIT_REQUEST(…);
NOTE: This method can be used to call any concurrent program.
30.
What
are logical page and physical page?
In the Runtime Previewer, you can scroll though a single page of report
output, page through the entire report, and split the screen to view different
sections of the same report concurrently.
A physical page (or panel) is the size of a page that will be output by
your printer. A logical page is the size
of one page of your actual report (it can be any number of physical pages wide
or long). The Runtime Previewer displays
the logical pages of your report output, one at a time.
31.
Why
is ref cursor is used in the reports?
Dynamic cursor
32.
When
we create a report we use the tables, there is some difference when we use the
multi-org tables and ordinary tables, can u tell the difference?
Set p_conc_request_id for org specific tables.
33.
What
is a template and what is its use. We have predefined template and we can
define user-defined template. Can u tell why we use the user-defined template?
34.
I
moved this field into that repeating frame, but I’m still getting a” frequency below its group” error?
35.
I
must put a repeating frame around these fields. How do I do this easily?
36.
I
switched the page size to 11 x 8.5, but the printer still prints in Portrait?
37.
We
have 2 different databases, and each system has 2 tables. Know there is
a link provided between them. The client want a report to be developed based on
the 4 tables that r there in the 2 different databases. The solution must be
efficient?
Use database links
1 comment:
Very well written and thoroughly analysed article. Thanks a lot.
Post a Comment