Frequently Asked Oracle Pl/SQL questions
Differentiate between TRUNCATE and DELETE ?
Can you use a commit statement within a database trigger?
Difference between procedure and function.?
What WHERE CURRENT OF clause does in a cursor?
What is a cursor for loop?
What are cursor attributes?
Difference between an implicit & an explicit cursor?
What is a cursor?
What is Bulk Collect
What is for all update clause
what is mutating trigger
What are the types of oracle triggers , define them ( row level & statement level trigger)
what are Autonomous transaction
What is Row id , and it's structure (built formation)
What are indexes & Types of indexes
What are exceptions
How to define user defined exceptions
What is a temporary table
Steps taken to improve performance of the queries
Tools used in development &and process.
Oracle Apps Interview questions
1) What is SET-OF-BOOKS
Collection of Chat of Accounts and Currency and Calendars is called SOB
2) How can u call a standard interface program from sql or pl/sql code
FND_REQUEST.SUBMIT_REQUEST (........)
3) Calling a Concurrent Program from UNIX
FND_CONCSUB(can submit conc program in host environment)
3) API's FOR CUSTOMER INTERFACE
HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C
HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C
4) PL/SQL stored procedure parameters or what are the two parameters that are mandatory for pl/sql type concurrent program?
(ERRBUF OUT,RETCODE OUT)
ERRBUF :- Used to write the error message to log or request file.
RETCODE :- Populate log request file with program submission details info.
5) What is Value Set?
The value set is a collection (or) container of values.
When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
If the list of values needed to be dynamic and ever changing and define a table based values set.
6) What r the validation types?
1.Independent ------input must exist on previously defined list of values
2.Dependent ------input is checked against a subset of values based on a prior value.
3.Table ----- input is checked against values in an application table
4.Special ------values set uses a flex field itself.
5.Pair ------ two flex fields together specify a range of valid values.
6.Translatable independent ----- input must exist on previously defined list values; translated values can be used.
7.Translatable dependent ------- input is checked against a subset of values based on a prior values; translated value can be used.
8.None -------- validation is minimal.
7) What is template?
The TEMPLATE form is the required starting point for all development of new Forms.
8) What are ad-hoc reports?
Ans.: Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a
particular purpose. For example, ad hoc tax codes or an ad hoc database query
9) What is responsibility?
Is collection of menus, request security groups and data groups
Menus: collection of forms is nothing but menus
Request security groups: collection of programs.
Data groups: is a group of modules to be made accessible by the user through Responsibility
System admin
10) What are different execution methods of executabls?
Host The execution file is a host script.
Oracle Reports The execution file is an Oracle Reports file.
PL/SQL Stored Procedure The execution file is a stored procedure.
SQL*Loader The execution file is a SQL script.
SQL*Plus The execution file is a SQL*Plus script.
SQL*Report The execution file is a SQL*Report script.
Spawned The execution file is a C or Pro*C program.
Immediate The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.
11) Composite Datatypes
PL/SQL TABLES / PL/SQL RECORDS / Nested TABLE / VARRAY
12) What is the sequence of functions – group by,having,orderby in a select statements ?
Select…..Group by…Having…Orderby..
13) Difference between User and Super User?
User : login user or front end user
Super user : it has full access of particular module
14) What is multi org?
“Legal entity has more than one operating unit is called as multi org”
a) Business group --- Human resources information is secured by Business group
b) Legal entity --- inter-company and fiscal/tax reporting.operating unit.
c) Operating unit --- secures AR, OE, AP, PA and PO Information.
d) Organizations --- is a specialize unit of work at particular locations
15) What is ERP Architecture of apps?
A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.
17) Data Link
- Data links relate the results of multiple queries.
- A data link (Parent - Child Relation Ship) causes the child query to be executed once for each instance of its parent group.
18)In which tables FF are stored?
A) FND_ID_FLEXS
B) FND_ID_FLEX_STRUCTURES
19) Oracle Applications Architecture
Internet computing Architecture is a frame work for 3-tired, distributed computing that supports Oracle Applications products.
The Three tiers are
1. Data Base Tier
2. Application Tier
3. Desk Top Tier
Database tier manages Oracle database.
Application tier manages Oracle Applications and other tools.
Desktop tier provides the user interface displace.
With internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard internet brows
20) List of some API'S
FND_PROGRAM.EXECUTABLE
FND_PROGRAM.REGISTER
FND_PROGRAM.PARAMETER
FND_PROGRAM.ADD_TO_GROUP
FND_REQUEST.SUBMIT_REQUEST
FND_PROFILE.VALUE
FND_PROFILE.GET
FND_PROGRAM.EXECUTABLE
FND_PROGRAM.REGISTER
21) How to get second parameter value based on first parameter?
$flex$.value_setname
22) How to register a table and columns through back end?
by using AD_DD package
23) How to write to a file through concurrent program.
By using FND_FILE package and it can be used only for log and output files.
1.FND_FILE.PUT
this is used to write text to a file with out a new line character
Multilane calls to FND_FILE.PUT will produce consummated text.
Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2);
can be FND_FILE.LOG or FND_FILE.OUTPUT.
2.FND_FILE.PUT_LINE
this procedure as used to write a line of text to a file followed by a new line character.
Procedure FND_FILE.PUT_LINE (which IN number, buff IN varchar2);
EX:- FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get);
3.FND_FILE.NEW_LINE
this procedure is used to write line terminators to a file
procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1);
Ex:- to write two newline characters to a log file
Fnd_file.new_line (fnd_file.log,2);
4.FND_FILE.PUT_NAMES
this procedure as used to set the temporary log file and output filenames and the temporary directory to the user specified values.
This should be called before calling my other FND_FILE procedure and only once per a session.
24) How to checks the request status?
A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
FND_CONCURRENT.GET_REQUEST_STATUS
25) How do you find that multiorg is installed?
multi organization architecture is meant to allow multiple companies or subsidiaries to store their records with in a single data base.
Multiple organization Architecture allows this by partitioning data through views in APPS schema.
Implementation of Multi org generally includes more than one business group.
To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups
26) What is a Data Group?
A data group is a group of oracle applications and the Oracle ID?s of each application
Oracle ID grants access privileges to tables in an Oracle Database
Data group determines which Oracle Data base accounts a responsibilities forms, concurrent programs and reports connect to.
27) What is a Responsibility?
Responsibility defines Applications Privileges
A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
Each user has at list one or more responsibilities and several users can share the same responsibility 22
Each responsibility allows access to
a specific application or a set of applications.
A set of books
A restricted list of windows that an user can navigate
Reports in a specific application.
28) What are security Attributes?
Security Attributes are used by Oracle self service web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row.
29) What is a Profile Option?
profile options are the set of changeable options that affects how the application looks and behaves.
By setting profile options, the applications can be made to react in different ways for different users depending on the specific user attributes.
30) What is the relation between Responsibility, Menu and Request Group?
Responsibility: - A responsibility is a set of authority in Oracle Apps that lets users access only that functionality of the application appropriate to their roles.
Menu: - A menu is a hierarchical arrangement of functions and menus of functions that appears in the Navigator. Each responsibility has a menu assigned to it.
Request Group: - it is a collection of reports or concurrent programs. A system Administrator defines report groups in order to control user access to reports and concurrent programs. Only a system administrator can create a request group.
31) What is meant by APPL_TOP?
$APPL_TOP: An environment variable that denotes the installation directory for Oracle Application Object Library and your other Oracle applications. $APPL_TOP is usually one directory level above each of the product directories (which are often referred to as $PROD_TOP or $PRODUCT_TOP or $<prod>_TOP).
32) What are profile options; at what levels can these be set?
A user profile is a set of changeable options that affects the way the applications run. Oracle
Applications object Library establishes a value for each option in a user?s profile when the
User logs on or changes responsibility.
System Profile: - Profile option can be set for the user community.
User Profile: - Provide Oracle Apps with standard information which describes a user,
Application, Responsibility and site. At each profile level user profile options can be set.
33) What is Set of Books?
A financial reporting entity that uses a particular chart of accounts, functional currency,
And accounting calendar. Oracle General Ledger secures transaction information (such as journal entries and balances) by set of books. When you use Oracle General Ledger, you choose a responsibility that specifies a set of books. You then see information for that set of books only.
34)Where do concurrent request logfiles and output files go?
places log files in $APPLCSF/$APPLLOG
Output files go in $APPLCSF/$APPLOUT
35) Difference b/w ROWID and ROWNUM?
ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored.
ROWNUM: It gives a sequence number in which rows are retrieved from the database.
36) Give some examples of pseudo columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE
37) Difference b/w implicit cursor and explicit cursor?
Implicit cursors are automatically created by oracle for all its DML stmts.
Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN;
Explicit cursors are created by the users for multi row select stmts.
38) Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:
DECLARE
CURSOR wip_cur IS
SELECT acct_no, enter_date
FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE" in the cursor select stmt.
"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.
By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.
39) What is RAISE_APPLICATION_ERROR?
The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error
40) What is mutating error?
Mutating error occurs in the following scenario:
WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR.
41) Can we have commit/rollback in DB triggers?
Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.
42) Can we make the trigger an autonomous transaction?
This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.
43) What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
44) What is a REF Cursor?
The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.
45) What is the difference between BINARY_INTEGER and PLS_INTEGER?
binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.
Compared to integer and binary_integer, pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operates on library arithmetic.
pls_integer comes from oracle10g.
binary_integer allows indexing integer for assocative arrays prior to oracle9i.
The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.
Suppose that I’ve written a program that is supposed to insert 10,000 rows into a table. After inserting 9,000 of those rows, the 9,001st insert fails with a DUP_VAL_ON_INDEX error (a unique index violation). The SQL engine passes that error back to the PL/SQL engine, and if the FORALL statement is written like the one in Listing 4, PL/SQL will terminate the FORALL statement. The remaining 999 rows will not be inserted.
If you want the PL/SQL engine to execute as many of the DML statements as possible, even if errors are raised along the way, add the SAVE EXCEPTIONS clause to the FORALL header. Then, if the SQL engine raises an error, the PL/SQL engine will save that information in a pseudocollection named SQL%BULK_EXCEPTIONS, and continue executing statements. When all statements have been attempted, PL/SQL then raises the ORA-24381 error.
You can—and should—trap that error in the exception section and then iterate through the contents of SQL%BULK_EXCEPTIONS to find out which errors have occurred. You can then write error information to a log table and/or attempt recovery of the DML statement.
BEGIN
54) Collection Exceptions
55) Suppose where the Invoice given by supplier is in USD, but the payment can be done in GBP. Is it possible in AP?
Not possible
56) What is the difference between format trigger and action trigger?
format trigger : when we want hide or display the objects in in layout model dynamically we can use format trigger
action trigger : when a button selectd procedure executed new report open format trigger we can write in item level
57) What is global tempory table?
Global temporary tables are temporary table for perticular session. Global temporary tables are two types,
1.Transaction wise temporary table
In transaction wise temporary tables we will use on commit delete rows keyword, which delete the data whenever we do commit.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
2.Session wise temporary tables.
In session wise temporary table we will use on commit preserve rows keyword, which preserve the data till the end of that session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
58) What is Automation of Accounts Payable activities (Evaluated Receipt settlement)?
Payment on receipt enables u to automatically create std,unapproved invoices for payment of goods based on receipt transaction.Invoices r created using a combination of receipt & PO info,eliminating duplicate manual data entry and ensuringmaccurateand timely data processig. Paymrnt on receipt is also known as Evaluated Receipt Settlemnt(ERS)& Self Billing
59) What is the use of profile options while configure multiorg?
HR:SECURITY PROFILE->restricts data in HR according to Business Group or whatever the criteria we mentioned in the security profile.
HR:User Type-> to complete the org setup,purchasing,payables and recievables responsibilties need it.
MO:SECURITY PROFILE->it restricts the access to certain operating units.not necessary for gl and inventory resposibilities and for HR it is optional.
MO:OPERATING UNIT->which operating unit a particular responsibilty corresponds to.
GL SET OF BOOKS NAME->each responsibilty identified with thisprofile.can see only the accounting information.
60) What is Reorder point and Reorder quantity? and what is its significance?
Reorder point: We have specific on hand balance in warehouse when it is below than i specific quanity (say safety stock) a requisition/mo proposal will generate automatically. So the point at which this proposal will generate known as reorder point.
Reorder point quanity: The minimum order quantity which is defined in warehouse for generating reorder point proposals called reorder point quantity quanity.
Its very important from the planner point of view because as soon we will have less on hnad quanity it will trigger a
requirment in the system for that perticular item.
61) What are the mandatory Flexi fields in Oracle Apps?
62) what is the significance of BILL OF LANDING PROGRAM in Order Management?
A Bill of Lading is a legal Document between the shipper of a particular good and the carrier detailing the type, quantity and destination of the good being carried. The bill of lading also serves as a receipt of shipment when the good is delivered to the predetermined destination. This document must accompany the shipped goods, no matter the form of transportation, and must be signed by an authorized representative from the carrier, shipper and receiver.
For Example:
Suppose that a logistics company must transport gasoline from a plant in Texas to a gas station in Arizona via heavy truck. A plant representative and the driver would sign the bill of lading after the gas is loaded onto the truck. Once the gasoline is delivered to the gas station in Arizona, the truck driver must have the clerk at the station sign the document as well.
63) Is it possible to change the warehouse after booking the Sales order, If Yes how to de allocate the items booked in previous warehouse?
Yes, it is possible ,and every thing is posible .excepet Order type and price list, if price list having same item then ok. similarly if item having same or other org then ok
64) Explain what are the tables of auto invoice?
RA_INTERFACE_LINES_ALL ,
RA_INTERFACE_DISTRIBUTIONS_ALL ARE THE TWO INTERFACE TABLES
MAINLY USED BT AUTOINVOICE.
FURTHER DATA READ FROM THESE TABLES WILL BE INSERTED INTO
RA_CUSTOMER_TRX_ALL , TA_CUSTOMER_TRX_LINES_ALL ,RA_CUST_TRX_LINE_GL_DIST_ALL , AR_PAYMENT_SCHEDULES_ALL
AFTER VALIDATING.
65) How to move the one file from one instance to another instance? And your scripts also?
Using FNDLOAD commands, which creates.ldt files and import those .ldt files in new instance using same command.
66) What is PTO and ATO?
PTO and ATO or Different Ordering Environments,
PTO is Pick To Order: where it is assumed that
availability of stock is plenty , just pick and relese and ship for an order.
ATO is Assemble to Order : it is assuemed that,
availble of option class, all included/standard items/ and depending up on the Order the Assemblies are carried and shipped.
where it is expected that an order is booked for a predefined model item,
67) What is ORDER_TO_CASH cycle?
We perform the following steps in O2C cycle
Enter and Book Sales Order
Schedule the Order
Pick Release the Order
Ship confirm the Order
Auto Invoice to the Receivables for the customer invoicing
Receipts
Bank Reconciliation
68) Explain Oracle Technology Network?
Oracle Technology Network (OTN) is Oracles official community website for Oracle technical professionals. Also referred to as Oracle TechNet or OTN.
OTN professes to be the worlds largest on-line community of DBAs, developers and architects using Oracle products and industry-standard technologies (such as Java, Linux and PHP), with 6+ million registrations.
69) Explain Some useful sections on OTN?
Some of the useful sections on OTN include:
* Oracle documentation
* Product information and demos
* Download products (trial license)
70) Please Explain Connect by Prior?
Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.
"connect by prior" is clause which is used in hierarchical queries
select ename,empno,mgr,job
from emp
start with job='PRESIDENT'
connect by prior empno=mgr;
71) What is schema?
A schema is collection of database objects of a User.
72) What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data.
Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.
73) Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.
XML/BI
Publisher Interview Questions
1) What is SET-OF-BOOKS
Collection of Chat of Accounts and Currency and Calendars is called SOB
2) How can u call a standard interface program from sql or pl/sql code
FND_REQUEST.SUBMIT_REQUEST (........)
3) Calling a Concurrent Program from UNIX
FND_CONCSUB(can submit conc program in host environment)
3) API's FOR CUSTOMER INTERFACE
HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C
HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C
4) PL/SQL stored procedure parameters or what are the two parameters that are mandatory for pl/sql type concurrent program?
(ERRBUF OUT,RETCODE OUT)
ERRBUF :- Used to write the error message to log or request file.
RETCODE :- Populate log request file with program submission details info.
5) What is Value Set?
The value set is a collection (or) container of values.
When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
If the list of values needed to be dynamic and ever changing and define a table based values set.
6) What r the validation types?
1.Independent ------input must exist on previously defined list of values
2.Dependent ------input is checked against a subset of values based on a prior value.
3.Table ----- input is checked against values in an application table
4.Special ------values set uses a flex field itself.
5.Pair ------ two flex fields together specify a range of valid values.
6.Translatable independent ----- input must exist on previously defined list values; translated values can be used.
7.Translatable dependent ------- input is checked against a subset of values based on a prior values; translated value can be used.
8.None -------- validation is minimal.
7) What is template?
The TEMPLATE form is the required starting point for all development of new Forms.
8) What are ad-hoc reports?
Ans.: Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a
particular purpose. For example, ad hoc tax codes or an ad hoc database query
9) What is responsibility?
Is collection of menus, request security groups and data groups
Menus: collection of forms is nothing but menus
Request security groups: collection of programs.
Data groups: is a group of modules to be made accessible by the user through Responsibility
System admin
10) What are different execution methods of executabls?
Host The execution file is a host script.
Oracle Reports The execution file is an Oracle Reports file.
PL/SQL Stored Procedure The execution file is a stored procedure.
SQL*Loader The execution file is a SQL script.
SQL*Plus The execution file is a SQL*Plus script.
SQL*Report The execution file is a SQL*Report script.
Spawned The execution file is a C or Pro*C program.
Immediate The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.
11) Composite Datatypes
PL/SQL TABLES / PL/SQL RECORDS / Nested TABLE / VARRAY
12) What is the sequence of functions – group by,having,orderby in a select statements ?
Select…..Group by…Having…Orderby..
13) Difference between User and Super User?
User : login user or front end user
Super user : it has full access of particular module
14) What is multi org?
“Legal entity has more than one operating unit is called as multi org”
a) Business group --- Human resources information is secured by Business group
b) Legal entity --- inter-company and fiscal/tax reporting.operating unit.
c) Operating unit --- secures AR, OE, AP, PA and PO Information.
d) Organizations --- is a specialize unit of work at particular locations
15) What is ERP Architecture of apps?
A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.
17) Data Link
- Data links relate the results of multiple queries.
- A data link (Parent - Child Relation Ship) causes the child query to be executed once for each instance of its parent group.
18)In which tables FF are stored?
A) FND_ID_FLEXS
B) FND_ID_FLEX_STRUCTURES
19) Oracle Applications Architecture
Internet computing Architecture is a frame work for 3-tired, distributed computing that supports Oracle Applications products.
The Three tiers are
1. Data Base Tier
2. Application Tier
3. Desk Top Tier
Database tier manages Oracle database.
Application tier manages Oracle Applications and other tools.
Desktop tier provides the user interface displace.
With internet computing architecture, only the presentation layer of Oracle Applications is on the Desk Top tier in the form of a plug-in to a standard internet brows
20) List of some API'S
FND_PROGRAM.EXECUTABLE
FND_PROGRAM.REGISTER
FND_PROGRAM.PARAMETER
FND_PROGRAM.ADD_TO_GROUP
FND_REQUEST.SUBMIT_REQUEST
FND_PROFILE.VALUE
FND_PROFILE.GET
FND_PROGRAM.EXECUTABLE
FND_PROGRAM.REGISTER
21) How to get second parameter value based on first parameter?
$flex$.value_setname
22) How to register a table and columns through back end?
by using AD_DD package
23) How to write to a file through concurrent program.
By using FND_FILE package and it can be used only for log and output files.
1.FND_FILE.PUT
this is used to write text to a file with out a new line character
Multilane calls to FND_FILE.PUT will produce consummated text.
Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2);
can be FND_FILE.LOG or FND_FILE.OUTPUT.
2.FND_FILE.PUT_LINE
this procedure as used to write a line of text to a file followed by a new line character.
Procedure FND_FILE.PUT_LINE (which IN number, buff IN varchar2);
EX:- FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get);
3.FND_FILE.NEW_LINE
this procedure is used to write line terminators to a file
procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1);
Ex:- to write two newline characters to a log file
Fnd_file.new_line (fnd_file.log,2);
4.FND_FILE.PUT_NAMES
this procedure as used to set the temporary log file and output filenames and the temporary directory to the user specified values.
This should be called before calling my other FND_FILE procedure and only once per a session.
24) How to checks the request status?
A PL/SQL procedure can check the status of a concurrent request by calling.
FND_CONCURENT.GET_REQUEST_STATUS
FND_CONCURRENT.WAIT_FOR_REQUEST
FND_CONCURRENT.GET_REQUEST_STATUS
25) How do you find that multiorg is installed?
multi organization architecture is meant to allow multiple companies or subsidiaries to store their records with in a single data base.
Multiple organization Architecture allows this by partitioning data through views in APPS schema.
Implementation of Multi org generally includes more than one business group.
To know whether multiorg is existing or not
select multi_org_flag form fnd_product_groups
26) What is a Data Group?
A data group is a group of oracle applications and the Oracle ID?s of each application
Oracle ID grants access privileges to tables in an Oracle Database
Data group determines which Oracle Data base accounts a responsibilities forms, concurrent programs and reports connect to.
27) What is a Responsibility?
Responsibility defines Applications Privileges
A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
Each user has at list one or more responsibilities and several users can share the same responsibility 22
Each responsibility allows access to
a specific application or a set of applications.
A set of books
A restricted list of windows that an user can navigate
Reports in a specific application.
28) What are security Attributes?
Security Attributes are used by Oracle self service web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row.
29) What is a Profile Option?
profile options are the set of changeable options that affects how the application looks and behaves.
By setting profile options, the applications can be made to react in different ways for different users depending on the specific user attributes.
30) What is the relation between Responsibility, Menu and Request Group?
Responsibility: - A responsibility is a set of authority in Oracle Apps that lets users access only that functionality of the application appropriate to their roles.
Menu: - A menu is a hierarchical arrangement of functions and menus of functions that appears in the Navigator. Each responsibility has a menu assigned to it.
Request Group: - it is a collection of reports or concurrent programs. A system Administrator defines report groups in order to control user access to reports and concurrent programs. Only a system administrator can create a request group.
31) What is meant by APPL_TOP?
$APPL_TOP: An environment variable that denotes the installation directory for Oracle Application Object Library and your other Oracle applications. $APPL_TOP is usually one directory level above each of the product directories (which are often referred to as $PROD_TOP or $PRODUCT_TOP or $<prod>_TOP).
32) What are profile options; at what levels can these be set?
A user profile is a set of changeable options that affects the way the applications run. Oracle
Applications object Library establishes a value for each option in a user?s profile when the
User logs on or changes responsibility.
System Profile: - Profile option can be set for the user community.
User Profile: - Provide Oracle Apps with standard information which describes a user,
Application, Responsibility and site. At each profile level user profile options can be set.
33) What is Set of Books?
A financial reporting entity that uses a particular chart of accounts, functional currency,
And accounting calendar. Oracle General Ledger secures transaction information (such as journal entries and balances) by set of books. When you use Oracle General Ledger, you choose a responsibility that specifies a set of books. You then see information for that set of books only.
34)Where do concurrent request logfiles and output files go?
places log files in $APPLCSF/$APPLLOG
Output files go in $APPLCSF/$APPLOUT
35) Difference b/w ROWID and ROWNUM?
ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored.
ROWNUM: It gives a sequence number in which rows are retrieved from the database.
36) Give some examples of pseudo columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE
37) Difference b/w implicit cursor and explicit cursor?
Implicit cursors are automatically created by oracle for all its DML stmts.
Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN;
Explicit cursors are created by the users for multi row select stmts.
38) Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:
DECLARE
CURSOR wip_cur IS
SELECT acct_no, enter_date
FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE" in the cursor select stmt.
"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.
By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.
39) What is RAISE_APPLICATION_ERROR?
The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error
40) What is mutating error?
Mutating error occurs in the following scenario:
WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR.
41) Can we have commit/rollback in DB triggers?
Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.
42) Can we make the trigger an autonomous transaction?
This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.
43) What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
44) What is a REF Cursor?
The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.
45) What is the difference between BINARY_INTEGER and PLS_INTEGER?
binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.
Compared to integer and binary_integer, pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operates on library arithmetic.
pls_integer comes from oracle10g.
binary_integer allows indexing integer for assocative arrays prior to oracle9i.
46) Information about BULK COLLECT and FORALL
Almost every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.
Almost every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.
Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.
BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly
EX : For BULK Exceptions
FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly
EX : For BULK Exceptions
Suppose that I’ve written a program that is supposed to insert 10,000 rows into a table. After inserting 9,000 of those rows, the 9,001st insert fails with a DUP_VAL_ON_INDEX error (a unique index violation). The SQL engine passes that error back to the PL/SQL engine, and if the FORALL statement is written like the one in Listing 4, PL/SQL will terminate the FORALL statement. The remaining 999 rows will not be inserted.
If you want the PL/SQL engine to execute as many of the DML statements as possible, even if errors are raised along the way, add the SAVE EXCEPTIONS clause to the FORALL header. Then, if the SQL engine raises an error, the PL/SQL engine will save that information in a pseudocollection named SQL%BULK_EXCEPTIONS, and continue executing statements. When all statements have been attempted, PL/SQL then raises the ORA-24381 error.
You can—and should—trap that error in the exception section and then iterate through the contents of SQL%BULK_EXCEPTIONS to find out which errors have occurred. You can then write error information to a log table and/or attempt recovery of the DML statement.
BEGIN
FORALL indx IN 1 .. l_eligible_ids.COUNT SAVE EXCEPTIONS
UPDATE employees emp
SET emp.salary = emp.salary + emp.salary * increase_pct_in
WHERE emp.employee_id = l_eligible_ids (indx);
EXCEPTION WHEN OTHERS
THEN
IF SQLCODE = -24381 THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line ( SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX || ‘: ‘ || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
END LOOP;
ELSE RAISE;
END IF;
END
47) PRAGMA TYPES
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.
The 5 types of Pragma directives available in Oracle are listed below:
1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.
2. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.
3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.
EX : RAISE_APPLICATION_ERROR (error_number, error_message);
49) Materialized Views ?
A materialized view is a stored summary containing precomputes results (originating from an SQL select statement). As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers
There are three types of materialized views:
1. Read only materialized view
2. Updateable materialized view
3. Writeable materialized view
Read only materialized views
Advantages: There is no possibility for conflicts as they cannot be updated.
Complex materialized views are supported
Updateable materialized views
Advantages: Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication. Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls. Updateable materialized views require the advnced replication option to be installed / You can make a materialized view updatable during creation by including the
Writeable materialized views
They are created with the for update clause during creation without then adding the materialized view to a materialized view group. In such a case, the materialized view is updatable, but the changes are lost when the materialized view refreshes.
The query rewrite facility is totally transparent to an application which needs not be aware of the existance of the underlying materialized view.
Refreshing a materialized view synchronizes is with its master table.Oracle performs the following operations when refreshing a materialized view.
In the case of a complete refresh (usingdbms_mview.refresh) sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh. The materialized base view is truncated.All rows selected from the master table are inserted into the snapshot base table. sys.slog$ is updated to reflect the time of the refresh.
In the case of a fast refresh, the steps are: sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh. Rows in the materialized base view are deleted. All rows selected from the master table are inserted into the snapshot base table. sys.slog$ is updated to reflect the time of the refresh.
Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (<schema name>.MLOG$_table)
If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
The following query checks for this:
select o.owner "Owner",
o.object_name "Mat View",
username "Username",
s.sid "Sid"
from v$lock l,
dba_objects o,
v$session s
where o.object_id = l.id1
and l.type ='JI'
and l.lmode = 6
and s.sid = l.sid
and o.object_type = 'TABLE'
Errors during the automatic refresh of materialized views
If an error occurs during the automatic refresh of a materialized view, an error message is written into the alert.log.
50) PL/SQL Data Types
Every constant, variable, and parameter has a data type (also called a type) that determines its storage format, constraints, valid range of values, and operations that can be performed on it. PL/SQL provides many predefined data types and subtypes, and lets you define your own PL/SQL subtypes.
Data Type Category Data Description
1. Scalar Single values with no internal components.
2. Composite Data items that have internal components that can be accessed individually.
3. Reference Pointers to other data items. Explained in Using Cursor Variables (REF CURSORs).
4. Large Object (LOB) Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.
1.Scalar ; Scalar data types store single values with no internal components.
Ex : 1.1 Numeric - PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE,
NUMBER
1.2 Character - CHAR,VARCHAR2, NCHAR, NVARCHAR2, LONG, ROWID,RAW (The RAW
data type stores binary or byte strings,such as sequences of graphics characters or
digitized pictures)
1.3 BOOLEAN -
1.4 Datetime - You use the DATE data type to store fixed-length datetimes
1.5 Interval - Use the data type INTERVAL YEAR TO MONTH to store and manipulate intervals of
years and months
2. Composite : A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite.
2.1 Collection Types
2.1.1 Associative array (or index-by table)
2.1.2 variable-size array (VARRAY)
2.1.3 Nested table
2.1.1 An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).
The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.
Like a database table, an associative array: Is empty (but not null) until you populate it , Can hold an unspecified number of elements, which you can access without knowing their positions
Unlike a database table, an associative array: Does not need disk space or network operations, Cannot be manipulated with DML statements
TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
2.1.2 A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size. To access an element of a varray variable, use the syntax variable_name(index). The lower bound of index is 1; the upper bound is the current number of elements.
A varray is appropriate when: You know the maximum number of elements. You usually access the elements sequentially.
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
2.1.3 In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable.
1. An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
2. An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.
Appropriate Uses for Nested Tables
1. The number of elements is not set.
2. Index values are not consecutive.
3. You must delete or update some elements, but not all elements simultaneously.
4. Nested table data is stored in a separate store table, a system-generated database table. When you
access a nested table, the database joins the nested table with its store table. This makes nested tables
suitable for queries and updates that affect only some elements of the collection.
5. You would create a separate lookup table, with multiple entries for each row of the main table, and
access it through join queries.
2.2 Collection Methods
2.2.1 DELETE - Procedure - Deletes elements from collection.
2.2.2 TRIM - Procedure - Deletes elements from end of varray or nested table.
2.2.3 EXTEND - Procedure - Adds elements to end of varray or nested table.
2.2.4 EXISTS - Function - Returns TRUE if and only if specified element of varray or nested table exists.
2.2.5 FIRST - Function - Returns first index in collection.
2.2.6 LAST - Function - Returns last index in collection.
2.2.7 COUNT - Function - Returns number of elements in collection.
2.2.8 LIMIT - Function - Returns maximum number of elements that collection can have.
2.2.9 PRIOR - Function - Returns index that precedes specified index.
2.2.10 NEXT - Function - Returns index that succeeds specified index.
3. Reference : Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has data type REF CURSOR, and you might see them referred to informally as REF CURSORs.
REF CURSOR types can be strong (with a return type) or weak (with no return type). Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.
-- Strong:
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
-- Weak:
TYPE genericcurtyp IS REF CURSOR;
cursor1 empcurtyp;
cursor2 genericcurtyp;
4. Large object (LOB) data types reference large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. LOB data types allow efficient, random, piecewise access to this data.
4.1 BFILE Data Type : You use the BFILE data type to store large binary objects in operating system files outside the database. Every BFILE variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name. Logical path names are not supported.
BFILEs are read-only, so you cannot modify them. Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.
BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, which is system dependent.
4.2 BLOB Data Type : You use the BLOB data type to store large binary objects in the database, inline or out-of-line. Every BLOB variable stores a locator, which points to a large binary object.
BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.
4.3 CLOB Data Type : You use the CLOB data type to store large blocks of character data in the database, inline or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data.
CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions.
51) Oracle Report Triggers?
There are eight report triggers.
53) NOCOPY Clause
The final point to cover in passing variables is the NOCOPY clause . When a parameter is passed as an IN variable, it is passed by reference. Since it will not change, PL/SQL uses the passed variable in the procedure/function. When variables are passed in OUT or INOUT mode, a new variable is define, and the value is copied to the passed variable when the procedure ends. If the variable is a large structure such as a PL/SQL table or an array, the application could see a performance degradation cause by copying this structure.
The NOCOPY clause tells to PL/SQL engine to pass the variable by reference, thus avoiding the cost of copying the variable at the end of the procedure. The PL/SQL engine has requirements that must be met before passing the variable by reference and if those requirements are not met, the NOCOPY clause will simply be ignored by the PL/SQL engine.
47) PRAGMA TYPES
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.
The 5 types of Pragma directives available in Oracle are listed below:
1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.
2. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.
3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.
48) Exception Types
There are four kinds of exceptions in PL/SQL:
1. Named system exceptions: Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.
There are four kinds of exceptions in PL/SQL:
1. Named system exceptions: Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
2. Named programmer-defined exceptions:Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.
EX : Exception_name EXCEPTION;
2. Named programmer-defined exceptions:Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.
EX : Exception_name EXCEPTION;
3. Unnamed system exceptions: Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.
EX : Child_rec_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);
4. Unnamed programmer-defined exceptions: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.
PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);
4. Unnamed programmer-defined exceptions: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.
EX : RAISE_APPLICATION_ERROR (error_number, error_message);
49) Materialized Views ?
A materialized view is a stored summary containing precomputes results (originating from an SQL select statement). As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers
There are three types of materialized views:
1. Read only materialized view
2. Updateable materialized view
3. Writeable materialized view
Read only materialized views
Advantages: There is no possibility for conflicts as they cannot be updated.
Complex materialized views are supported
CREATE MATERIALIZED VIEW hr.employees AS
SELECT * FROM hr.employees@orc1.world;
Updateable materialized views
Advantages: Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication. Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls. Updateable materialized views require the advnced replication option to be installed / You can make a materialized view updatable during creation by including the
FOR UPDATE clause CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
SELECT * FROM hr.departments@orc1.world;
Writeable materialized views
They are created with the for update clause during creation without then adding the materialized view to a materialized view group. In such a case, the materialized view is updatable, but the changes are lost when the materialized view refreshes.
The query rewrite facility is totally transparent to an application which needs not be aware of the existance of the underlying materialized view.
Refreshing a materialized view synchronizes is with its master table.Oracle performs the following operations when refreshing a materialized view.
In the case of a complete refresh (usingdbms_mview.refresh) sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh. The materialized base view is truncated.All rows selected from the master table are inserted into the snapshot base table. sys.slog$ is updated to reflect the time of the refresh.
In the case of a fast refresh, the steps are: sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh. Rows in the materialized base view are deleted. All rows selected from the master table are inserted into the snapshot base table. sys.slog$ is updated to reflect the time of the refresh.
Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (<schema name>.MLOG$_table)
If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
The following query checks for this:
select o.owner "Owner",
o.object_name "Mat View",
username "Username",
s.sid "Sid"
from v$lock l,
dba_objects o,
v$session s
where o.object_id = l.id1
and l.type ='JI'
and l.lmode = 6
and s.sid = l.sid
and o.object_type = 'TABLE'
Errors during the automatic refresh of materialized views
If an error occurs during the automatic refresh of a materialized view, an error message is written into the alert.log.
50) PL/SQL Data Types
Every constant, variable, and parameter has a data type (also called a type) that determines its storage format, constraints, valid range of values, and operations that can be performed on it. PL/SQL provides many predefined data types and subtypes, and lets you define your own PL/SQL subtypes.
Data Type Category Data Description
1. Scalar Single values with no internal components.
2. Composite Data items that have internal components that can be accessed individually.
3. Reference Pointers to other data items. Explained in Using Cursor Variables (REF CURSORs).
4. Large Object (LOB) Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.
1.Scalar ; Scalar data types store single values with no internal components.
Ex : 1.1 Numeric - PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE,
NUMBER
1.2 Character - CHAR,VARCHAR2, NCHAR, NVARCHAR2, LONG, ROWID,RAW (The RAW
data type stores binary or byte strings,such as sequences of graphics characters or
digitized pictures)
1.3 BOOLEAN -
1.4 Datetime - You use the DATE data type to store fixed-length datetimes
1.5 Interval - Use the data type INTERVAL YEAR TO MONTH to store and manipulate intervals of
years and months
2. Composite : A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite.
2.1 Collection Types
2.1.1 Associative array (or index-by table)
2.1.2 variable-size array (VARRAY)
2.1.3 Nested table
2.1.1 An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).
The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.
Like a database table, an associative array: Is empty (but not null) until you populate it , Can hold an unspecified number of elements, which you can access without knowing their positions
Unlike a database table, an associative array: Does not need disk space or network operations, Cannot be manipulated with DML statements
TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
2.1.2 A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size. To access an element of a varray variable, use the syntax variable_name(index). The lower bound of index is 1; the upper bound is the current number of elements.
A varray is appropriate when: You know the maximum number of elements. You usually access the elements sequentially.
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
2.1.3 In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable.
1. An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
2. An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.
Appropriate Uses for Nested Tables
1. The number of elements is not set.
2. Index values are not consecutive.
3. You must delete or update some elements, but not all elements simultaneously.
4. Nested table data is stored in a separate store table, a system-generated database table. When you
access a nested table, the database joins the nested table with its store table. This makes nested tables
suitable for queries and updates that affect only some elements of the collection.
5. You would create a separate lookup table, with multiple entries for each row of the main table, and
access it through join queries.
2.2 Collection Methods
2.2.1 DELETE - Procedure - Deletes elements from collection.
2.2.2 TRIM - Procedure - Deletes elements from end of varray or nested table.
2.2.3 EXTEND - Procedure - Adds elements to end of varray or nested table.
2.2.4 EXISTS - Function - Returns TRUE if and only if specified element of varray or nested table exists.
2.2.5 FIRST - Function - Returns first index in collection.
2.2.6 LAST - Function - Returns last index in collection.
2.2.7 COUNT - Function - Returns number of elements in collection.
2.2.8 LIMIT - Function - Returns maximum number of elements that collection can have.
2.2.9 PRIOR - Function - Returns index that precedes specified index.
2.2.10 NEXT - Function - Returns index that succeeds specified index.
3. Reference : Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has data type REF CURSOR, and you might see them referred to informally as REF CURSORs.
REF CURSOR types can be strong (with a return type) or weak (with no return type). Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.
-- Strong:
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
-- Weak:
TYPE genericcurtyp IS REF CURSOR;
cursor1 empcurtyp;
cursor2 genericcurtyp;
4. Large object (LOB) data types reference large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. LOB data types allow efficient, random, piecewise access to this data.
4.1 BFILE Data Type : You use the BFILE data type to store large binary objects in operating system files outside the database. Every BFILE variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name. Logical path names are not supported.
BFILEs are read-only, so you cannot modify them. Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.
BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, which is system dependent.
4.2 BLOB Data Type : You use the BLOB data type to store large binary objects in the database, inline or out-of-line. Every BLOB variable stores a locator, which points to a large binary object.
BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.
4.3 CLOB Data Type : You use the CLOB data type to store large blocks of character data in the database, inline or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data.
CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions.
51) Oracle Report Triggers?
There are eight report triggers.
Global triggers called the Report Triggers :
(1) Before Parameter Form
(2) After Parameter Form
(3) Before Report
(4) After Report
(5) Between Pages
Other Triggers :
(6) Validation Triggers
(7) Format Triggers
(8) Action Triggers
(1) Before Parameter Form Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).
(2) After Parameter Form Fires after the Runtime Parameter Form is displayed. Fromfires. Consequently, you can use this trigger for validation of command line parameters or other data).
(3) Before Report Fires before the report is executed but after queries are parsed and data is fetched.
(4) Between Pages Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)
(5) After Report Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.
(6) Validation Triggers Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.
(7) Format Triggers 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.
(8) Action Triggers Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.
52) Forward Declarations
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. PL/SQL solves the problem of subprograms used before they are declared by providing a special subprogram declaration called forward declaration.
A forward declaration consists of a subprogram specification terminated by a semicolon.
52) Forward Declarations
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. PL/SQL solves the problem of subprograms used before they are declared by providing a special subprogram declaration called forward declaration.
A forward declaration consists of a subprogram specification terminated by a semicolon.
DECLARE
PROCEDURE calc_rating (...); -- forward declaration /* Define subprogram in alphabetical order */
PROCEDURE award_bonus (..)
IS
BEGIN
calc_rating(..); ...
END;
PROCEDURE calc_rating (...)
IS
BEGIN ...
END; ...
END;
In Simple terminology we can create a procedure / function in package body without declaring them in the package Specification, but you can provide the procedure name before calling the procedure in package body.
Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same block, subprogram, or package.
END;
In Simple terminology we can create a procedure / function in package body without declaring them in the package Specification, but you can provide the procedure name before calling the procedure in package body.
Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same block, subprogram, or package.
The final point to cover in passing variables is the NOCOPY clause . When a parameter is passed as an IN variable, it is passed by reference. Since it will not change, PL/SQL uses the passed variable in the procedure/function. When variables are passed in OUT or INOUT mode, a new variable is define, and the value is copied to the passed variable when the procedure ends. If the variable is a large structure such as a PL/SQL table or an array, the application could see a performance degradation cause by copying this structure.
The NOCOPY clause tells to PL/SQL engine to pass the variable by reference, thus avoiding the cost of copying the variable at the end of the procedure. The PL/SQL engine has requirements that must be met before passing the variable by reference and if those requirements are not met, the NOCOPY clause will simply be ignored by the PL/SQL engine.
54) Collection Exceptions
| Collection Exception | Raised when... |
|---|---|
COLLECTION_IS_NULL | you try to operate on an atomically null collection. |
NO_DATA_FOUND | a subscript designates an element that was deleted, or a nonexistent element of an associative array. |
SUBSCRIPT_BEYOND_COUNT | a subscript exceeds the number of elements in a collection. |
SUBSCRIPT_OUTSIDE_LIMIT | a subscript is outside the allowed range. |
VALUE_ERROR | a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range. |
DECLARE
TYPE WordList IS TABLE OF VARCHAR2(5);
words WordList;
err_msg VARCHAR2(100);
PROCEDURE display_error IS
BEGIN
err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('Error message = ' || err_msg);
END;
BEGIN
BEGIN
words(1) := 10; -- Raises COLLECTION_IS_NULL
-- A constructor has not been used yet.
-- Note: This exception applies to varrays and nested tables,
-- but not to associative arrays which do not need a constructor.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
-- After using a constructor, you can assign values to the elements.
words := WordList('1st', '2nd', '3rd'); -- 3 elements created
-- Any expression that returns a VARCHAR2(5) is valid.
words(3) := words(1) || '+2';
BEGIN
words(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR
-- The assigned value is too long.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
BEGIN
words('B') := 'dunno'; -- Raises VALUE_ERROR
-- The subscript (B) of a nested table must be an integer.
-- Note: Also, NULL is not allowed as a subscript.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
BEGIN
words(0) := 'zero'; -- Raises SUBSCRIPT_OUTSIDE_LIMIT
-- Subscript 0 is outside the allowed subscript range.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
BEGIN
words(4) := 'maybe'; -- Raises SUBSCRIPT_BEYOND_COUNT
-- The subscript (4) exceeds the number of elements in the table.
-- To add new elements, invoke the EXTEND method first.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
BEGIN
words.DELETE(1);
IF words(1) = 'First' THEN NULL; END IF;
-- Raises NO_DATA_FOUND
-- The element with subcript (1) was deleted.
EXCEPTION
WHEN OTHERS THEN display_error;
END;
END;
/
55) Suppose where the Invoice given by supplier is in USD, but the payment can be done in GBP. Is it possible in AP?
Not possible
56) What is the difference between format trigger and action trigger?
format trigger : when we want hide or display the objects in in layout model dynamically we can use format trigger
action trigger : when a button selectd procedure executed new report open format trigger we can write in item level
57) What is global tempory table?
Global temporary tables are temporary table for perticular session. Global temporary tables are two types,
1.Transaction wise temporary table
In transaction wise temporary tables we will use on commit delete rows keyword, which delete the data whenever we do commit.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
2.Session wise temporary tables.
In session wise temporary table we will use on commit preserve rows keyword, which preserve the data till the end of that session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
58) What is Automation of Accounts Payable activities (Evaluated Receipt settlement)?
Payment on receipt enables u to automatically create std,unapproved invoices for payment of goods based on receipt transaction.Invoices r created using a combination of receipt & PO info,eliminating duplicate manual data entry and ensuringmaccurateand timely data processig. Paymrnt on receipt is also known as Evaluated Receipt Settlemnt(ERS)& Self Billing
59) What is the use of profile options while configure multiorg?
HR:SECURITY PROFILE->restricts data in HR according to Business Group or whatever the criteria we mentioned in the security profile.
HR:User Type-> to complete the org setup,purchasing,payables and recievables responsibilties need it.
MO:SECURITY PROFILE->it restricts the access to certain operating units.not necessary for gl and inventory resposibilities and for HR it is optional.
MO:OPERATING UNIT->which operating unit a particular responsibilty corresponds to.
GL SET OF BOOKS NAME->each responsibilty identified with thisprofile.can see only the accounting information.
60) What is Reorder point and Reorder quantity? and what is its significance?
Reorder point: We have specific on hand balance in warehouse when it is below than i specific quanity (say safety stock) a requisition/mo proposal will generate automatically. So the point at which this proposal will generate known as reorder point.
Reorder point quanity: The minimum order quantity which is defined in warehouse for generating reorder point proposals called reorder point quantity quanity.
Its very important from the planner point of view because as soon we will have less on hnad quanity it will trigger a
requirment in the system for that perticular item.
61) What are the mandatory Flexi fields in Oracle Apps?
Owner
|
Key Flexfield Name
|
Flexfield Code
|
Oracle Assets
|
Asset Key Flexfield
|
KEY#
|
Oracle Assets
|
Category Flexfield
|
CAT#
|
Oracle Assets
|
Location Flexfield
|
LOC#
|
Oracle General Ledger
|
Accounting Flexfield
|
GL#
|
Oracle Human Resources
|
Grade Flexfield
|
GRD
|
Oracle Human Resources
|
Job Flexfield
|
JOB
|
Oracle Human Resources
|
Personal Analysis Flexfield
|
PEA
|
Oracle Human Resources
|
Position Flexfield
|
POS
|
Oracle Human Resources
|
Soft Coded KeyFlexfield
|
SCL
|
Oracle Inventory
|
Account Aliases
|
MDSP
|
Oracle Inventory
|
Item Catalogs
|
MICG
|
Oracle Inventory
|
Item Categories
|
MCAT
|
Oracle Inventory
|
SalesOrders
|
RLOC
|
Oracle Inventory
|
Stock Locators
|
MTLL
|
Oracle Inventory
|
System Items
|
MSTK
|
Oracle Payroll
|
Bank Details KeyFlexField
|
BANK
|
Oracle Payroll
|
Cost Allocation Flexfield
|
COST
|
Oracle Payroll
|
People Group Flexfield
|
GRP
|
Oracle Receivables
|
Sales Tax Location Flexfield
|
MKTS
|
Oracle Receivables
|
Territory Flexfield
|
CT#
|
Oracle Service
|
Oracle Service Item Flexfield
|
SERV
|
Oracle Training Administration
|
Training Resources
|
RES
|
A Bill of Lading is a legal Document between the shipper of a particular good and the carrier detailing the type, quantity and destination of the good being carried. The bill of lading also serves as a receipt of shipment when the good is delivered to the predetermined destination. This document must accompany the shipped goods, no matter the form of transportation, and must be signed by an authorized representative from the carrier, shipper and receiver.
For Example:
Suppose that a logistics company must transport gasoline from a plant in Texas to a gas station in Arizona via heavy truck. A plant representative and the driver would sign the bill of lading after the gas is loaded onto the truck. Once the gasoline is delivered to the gas station in Arizona, the truck driver must have the clerk at the station sign the document as well.
63) Is it possible to change the warehouse after booking the Sales order, If Yes how to de allocate the items booked in previous warehouse?
Yes, it is possible ,and every thing is posible .excepet Order type and price list, if price list having same item then ok. similarly if item having same or other org then ok
64) Explain what are the tables of auto invoice?
RA_INTERFACE_LINES_ALL ,
RA_INTERFACE_DISTRIBUTIONS_ALL ARE THE TWO INTERFACE TABLES
MAINLY USED BT AUTOINVOICE.
FURTHER DATA READ FROM THESE TABLES WILL BE INSERTED INTO
RA_CUSTOMER_TRX_ALL , TA_CUSTOMER_TRX_LINES_ALL ,RA_CUST_TRX_LINE_GL_DIST_ALL , AR_PAYMENT_SCHEDULES_ALL
AFTER VALIDATING.
65) How to move the one file from one instance to another instance? And your scripts also?
Using FNDLOAD commands, which creates.ldt files and import those .ldt files in new instance using same command.
66) What is PTO and ATO?
PTO and ATO or Different Ordering Environments,
PTO is Pick To Order: where it is assumed that
availability of stock is plenty , just pick and relese and ship for an order.
ATO is Assemble to Order : it is assuemed that,
availble of option class, all included/standard items/ and depending up on the Order the Assemblies are carried and shipped.
where it is expected that an order is booked for a predefined model item,
67) What is ORDER_TO_CASH cycle?
We perform the following steps in O2C cycle
Enter and Book Sales Order
Schedule the Order
Pick Release the Order
Ship confirm the Order
Auto Invoice to the Receivables for the customer invoicing
Receipts
Bank Reconciliation
68) Explain Oracle Technology Network?
Oracle Technology Network (OTN) is Oracles official community website for Oracle technical professionals. Also referred to as Oracle TechNet or OTN.
OTN professes to be the worlds largest on-line community of DBAs, developers and architects using Oracle products and industry-standard technologies (such as Java, Linux and PHP), with 6+ million registrations.
69) Explain Some useful sections on OTN?
Some of the useful sections on OTN include:
* Oracle documentation
* Product information and demos
* Download products (trial license)
70) Please Explain Connect by Prior?
Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.
"connect by prior" is clause which is used in hierarchical queries
select ename,empno,mgr,job
from emp
start with job='PRESIDENT'
connect by prior empno=mgr;
71) What is schema?
A schema is collection of database objects of a User.
72) What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data.
Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.
73) Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.
XML/BI
Publisher Interview Questions
What is BI Publisher?
It is a reporting
tool for generating the reports. More than tool it is an engine that can be
integrated with systems supporting the business.Is BI Publisher integrated with Oracle Apps?
Yes, it is tightly integrated with Oracle Apps for reporting needs. In 11.5.10 instances xml publisher was used, in R12 we can it BI Publisher
integrated with systems supporting the business.Is BI Publisher integrated with Oracle Apps?
Yes, it is tightly integrated with Oracle Apps for reporting needs. In 11.5.10 instances xml publisher was used, in R12 we can it BI Publisher
What is the difference between xml publisher and BI Publisher?
Name is the difference, initially it was released on the name of xml publisher( the initial patchset), later on they have added more features and called it Business Intelligence Publisher. In BI by default we have integration with Data definitions in R12 instance. Both these names can be used interchangeably
Name is the difference, initially it was released on the name of xml publisher( the initial patchset), later on they have added more features and called it Business Intelligence Publisher. In BI by default we have integration with Data definitions in R12 instance. Both these names can be used interchangeably
What are the various components required for developing a BI
publisher report?
Data Template, Layout template and the integration with Concurrent Manager.
Data Template, Layout template and the integration with Concurrent Manager.
How does the concurrent program submitted by the user knows
about the datatemplate or layout template it should be using for
generating the output?
The concurrent program ‘shortname’ will be mapped to the ‘code’ of the Datatemplate. Layout template is attached to the datatemplate, this forms the mapping between all the three.
The concurrent program ‘shortname’ will be mapped to the ‘code’ of the Datatemplate. Layout template is attached to the datatemplate, this forms the mapping between all the three.
What is a datatemplate?
Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output
Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output
What is a layout template?
Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.
Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.
What are the output formats supported by layout template?
xls, html, pdf, eText etc are supported based on the business need.
xls, html, pdf, eText etc are supported based on the business need.
Do you need to write multiple layout templates for each output
type like html/pdf?
No, only layout template will be created, BI Publisher generates desired output format when the request is run
No, only layout template will be created, BI Publisher generates desired output format when the request is run
What is the default output format of the report?
The default output format defined during the layout template creation will be used to generate the output, the same can be modified during the request submission and it will overwrite the one defined at layout template
The default output format defined during the layout template creation will be used to generate the output, the same can be modified during the request submission and it will overwrite the one defined at layout template
Can you have multiple layout templates for a singe data
template?
Yes, multiple layouts can be defined, user has a choice here to use one among them at run time during conc request submission
Yes, multiple layouts can be defined, user has a choice here to use one among them at run time during conc request submission
Where do you register data and layout templates?
Layout template will be registered under xml publisher administrator responsibility>Templates tab.
Data template will be registered under xml publisher admininstrator responsibility> Data Definitions
Layout template will be registered under xml publisher administrator responsibility>Templates tab.
Data template will be registered under xml publisher admininstrator responsibility> Data Definitions
I want to create a report output in 10 languages, do I have to
create 10 layout templates?
No, BI Publisher provides the required translation for your templates, based on the number of languages installed in your oracle apps environment requires outputs are provided
No, BI Publisher provides the required translation for your templates, based on the number of languages installed in your oracle apps environment requires outputs are provided
What is the required installation for using BI Pub report?
BI Publisher deskop tool has be installed. Using this tool you can preview or test the report before deploying the same on to the instance.
BI Publisher deskop tool has be installed. Using this tool you can preview or test the report before deploying the same on to the instance.
How do you move your
layout or data template across instances?
xdoloader is the utility that will be used.
xdoloader is the utility that will be used.
What is the tool to map required data output and layout
templates so that they can be tested in local machine?
Template viewer will be used for the same.
Template viewer will be used for the same.
Which component is responsible for generating the output in xml
format before applying it to layout template?
DataEngine will take DataTemplate as the input and the output will be generated in xml format which will then be applied on layout template
DataEngine will take DataTemplate as the input and the output will be generated in xml format which will then be applied on layout template
Can BI publisher reports be used in OAF pages?
XDO template utility helper java classes are provided for the same.
XDO template utility helper java classes are provided for the same.
Name some business use cases for BI reports?
Bank EFT, customer documents, shipping documents, internal analysis documents or any transactional documents
Bank EFT, customer documents, shipping documents, internal analysis documents or any transactional documents
How do you pass parameters to your report?
Concurrent program parameters should be passed, ensure that the parameter name/token are same as in the conc prog defn and the data template
Concurrent program parameters should be passed, ensure that the parameter name/token are same as in the conc prog defn and the data template
What are the various sections in the data template?
Parameter section
Trigger Section
Sql stmt section
Data Structure section
Lexical Section
Parameter section
Trigger Section
Sql stmt section
Data Structure section
Lexical Section
What does lexical section contain?
The required lexical clause of Key Flex field or Descriptive FF are created under this section
The required lexical clause of Key Flex field or Descriptive FF are created under this section
What triggers are supported in Data template?
Before report and After report are supported
Before report and After report are supported
Where is the trigger code written?
The code is written in the plsql package which is given under ‘defaultpackage’ tag of data template.
The code is written in the plsql package which is given under ‘defaultpackage’ tag of data template.
what is the file supporting the translation for a layout template?
xliff is the file that supports the translation, you can modify the same as required.
xliff is the file that supports the translation, you can modify the same as required.
How do you display the company logo on the report output?
Copy and paste the logo (.gif. or any format) on the header section of .rtf file . Ensure you resize per the company standards.
Copy and paste the logo (.gif. or any format) on the header section of .rtf file . Ensure you resize per the company standards.
No comments:
Post a Comment