Oracle Apps Technical Interview Questions – Part 5

By | September 27, 2014

Following is List of articles for Oracle Apps Technical Questions

Alternatively, Go through the following Top 10 Oracle Apps Technical Questions

List of Oracle Apps Technical Interview Questions – Part 5

1. What is difference between TRUNCATE & DELETE
2. We have one million records in our log table, which command either Delete/ Truncate should be used and why?
3. What are the advantages of VIEW?
4. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
5. how to find out duplicate records ?
6. What are the different types of files used in SQL Loader ?
7. Can you have multiple layout templates for a single data template?
8. What is the executable and concurrent program used to develop a report without RDF?
9. How to do conditional formatting in XML Publisher?
10. What is the difference between Conditional Formatting and format trigger?
11. I want to create a report output in 10 languages, do I have to create 10 layout templates?
12. How do you pass parameters to your report?For RDF based Reports
13. What are the various sections in the data template?Parameter section
14. What triggers are supported in Data template?
15. Where is the trigger code written?
16. What is the file supporting the translation for a layout template?
17. How do you display the company logo on the report output?
18. In order to enable the attachment feature, what minimum information is needed to know.
19. What are steps to enable attachment feature on any apps form?
20. How do you get profile options values from with in an PL/SQL procedure?

1. What is difference between TRUNCATE & DELETE

*  TRUNCATE is a DDL command whereas DELETE is a DML command.
*  TRUNCATE is much faster than DELETE.

When you user DELETE statement, all data get copied into the Rollback Tablespace first, then delete operation get performed. Thatswhy when you user ROLLBACK statement after deleting rows from a table ,you can get back the data(System get it for you from the Rollback Tablespace). All this process take times.

But when you use TRUNCATE statement, it removes data directly without copying it into the Rollback Tablespace. Thatswhy TRUNCATE is faster. Once you Truncate you cann’t get back the data.

*  You cann’t rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
*  In case of TRUNCATE ,DML Trigger doesn’t get fired.But in DML commands like DELETE .Trigger get fired.
*  You cann’t use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
*  That TRUNCATE command resets the High Water Mark for the table but DELETE does not has any impact on higher water mark.

Back to top

2. We have one million records in our log table, which command either Delete/ Truncate should be used and why?

Truncate should be used as it will not generate redo logs and hence data will be wiped out much fastly.

Back to top

3. What are the advantages of VIEW?

* To protect some of the columns of a table from other users.
* To hide complexity of a query.
* To hide complexity of calculations.

Back to top

4. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

The SQLCODE returns the value of the error number for the last error encountered.

The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

Back to top

5. how to find out duplicate records ?

select * from emp where rowid not in ( select min(rowid) from emp group by ename);

Back to top

6. What are the different types of files used in SQL Loader ?

There are 5 files are user in SQL Loader.
they are.

1. Data File.
2.Control File.
3.Log file.
4.Bad File.
5.Discard file.

data file and control file are required file.
example
SQL> sqlldr userid=apps/apps control=XYZ.ctl

This control file contain the reference of data file. Log file and Bad file automatically created by sql Loader whereas Discard file is optional.

Back to top

7. Can you have multiple layout templates for a single 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

Back to top

8. What is the executable and concurrent program used to develop a report without RDF?

XDODTEXE is used as executable and jAVA concurrent program is used as concurrent program for developing report without RDF.

Back to top

9. How to do conditional formatting in XML Publisher?

Conditional formatting in BI Publisher/ XML Publisher can be done in these some of the following way:
1) if statements
2) if then else statements :
3)choose statements : for multiple conditions

There are more ways for conditional formatting in addition to the above mentioned methods like column formatting, row formatting etc.

Back to top

10. What is the difference between Conditional Formatting and format trigger?

In Oracle reports, we have two option to control the formatting of data

Through GUI
This is user interface in oracle reports where we can define the conditions and logic to display data as per our requirement. e.g If amount > 100 its colos should be red other wise no color. Etc

Through Format Triggers
For each text item in oracle reports, you can attach a format trigger, it returns a BOOLEAN value. If you return FALSE, value will not be displayed in output. In this way you can control the behavior e.g if amount is zero no need to display the data etc.

Another person replied this question in following words.
Both provide the same functionality, used to format the output based on particular conditions. Format triggers provide a wide variety of options when compared to conditional formatting(GUI). In format Triggers we have the option to write PL/SQL code where as conditional formatting is GUI based which provide limited options.

Back to top

11. 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

Back to top

12. How do you pass parameters to your report?For RDF based Reports

Token defined at concurrent program parameter level should be exactly same as parameter defined in RDF report.
Token for each parameter should match with respective parameter in RDF report to pass the data. If they mismatch, there will be no error but data will not be passed.

For XML Based Reports (Where executable is XDODTXE)

Token defined at concurrent program parameter level should be exactly same as parameter defined in data template.

Back to top

13. What are the various sections in the data template?Parameter section

Trigger Section
Sql stmt section
Data Structure section
Lexical Section

Back to top

14. What triggers are supported in Data template?

Before report and After report are supported

Back to top

15. Where is the trigger code written?

The code is written in the plsql package which is given under ‘defaultpackage’ tag of data template.

Back to top

16. 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.

Back to top

17. 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.

Back to top

18. In order to enable the attachment feature, what minimum information is needed to know.

Once the block we want to enable attachments for is identified, we have to identify the following information for it.
*  Form Name: PERWSMEA
*  Function Name: PERWSMEA
*  Block Name: MEA
*  Base Table Name: PER_MEDICAL_ASSESSMENTS
*  Primary Key Columns: MEDICAL_ASSESSMENT_ID

Back to top

19. What are steps to enable attachment feature on any apps form?

1. Define document entity
2. Define document category
3. Enable Attachment for the form using “Attachment Functions” menu item in attachments Menu
I. Associate document category created in step2 with attachment function created in step 3.
II. Click on Block button and identify block access method (either allow change of Query Only). Click on Entities
a. select the the privileges
b. Assign primary key fields.

Back to top

20. How do you get profile options values from with in an PL/SQL procedure?

By calling the standard FND_PROFILE.VALUE() procedure.

Following are the FND_PROFILE values that can be used in the PL/SQL code:

fnd_profile.value(‘MFG_ORGANIZATION_ID‘);
fnd_profile.value(‘ORG_ID‘);
fnd_profile.value(‘LOGIN_ID‘);
fnd_profile.value(‘USER_ID‘);
fnd_profile.value(‘USERNAME‘);
fnd_profile.value(‘CONCURRENT_REQUEST_ID‘);
fnd_profile.value(‘GL_SET_OF_BKS_ID‘);
fnd_profile.value(‘SO_ORGANIZATION_ID‘);
fnd_profile.value(‘APPL_SHRT_NAME‘);
fnd_profile.value(‘RESP_NAME‘);
fnd_profile.value(‘RESP_ID‘);

 

Prev Oracle Apps Technical Interview Questions – Part 4                                            Next Oracle Apps Technical Interview Questions – Part 6

Related Posts:

Oracle HRMS Interview Questions
Question of the Day
9 Steps to become Oracle Apps Consultant
How to create backend logging mechanism
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only
Step to Register Discoverer Report
Hierarchy of OLM Tables