Interview Questions
1.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.
Assume that the two databases be DB1 and DB2. At one time I could connect to only one database say DB1. Now I should able to access the tables in DB2 from DB1. First I create a DBlink in DB1 that access the tables in DB2. Using the DBlink, create snapshots for each of the tables in DB2. Now we can use these Snapshots in query, as if like tables in DB1.
The purpose for creating snapshot is both security and to reduce the network load, for each access of the tables in DB2.
2.what is the difference between _all and with underscore all tables.
Tables that end with all are belong to multi-org. They have a field called ORG_ID.
3.Differences between OE and OM.
None of the new features in R11i Order Management or Shipping Execution have been backported to R11 Order Entry or earlier. The data model changes make the effort impractical.
The database appears to support the older SO_% tables and the newer OE_% tables for Order Management.
The concurrent process, order import, pick up the data from SO_HEADERS_INTERFACE_ALL and populate both the older SO_HEADERS_ALL and the newer OE_ORDER_HEADERS_ALL table. The SO_HEADERS_ALL table is present for those customers who are upgrading from 10.7 to 11i. The tables are there as part of the installation to facilitate the upgrade process.
The 'OE:' profile options are still accessible even though these profile options are not used in 11i. These profile options have been replaced by the 'OM:' profile options.
4. we have a system with 10.7 apps,in that we have 2.5 reports and 4.5 forms. Now there is upgrade from 10.7 to 11i.in that case the reports and forms must be converted into 6i. What r the steps we perform.list them.There are a minor changes in the PL/SQL Script in Forms 4.5 and Reports 2.5 with that of Forms6i and Reports6i. The differences are…
CHAR replaced by VARCHAR2
String length required for VARCHAR2
OUT and IN OUT default expressions removed
NULL added to RETURN in functions
Obsolete built-in
LENGTH function returns NULL for zero-length string
Passing NULL to overloaded subprogram does not resolve properly
Missing RETURN statement in function
5. The differences between reports 2.5 and 6i
ie 4t question answer
6. The differences between forms 4.5 and forms 6i.
ie 4t question answer
7. How is the stored procedures in the report are called. what is the use of that.
There is no sperate method for calling a database stored procedure in Oracle Reports. When a procedure is called form Oracle Report and a procedure with that name is available at several places namely Program Units, Attached Library and Database. The order will be..
1. Program Units
2. Attached Library
3. Database.
8.what is mutating table and mutating error .Through any example explain how we any over come that error.
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress.
Tables are never considered mutating for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. Views are not considered mutating in INSTEAD OF triggers.
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.
9.what r pl/sql table and explain were in ur project u have used that tables.what r the pl/sql records,and advantages of pl/sql tables and records.
A PL/SQL table can store rows (not just a column) of Oracle data. PL/SQL tables of records make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. We can even use PL/SQL tables of records to simulate local database tables. Attributes are characteristics of an object. Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.
PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
"global temporary tables" are not in-memory structures. But they do offer greatly-reduced disk I/O compared to standard tables (and therefore usually a significant performance improvement) and they also offer the ease-of-use of standard tables, since standard SQL can be used with them, no special array-processing syntax is required.
For processes where every bit of performance improvement is critical, PL\SQL tables are the best way to go.
10. what is data mapping and what type of mapping u have done in ur carrer or in the recent projects.
In this task, we map the data elements from the legacy systems to the target Oracle Applications.
11.What is Data cleaning and testing.
Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.
Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables.
Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit.
12.what r the differences between oracle data base 7 and 8.
Oracle 7 is a simple RDBMS, where as Oracle 8 is ORDBMS i.e., RDBMS with Object Support. The main add-ons in version 8 are…
Abstract Datatypes
Varrays
PL/SQL Tables
Nested Tables
Partitioned Tables
etc.,
13.what r the lexical parameters and were did u used this in ur project .
We define lexical parameters in Oracle Reports. Lexical parameters can dynamically replace clauses in the Select statement in the data model and even the whole select statement.
A lexical reference replaces any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&). If the parameter object does not exist, Report Builder does not create. We must always create the parameter for a lexical reference in the Object Navigator.
14.while registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can u list them?
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
15.when “no data found” exception occurs, give any example.A SELECT statement in a PL/SQL block should return one and only one row. If it fails to return a row, then we get this exception.
16.How u can delete the duplicate records from the table.
delete from a
where a.rowid != (select max(b.rowid)
from b
where a. = b. and
a. = b. ...
group by a., a....);
------------------------------------------------------------
Method 1: SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Method 2: SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Method 3: (thanks to Kenneth R Vanluvanee)
SQL> Delete from my_table where rowid not in(
SQL> select max(rowid) from my_table
SQL> group by my_column_name );
Method 4: (thanks to Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
17. In matrix reports minimum how many groups are required.
A matrix (crosstab) 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.
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.
18.How we can call from form to form, form to report.
Form to Form:
i) call_form('your form name', hide, do_replace);
ii) open_form('your form name');
The first version can be used to hide the calling form until the called form has been exited The second version leaves both forms open at the same time.
Form to Report:
Using the RUN_PRODUCT built-in procedure.
Eg: Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
Where pl_id is the parameter list through which we pass parameter values to the report.
19.why is ref cursor is used in the reports.
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor. We base a query on a ref cursor when you want to:
i) More easily administer SQL
ii) Avoid the use of lexical parameters in your reports
iii) Share datasources with other applications, such as Form Builder
iv) Increase control and security
v) Encapsulate logic within a subprogram
Furthermore, if we use a stored program unit to implement ref cursors, we receive the added benefits that go along with storing your program units in the Oracle database.
21.what r the transaction types in OM.With the release of Oracle Order Management 11i, Order Cycles have been replaced by Oracle Workflow definitions, and Order Types have been replaced by Order Management Transaction Types. Order Management provides seeded Workflow process definitions for both orders and lines, and Order Management also enables you to define both order header and Order Line transaction types.
Note: Order Management provides NO seeded transaction types. For existing Oracle Order Entry customers, Order Management will update existing Order Types to order and line transaction types during the upgrade process.
Order Management Transaction types:
i) Determine the workflow processes executed for both the order and line
ii) Can act as sources for order and line level attribute defaulting
iii) Can establish order or line level processing constraints
iv) Can default from the Customer, Ship To, Bill To, or Deliver-To site at the order header, and line transaction types can default from the order transaction type.
v) Enable you to group orders and lines
vi) Can specific processing controls for an order or line based upon the transaction type entered. For example, the Scheduling level controls the way scheduling works at the time of order entry for lines.
22.what r the different types of orders in OM.
23. where does u find the order status column, in which table. If the order which comes in from legacy fails due to some validation, where does the order goes from here. what is the next step to deal with that order.
In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.
24.the order which comes from legacy while it may be a CRM (seibel).it is a service order it must not go to the shipping module ,it must go to AR directly how it will go. can u tell that
BASICALLY WHAT EVER ORDER COMES FROM LEGANCY IT COMES TO OM MODULE THEN THE STATUS IS CHANGED AND IT MAY GO TO INVENTORY OR SHIPPING THEN IT GOES TO AR, but for the service order it has to go to AR directly. How is it possible?
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=118171.1
25. what r the parameter passed to run a report.
P_CONC_REQUEST_ID
26. what is the schema attached to OA.
APPS schema.
27. when we run the order import program it validates and the errors occurred can be seen in ?
Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections
28.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?
If we are using Multi-Org tables, we must define a user parameter, P_CONC_REQUEST_ID with Number(15).
29.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 definr=ed template.
We can define user-defined templates. Templates are like format that applied on the data. I didn't develop new template in the Reports.
30. How many libraries we can attach to a form
As such there is no limit
31. I moved this field into that repeating frame, but I’m still getting a ”frequency below it’s group” error.
http://www.dbasupport.com/oracle/faq/Detailed/351.shtml
Moving fields around does not change what enclosing object is considered it's parent group. Oracle carefully remembers what repeating frame a field was originally placed in and assigns that as it's parent. If you then reference a column further down the line of the query structure it will return that error. If you are not exactly sure which repeating frame a field belongs to, try dragging it out of all of them. Whichever frame will not allow it to escape is it's parent. To change a field's parent, first click on the lock button on the speedbutton bar. It should now look like an unlocked padlock. Now all of the fields on the layout can be repositioned regardless of their original parent items. When you are satisfied with the repositioning click the lock button again to lock the layout. Oracle will parse the layout and assumes that any item fully enclosed in a repeating frame is a child object of that frame.
Sometimes, for unknown and mysterious reasons, this method does not work. The alternative in this case is to highlight the field (or fields), cut it (cntrl-x), and then paste it into the desired frame. The paste does not initially set it into the right frame, but if we drag and drop it there before clicking on any other objects, and then click on something else, Oracle will usually figure what our intent was and assign the object(s) as a child of that frame.
One note though, if we are reassigning a group of fields, make sure the frame we are going to move them into is large enough to accept the whole group at once before we do the cut/paste.
If this technique also fails, we are probably going to have to delete and then recreate the objects within the desired frame. If the object has triggers attached, save yourself some typing by creating the new object in the right frame, copying over the trigger code, and then deleting the old object.
32. I switched the page size to 11x 8.5,but the printer still prints in Portriat.
http://www.orafaq.org/faqrep.htm
Even though we set the page size in the report properties, there is a another variable in the system parameters section under the data model in the object navigator called orientation. This sets the printer orientation. Oracle starts by setting it to "default" which means that no matter how we set the page size, the user's default printer setup will be used. We can also set it to either "Landscape" or "Portrait" to force the printer orientation no matter what the user has set as default.
33. I must put a repeating frame around these fields. How do I do this easily?.
Oracle looks at the layout, as a sort of layered inheritance model such that anything created on top of and completely inside another object is by definition a child of that object. Creation order is therefore critical to the layout process.
First, you can place the new repeating frame in the correct place and then use the techniques shown above in the "I moved this field but am still getting a frequency error" to reassign the fields into the new frame.
A second choice, Go ahead and draw the new frame around the fields we want to have placed in it. Now if we try to click on one of the fields you will not be able to as they are fully covered by the new frame. Now go to the "Arrange" menu, use the "send backwards" option to move the frame backwards until all of the fields have popped to the front and are now enclosed in it. Oracle reassigns the new repeating frame as each object's parent as they pop to the front.
34. How do I change the printed value of a field at runtime. What is call form stack?
i) Use Format Trigger for that field. 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.
Format triggers do not affect the data retrieved by the report. i.e., the data for the field is retrieved even though the field does not appear in the output.
ii) When successive forms are loaded via the CALL_FORM procedure, the resulting module hierarchy is known as the call form stack.
The call form stack problem, typically happens when we mix OPEN_FORM and CALL_FORMs together. If A issues an OPEN_FORM to start B, and then we issue a CALL_FORM in A to start C, we cannot issue a CALL_FORM from B to start D because we can have only one call form stack open at one time.
35.what is the difference between the snapshot and synonym?
A synonym is another name assigned to a table for easy identification. For example, if we refer to tables residing in other accounts or databases, we may find it convenient to define a synonym for the long string of identifiers. If a table name changes, we can simply redefine the synonym rather than rewrite all related queries. This is particularly useful if we regularly refer to tables with long or oblique names. For example, we could create a synonym 'ORG' for the unwieldy FUNCTIONAL_ORGANIZATION_TABLE, or create an easier-to-remember synonym 'PRODUCT' for the LEDGER_PRODUCT_CHARTFIELD table.
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
36. what is the difference between anonymous block and a procedure.Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema. It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.
A Stored Procedure is a named program running in the database that can take complex actions based on the inputs send to it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL.
The Stored Procedure needs to be validated, and if necessary to be recompile.Put it in a package and try pinning the package with dbms_shared_pool.keep('')
37. What are user exits and why do u use them what is their use?
To access profile values, multiple organizations, or Oracle Applications user exits, and for program to be used with concurrent processing at all, we must have the first and last user exits called by your Oracle Reports program be FND SRWINIT and FND SRWEXIT.
38. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are
i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure
ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status
39. Maximum how many libraries can u attach to a form.
This information is older one. It’s included here for conceptual understanding.
The libraries are attached to modules at design time, and although the actual program units are not loaded into memory until they are needed, the .lib file is attached and a file handle is held for future use. Under Windows there is currently a limit of 20 file handles per application, this limit is unchanged by the use of the FILES DOS variable. When using Oracle Forms for example, before the application is executed, Forms has taken up to 10 file handles already for itself. This clearly depletes the number left for the application.
The only modules this limit effects are the libraries, other CDE (Cooperative Development Environment) modules (fmx, mmx files etc.) when executed, are loaded into memory and then closed at the file level. This implies the need for 2 (to be safe) or more 'floating' file handles needed to perform this task.
This now leaves the application a maximum of 8 spare handles, which in turn only allows the application to attach up to 8 libraries. As this is a limit under Windows, there is little the application developer can do to work around this.
If this limit becomes an issue the only solution is to use the referencing facilities within the CDE products.
40. The vendor information in the purchasing module is distributed in 3 to 4 tables which column will keep the tables integrated to get the data I mean by which column we can track the data in different tables.
PO_VENDORS and PO_VENDOR_SITES_ALL are the primary vendor tables. They are connected by a column namely “VENDOR_ID”.
41. When a form call a pl/sql routine if there is an error in the pl/sql routine how do u place custom message in the form, which API will u use.
FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string('Message Text');
fnd_message.show;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.
42. We can import items from legacy system using item import interface using the SRS. if I want to import using the UNIX Commands or pl/sql with out using SRS how do I do it.
From the operating system, use CONCSUB to submit a concurrent program. It's an easiest way to test a concurrent program.
Normally, CONCSUB submits a concurrent request and returns control to the OS prompt/shell script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to make CONCSUB wait until the request has completed before returning control to the OS prompt/shell script
By using the WAIT token, the utility checks the request status every 60 seconds and returns to the operating system prompt upon completion of the request. concurrent manager does not abort, shut down, or start up until the concurrent request completes. If your concurrent program is compatible with itself, we can check it for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
Syntax: CONCSUB [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
To pass null parameters to CONCSUB, use '""' without spaces for each null parameter.
In words: single quote double quote double quote single quote
Following is an example of CONCSUB syntax with null parameters:
CONCSUB oe/oe OE 'Order Entry Super User' JWALSH CONCURRENT XOE XOEPACK 4 3 '""' 3
To Invoke a Concurrent Program using PL/SQL:
i) Just insert a row in FND_CONCURRENT_REQUESTS with the apropriate parameters and commit.
ii) Invoke the SUBMIT_REQUEST procedure in FND_REQUEST package.
FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '', FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP', '01-JAN-00', chr(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
43. What is an anchor?
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.
Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another. An anchor defines the relative position of an object to the object to which it is anchored. Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor. It should also be noted that the position of the object in the Layout editor effects the final position in the report output. Any physical offset in the layout is incorporated into the percentage position specified in the Anchor property sheet.
44. How many types of repeating frames are there.
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
Use repeating frames to define record-level layout information. For example, you can specify the direction in which the records print (e.g., Down, Across, Across/Down, etc.) and the spacing between each record., they provide a subset of repeating frame functionality (e.g., they do not have a Maximum Records per Page property).
There are two types of repeating frames:
Default
user-created
Default Repeating Frames When you accept the Default Layout dialog box, Oracle Reports generates one repeating frame for each group in the data model, and places one field inside it for each of the group's columns. Repeating frames can enclose any layout object, including other repeating frames. Nested repeating frames are typically used to produce master/detail and break reports. For each record of the outer repeating frame, Oracle Reports will format all related records of the enclosed repeating frame.
User-created Repeating Frames Create a repeating frame in the Layout editor by clicking on the Repeating Frame tool, dragging a region, then specifying its properties in its property sheet.
45. What are the difficulties u faced when customizing a form.
There is no built-in functionality to validate duplicate record entry in a multi-record block. We need to build a logic to handle this validation.
47. What is the use of item category column in the items interface table.
You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs.
Multiple Organizations is enabled in Oracle
Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by '_ALL'.
48. What are the difficulties u faced when customizing a form.
There is no built-in functionality to validate duplicate record entry in a multi-record block. We need to build a logic to handle this validation.
Assume that the two databases be DB1 and DB2. At one time I could connect to only one database say DB1. Now I should able to access the tables in DB2 from DB1. First I create a DBlink in DB1 that access the tables in DB2. Using the DBlink, create snapshots for each of the tables in DB2. Now we can use these Snapshots in query, as if like tables in DB1.
The purpose for creating snapshot is both security and to reduce the network load, for each access of the tables in DB2.
2.what is the difference between _all and with underscore all tables.
Tables that end with all are belong to multi-org. They have a field called ORG_ID.
3.Differences between OE and OM.
None of the new features in R11i Order Management or Shipping Execution have been backported to R11 Order Entry or earlier. The data model changes make the effort impractical.
The database appears to support the older SO_% tables and the newer OE_% tables for Order Management.
The concurrent process, order import, pick up the data from SO_HEADERS_INTERFACE_ALL and populate both the older SO_HEADERS_ALL and the newer OE_ORDER_HEADERS_ALL table. The SO_HEADERS_ALL table is present for those customers who are upgrading from 10.7 to 11i. The tables are there as part of the installation to facilitate the upgrade process.
The 'OE:' profile options are still accessible even though these profile options are not used in 11i. These profile options have been replaced by the 'OM:' profile options.
4. we have a system with 10.7 apps,in that we have 2.5 reports and 4.5 forms. Now there is upgrade from 10.7 to 11i.in that case the reports and forms must be converted into 6i. What r the steps we perform.list them.There are a minor changes in the PL/SQL Script in Forms 4.5 and Reports 2.5 with that of Forms6i and Reports6i. The differences are…
CHAR replaced by VARCHAR2
String length required for VARCHAR2
OUT and IN OUT default expressions removed
NULL added to RETURN in functions
Obsolete built-in
LENGTH function returns NULL for zero-length string
Passing NULL to overloaded subprogram does not resolve properly
Missing RETURN statement in function
5. The differences between reports 2.5 and 6i
ie 4t question answer
6. The differences between forms 4.5 and forms 6i.
ie 4t question answer
7. How is the stored procedures in the report are called. what is the use of that.
There is no sperate method for calling a database stored procedure in Oracle Reports. When a procedure is called form Oracle Report and a procedure with that name is available at several places namely Program Units, Attached Library and Database. The order will be..
1. Program Units
2. Attached Library
3. Database.
8.what is mutating table and mutating error .Through any example explain how we any over come that error.
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress.
Tables are never considered mutating for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. Views are not considered mutating in INSTEAD OF triggers.
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.
9.what r pl/sql table and explain were in ur project u have used that tables.what r the pl/sql records,and advantages of pl/sql tables and records.
A PL/SQL table can store rows (not just a column) of Oracle data. PL/SQL tables of records make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. We can even use PL/SQL tables of records to simulate local database tables. Attributes are characteristics of an object. Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.
PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
"global temporary tables" are not in-memory structures. But they do offer greatly-reduced disk I/O compared to standard tables (and therefore usually a significant performance improvement) and they also offer the ease-of-use of standard tables, since standard SQL can be used with them, no special array-processing syntax is required.
For processes where every bit of performance improvement is critical, PL\SQL tables are the best way to go.
10. what is data mapping and what type of mapping u have done in ur carrer or in the recent projects.
In this task, we map the data elements from the legacy systems to the target Oracle Applications.
11.What is Data cleaning and testing.
Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.
Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables.
Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit.
12.what r the differences between oracle data base 7 and 8.
Oracle 7 is a simple RDBMS, where as Oracle 8 is ORDBMS i.e., RDBMS with Object Support. The main add-ons in version 8 are…
Abstract Datatypes
Varrays
PL/SQL Tables
Nested Tables
Partitioned Tables
etc.,
13.what r the lexical parameters and were did u used this in ur project .
We define lexical parameters in Oracle Reports. Lexical parameters can dynamically replace clauses in the Select statement in the data model and even the whole select statement.
A lexical reference replaces any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&). If the parameter object does not exist, Report Builder does not create. We must always create the parameter for a lexical reference in the Object Navigator.
14.while registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can u list them?
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
15.when “no data found” exception occurs, give any example.A SELECT statement in a PL/SQL block should return one and only one row. If it fails to return a row, then we get this exception.
16.How u can delete the duplicate records from the table.
delete from a
where a.rowid != (select max(b.rowid)
from b
where a. = b. and
a. = b. ...
group by a., a....);
------------------------------------------------------------
Method 1: SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Method 2: SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Method 3: (thanks to Kenneth R Vanluvanee)
SQL> Delete from my_table where rowid not in(
SQL> select max(rowid) from my_table
SQL> group by my_column_name );
Method 4: (thanks to Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
17. In matrix reports minimum how many groups are required.
A matrix (crosstab) 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.
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.
18.How we can call from form to form, form to report.
Form to Form:
i) call_form('your form name', hide, do_replace);
ii) open_form('your form name');
The first version can be used to hide the calling form until the called form has been exited The second version leaves both forms open at the same time.
Form to Report:
Using the RUN_PRODUCT built-in procedure.
Eg: Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
Where pl_id is the parameter list through which we pass parameter values to the report.
19.why is ref cursor is used in the reports.
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor. We base a query on a ref cursor when you want to:
i) More easily administer SQL
ii) Avoid the use of lexical parameters in your reports
iii) Share datasources with other applications, such as Form Builder
iv) Increase control and security
v) Encapsulate logic within a subprogram
Furthermore, if we use a stored program unit to implement ref cursors, we receive the added benefits that go along with storing your program units in the Oracle database.
21.what r the transaction types in OM.With the release of Oracle Order Management 11i, Order Cycles have been replaced by Oracle Workflow definitions, and Order Types have been replaced by Order Management Transaction Types. Order Management provides seeded Workflow process definitions for both orders and lines, and Order Management also enables you to define both order header and Order Line transaction types.
Note: Order Management provides NO seeded transaction types. For existing Oracle Order Entry customers, Order Management will update existing Order Types to order and line transaction types during the upgrade process.
Order Management Transaction types:
i) Determine the workflow processes executed for both the order and line
ii) Can act as sources for order and line level attribute defaulting
iii) Can establish order or line level processing constraints
iv) Can default from the Customer, Ship To, Bill To, or Deliver-To site at the order header, and line transaction types can default from the order transaction type.
v) Enable you to group orders and lines
vi) Can specific processing controls for an order or line based upon the transaction type entered. For example, the Scheduling level controls the way scheduling works at the time of order entry for lines.
22.what r the different types of orders in OM.
23. where does u find the order status column, in which table. If the order which comes in from legacy fails due to some validation, where does the order goes from here. what is the next step to deal with that order.
In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.
24.the order which comes from legacy while it may be a CRM (seibel).it is a service order it must not go to the shipping module ,it must go to AR directly how it will go. can u tell that
BASICALLY WHAT EVER ORDER COMES FROM LEGANCY IT COMES TO OM MODULE THEN THE STATUS IS CHANGED AND IT MAY GO TO INVENTORY OR SHIPPING THEN IT GOES TO AR, but for the service order it has to go to AR directly. How is it possible?
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=118171.1
25. what r the parameter passed to run a report.
P_CONC_REQUEST_ID
26. what is the schema attached to OA.
APPS schema.
27. when we run the order import program it validates and the errors occurred can be seen in ?
Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections
28.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?
If we are using Multi-Org tables, we must define a user parameter, P_CONC_REQUEST_ID with Number(15).
29.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 definr=ed template.
We can define user-defined templates. Templates are like format that applied on the data. I didn't develop new template in the Reports.
30. How many libraries we can attach to a form
As such there is no limit
31. I moved this field into that repeating frame, but I’m still getting a ”frequency below it’s group” error.
http://www.dbasupport.com/oracle/faq/Detailed/351.shtml
Moving fields around does not change what enclosing object is considered it's parent group. Oracle carefully remembers what repeating frame a field was originally placed in and assigns that as it's parent. If you then reference a column further down the line of the query structure it will return that error. If you are not exactly sure which repeating frame a field belongs to, try dragging it out of all of them. Whichever frame will not allow it to escape is it's parent. To change a field's parent, first click on the lock button on the speedbutton bar. It should now look like an unlocked padlock. Now all of the fields on the layout can be repositioned regardless of their original parent items. When you are satisfied with the repositioning click the lock button again to lock the layout. Oracle will parse the layout and assumes that any item fully enclosed in a repeating frame is a child object of that frame.
Sometimes, for unknown and mysterious reasons, this method does not work. The alternative in this case is to highlight the field (or fields), cut it (cntrl-x), and then paste it into the desired frame. The paste does not initially set it into the right frame, but if we drag and drop it there before clicking on any other objects, and then click on something else, Oracle will usually figure what our intent was and assign the object(s) as a child of that frame.
One note though, if we are reassigning a group of fields, make sure the frame we are going to move them into is large enough to accept the whole group at once before we do the cut/paste.
If this technique also fails, we are probably going to have to delete and then recreate the objects within the desired frame. If the object has triggers attached, save yourself some typing by creating the new object in the right frame, copying over the trigger code, and then deleting the old object.
32. I switched the page size to 11x 8.5,but the printer still prints in Portriat.
http://www.orafaq.org/faqrep.htm
Even though we set the page size in the report properties, there is a another variable in the system parameters section under the data model in the object navigator called orientation. This sets the printer orientation. Oracle starts by setting it to "default" which means that no matter how we set the page size, the user's default printer setup will be used. We can also set it to either "Landscape" or "Portrait" to force the printer orientation no matter what the user has set as default.
33. I must put a repeating frame around these fields. How do I do this easily?.
Oracle looks at the layout, as a sort of layered inheritance model such that anything created on top of and completely inside another object is by definition a child of that object. Creation order is therefore critical to the layout process.
First, you can place the new repeating frame in the correct place and then use the techniques shown above in the "I moved this field but am still getting a frequency error" to reassign the fields into the new frame.
A second choice, Go ahead and draw the new frame around the fields we want to have placed in it. Now if we try to click on one of the fields you will not be able to as they are fully covered by the new frame. Now go to the "Arrange" menu, use the "send backwards" option to move the frame backwards until all of the fields have popped to the front and are now enclosed in it. Oracle reassigns the new repeating frame as each object's parent as they pop to the front.
34. How do I change the printed value of a field at runtime. What is call form stack?
i) Use Format Trigger for that field. 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.
Format triggers do not affect the data retrieved by the report. i.e., the data for the field is retrieved even though the field does not appear in the output.
ii) When successive forms are loaded via the CALL_FORM procedure, the resulting module hierarchy is known as the call form stack.
The call form stack problem, typically happens when we mix OPEN_FORM and CALL_FORMs together. If A issues an OPEN_FORM to start B, and then we issue a CALL_FORM in A to start C, we cannot issue a CALL_FORM from B to start D because we can have only one call form stack open at one time.
35.what is the difference between the snapshot and synonym?
A synonym is another name assigned to a table for easy identification. For example, if we refer to tables residing in other accounts or databases, we may find it convenient to define a synonym for the long string of identifiers. If a table name changes, we can simply redefine the synonym rather than rewrite all related queries. This is particularly useful if we regularly refer to tables with long or oblique names. For example, we could create a synonym 'ORG' for the unwieldy FUNCTIONAL_ORGANIZATION_TABLE, or create an easier-to-remember synonym 'PRODUCT' for the LEDGER_PRODUCT_CHARTFIELD table.
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
36. what is the difference between anonymous block and a procedure.Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema. It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.
A Stored Procedure is a named program running in the database that can take complex actions based on the inputs send to it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL.
The Stored Procedure needs to be validated, and if necessary to be recompile.Put it in a package and try pinning the package with dbms_shared_pool.keep('')
37. What are user exits and why do u use them what is their use?
To access profile values, multiple organizations, or Oracle Applications user exits, and for program to be used with concurrent processing at all, we must have the first and last user exits called by your Oracle Reports program be FND SRWINIT and FND SRWEXIT.
38. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are
i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure
ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status
39. Maximum how many libraries can u attach to a form.
This information is older one. It’s included here for conceptual understanding.
The libraries are attached to modules at design time, and although the actual program units are not loaded into memory until they are needed, the .lib file is attached and a file handle is held for future use. Under Windows there is currently a limit of 20 file handles per application, this limit is unchanged by the use of the FILES DOS variable. When using Oracle Forms for example, before the application is executed, Forms has taken up to 10 file handles already for itself. This clearly depletes the number left for the application.
The only modules this limit effects are the libraries, other CDE (Cooperative Development Environment) modules (fmx, mmx files etc.) when executed, are loaded into memory and then closed at the file level. This implies the need for 2 (to be safe) or more 'floating' file handles needed to perform this task.
This now leaves the application a maximum of 8 spare handles, which in turn only allows the application to attach up to 8 libraries. As this is a limit under Windows, there is little the application developer can do to work around this.
If this limit becomes an issue the only solution is to use the referencing facilities within the CDE products.
40. The vendor information in the purchasing module is distributed in 3 to 4 tables which column will keep the tables integrated to get the data I mean by which column we can track the data in different tables.
PO_VENDORS and PO_VENDOR_SITES_ALL are the primary vendor tables. They are connected by a column namely “VENDOR_ID”.
41. When a form call a pl/sql routine if there is an error in the pl/sql routine how do u place custom message in the form, which API will u use.
FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string('Message Text');
fnd_message.show;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.
42. We can import items from legacy system using item import interface using the SRS. if I want to import using the UNIX Commands or pl/sql with out using SRS how do I do it.
From the operating system, use CONCSUB to submit a concurrent program. It's an easiest way to test a concurrent program.
Normally, CONCSUB submits a concurrent request and returns control to the OS prompt/shell script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to make CONCSUB wait until the request has completed before returning control to the OS prompt/shell script
By using the WAIT token, the utility checks the request status every 60 seconds and returns to the operating system prompt upon completion of the request. concurrent manager does not abort, shut down, or start up until the concurrent request completes. If your concurrent program is compatible with itself, we can check it for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
Syntax: CONCSUB [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
To pass null parameters to CONCSUB, use '""' without spaces for each null parameter.
In words: single quote double quote double quote single quote
Following is an example of CONCSUB syntax with null parameters:
CONCSUB oe/oe OE 'Order Entry Super User' JWALSH CONCURRENT XOE XOEPACK 4 3 '""' 3
To Invoke a Concurrent Program using PL/SQL:
i) Just insert a row in FND_CONCURRENT_REQUESTS with the apropriate parameters and commit.
ii) Invoke the SUBMIT_REQUEST procedure in FND_REQUEST package.
FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '', FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP', '01-JAN-00', chr(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
43. What is an anchor?
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.
Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another. An anchor defines the relative position of an object to the object to which it is anchored. Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor. It should also be noted that the position of the object in the Layout editor effects the final position in the report output. Any physical offset in the layout is incorporated into the percentage position specified in the Anchor property sheet.
44. How many types of repeating frames are there.
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
Use repeating frames to define record-level layout information. For example, you can specify the direction in which the records print (e.g., Down, Across, Across/Down, etc.) and the spacing between each record., they provide a subset of repeating frame functionality (e.g., they do not have a Maximum Records per Page property).
There are two types of repeating frames:
Default
user-created
Default Repeating Frames When you accept the Default Layout dialog box, Oracle Reports generates one repeating frame for each group in the data model, and places one field inside it for each of the group's columns. Repeating frames can enclose any layout object, including other repeating frames. Nested repeating frames are typically used to produce master/detail and break reports. For each record of the outer repeating frame, Oracle Reports will format all related records of the enclosed repeating frame.
User-created Repeating Frames Create a repeating frame in the Layout editor by clicking on the Repeating Frame tool, dragging a region, then specifying its properties in its property sheet.
45. What are the difficulties u faced when customizing a form.
There is no built-in functionality to validate duplicate record entry in a multi-record block. We need to build a logic to handle this validation.
47. What is the use of item category column in the items interface table.
You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs.
Multiple Organizations is enabled in Oracle
Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by '_ALL'.
48. What are the difficulties u faced when customizing a form.
There is no built-in functionality to validate duplicate record entry in a multi-record block. We need to build a logic to handle this validation.
=================
Report Questions
Report Questions
1. What is SRW Package?
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., " $?).
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
Screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").
5. How many Types of Reports available in Reports
Tabular form-like form – letter Group left
Group above matrix Matrix with group Mailing labelMatrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1. Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2. Repeating frame for columns (Across)
3. Matrix object the intersection of the two repeating frames
6. What Types of Triggers are Available in Reports.
Report level Triggers
Data Model Triggers
Layout Model TriggersReport Level Triggers
Before parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the reportData Model Triggers
Formula Column, Group Filter, Parameter valuesLayout Model Triggers
7. What are Format triggers?
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max (Sal) for particular department.
ii) To format the Sal column with a Dollar ($) prefix.
iii) To format Date formats….etc
8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
9. What is Layout model?
Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.
10 What is Live Previewer?
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name, mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.
12. What is Query?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group? The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.
14 What is Repeating Frame?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref cursor when you want to:
n more easily administer SQL
n avoid the use of lexical parameters in your reports
n share data sources with other applications, such as Form Builder
n increase control and security
n encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.
16. What is Template?
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders " them.
Off: parent borders remain fixed when child objects are moved against them.
18. What is Page Break?
Ans: To limit the records per page.
19 What is Page Protector?
Ans: The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information prints another page.Print Condition
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.
21 What is Vertical and Horizental ElacityAns: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:
22.What is Place holder Columns?
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: 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.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: 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.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.
29 What is Break Column?
Ans: We can break a column through data model , it Display once for a group
30. How do u call Report from form?
Ans: We can break a column through data model , it Display once for a group
30. How do u call Report from form?
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
31. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS
32 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.
In Data model Layout , Group Property Through Filter Type & No of records to display
Property, Values are First, last, pl/sql
33. What is Header ,Body, Trailer, and Footer in Reports
Header: The header consists of one or more pages that are printed before report proper. The type of
Information you might want to print title of the page, company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.
Body: The body is where all the main report objects are placed
Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.
34. what are Executable file definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports Runtime (RWRUN60.EXE)
n Reports Convert (RWCON60.EXE)
n Reports Background Engine (RWRBE60.EXE)
n Reports Server (RWMTS60.EXE)
n Reports Web Cartridge (RWOWS60.DLL)
n Reports CGI (RWCGI60.EXE)
n Reports Queue Manager (RWRQM60.EXE)
n Reports Launcher (RWSXC60.EXE)
n Reports ActiveX Control (RWSXA60.OCX)
35 what are the Non_query fields?
Aggregated Information, Calculated information, A string Function
Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
36 How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value.
That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.
The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .
There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields. While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals
Report Bursting
The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.
Additional Layout:
Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.
System Variables as Source Field In Layout Editor
Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total Physical Pages.
Link File: Is a special type of boilerplate, that doesn’t have to remain constant for each report run
The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL
Source filename: the name of the file the u want link to the report through import Image from
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., " $?).
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
Screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").
5. How many Types of Reports available in Reports
Tabular form-like form – letter Group left
Group above matrix Matrix with group Mailing labelMatrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1. Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2. Repeating frame for columns (Across)
3. Matrix object the intersection of the two repeating frames
6. What Types of Triggers are Available in Reports.
Report level Triggers
Data Model Triggers
Layout Model TriggersReport Level Triggers
Before parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the reportData Model Triggers
Formula Column, Group Filter, Parameter valuesLayout Model Triggers
7. What are Format triggers?
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max (Sal) for particular department.
ii) To format the Sal column with a Dollar ($) prefix.
iii) To format Date formats….etc
8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
9. What is Layout model?
Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.
10 What is Live Previewer?
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name, mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.
12. What is Query?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group? The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.
14 What is Repeating Frame?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref cursor when you want to:
n more easily administer SQL
n avoid the use of lexical parameters in your reports
n share data sources with other applications, such as Form Builder
n increase control and security
n encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.
16. What is Template?
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders " them.
Off: parent borders remain fixed when child objects are moved against them.
18. What is Page Break?
Ans: To limit the records per page.
19 What is Page Protector?
Ans: The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information prints another page.Print Condition
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.
21 What is Vertical and Horizental ElacityAns: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:
22.What is Place holder Columns?
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: 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.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: 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.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.
29 What is Break Column?
Ans: We can break a column through data model , it Display once for a group
30. How do u call Report from form?
Ans: We can break a column through data model , it Display once for a group
30. How do u call Report from form?
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
31. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS
32 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.
In Data model Layout , Group Property Through Filter Type & No of records to display
Property, Values are First, last, pl/sql
33. What is Header ,Body, Trailer, and Footer in Reports
Header: The header consists of one or more pages that are printed before report proper. The type of
Information you might want to print title of the page, company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.
Body: The body is where all the main report objects are placed
Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.
34. what are Executable file definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports Runtime (RWRUN60.EXE)
n Reports Convert (RWCON60.EXE)
n Reports Background Engine (RWRBE60.EXE)
n Reports Server (RWMTS60.EXE)
n Reports Web Cartridge (RWOWS60.DLL)
n Reports CGI (RWCGI60.EXE)
n Reports Queue Manager (RWRQM60.EXE)
n Reports Launcher (RWSXC60.EXE)
n Reports ActiveX Control (RWSXA60.OCX)
35 what are the Non_query fields?
Aggregated Information, Calculated information, A string Function
Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
36 How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value.
That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.
The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .
There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields. While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals
Report Bursting
The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.
Additional Layout:
Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.
System Variables as Source Field In Layout Editor
Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total Physical Pages.
Link File: Is a special type of boilerplate, that doesn’t have to remain constant for each report run
The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL
Source filename: the name of the file the u want link to the report through import Image from
==================
REPORTS FAQ'S
REPORTS
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 after trigger
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 ParameterForm).
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 argumentor 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.) COMMITscan occur during this time due to any of the following--user exit with DDL, SRW.DO_SQLwith 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 theONSUCCESS argument or setting.
Cautions=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which thereport is based. Step 3 takes a snapshot of the tables and the snapshot must remain validthroughout the execution of the report. In steps 7 through 9, avoid DML statements thatwould modify the contents of the tables on which the report is based. Queries may beexecuted in any order, which makes DML statements unreliable (unless performed on tablesnot used by the report).2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDLstatement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If youare using READONLY, this will prematurely end the transaction begun by SETTRANSACTION READONLY.
Report trigger restrictions=============================
1. If you are sending your report output to the Runtime Previewer or Live Previewer, youshould note that some or all of the report triggers may be fired before you see the reportoutput. For example, suppose that you use SRW.MESSAGE to issue a message in theBetween 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 toformat ahead to compute the forward references. Hence, even though you have not yet seena 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. Forexample, 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 setthe 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 alsothat the use of PL/SQL global variables to indirectly set the values of columns or parametersis 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 orSRW.RUN_REPORT), you should commit database changes you make in the BeforeParameter 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 AfterParameter 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_clauseIf the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, youmust specify the value in the After Parameter Form trigger or earlier. You would get anerror if you supplied the following value for the parameter in the Before Report trigger. Ifyou 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?
Custom Reports and Standard reports
17. Name Custom Reports and…-------------------------------------
Tell some of reports that you did
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
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. 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
28. 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.
29. 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.
30. Why is ref cursor is used in the reports?
Dynamic refcursor
31. 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.
32. 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?
Assume that the two databases be DB1 and DB2. At one time I could connect to only one database say DB1. Now I should able to access the tables in DB2 from DB1. First I create a DBlink in DB1 that access the tables in DB2. Using the DBlink, create snapshots for each of the tables in DB2. Now we can use these Snapshots in query, as if like tables in DB1.
The purpose for creating snapshot is both security and to reduce the network load, for each access of the tables in DB2.
No comments:
Post a Comment