Oracle 11i Application Developer – Technical FAQ – Part 2


Hello Friends, here is the second set of Questions on Application Developers , more of general questions, i am not digging in particular areas , only trying to put question which i normally ask with candidates. If you want help in any specific areas , plz advise, i will try to do that.

Q11 ) What is SET-OF-BOOKS?
Collection of Chat of Accounts and Currency and Calendars is called SOB

Q12 ) What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.

Q13 Tell me what is the procedure to develop an interface?
a. First we will get the Requirement document.
b. We will create control file based on that plot file.
c. Then the control files which loads the data into staging tables.
d. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
e. Through the standard programs we will push the data from interface tables to Base tables.

Q14) 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.
c) Operating unit — secures AR, OE, AP, PA and PO Information.
d) Organizations — is a specialize unit of work at particular locations

Q15) What are the User PARAMETERS in the Reports?
P_CONC_REQUEST_ID
P_FLEX_VALUE

Q16) FND USER EXITS:-
FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.
FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL are used to display flex field information like prompt, value etc
FND FLEXSQL these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column

Q17) what are the two parameters that are mandatory for pl/sql type concurrent program?
Procedure/function (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.

Q18.) 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.
n If the list of values needed to be dynamic and ever changing and define a table based values set.

Q19) What r the validation types in Value Set?
1) None ——– validation is minimal.
2) Independent ——input must exist on previously defined list of values
3) 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
of 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.

Q20) Who information’s?
1) Created by
2) Creation date
3) Last _updated by
4) last_update_date

Thanks  — Shivmohan Purohit

Important things to focus as you are 0 to 2 Year experience in Oracle Applications Technical


Important things to focus as you are 0 to 2 Year experience in Oracle Applications Technical

1.Learn SQL – Writing Queries

2.Be Proficient in PL/SQL Programming

3.Learn more or more on System Admin and Application Developer module

4.Learn Unix – at least basics !

Oracle Reports (Report 6i) – Technical – Interview – Question Answers


Oracle Reports (Report 6i) – Technical – Interview – Question Answers

Question: How many types of columns are there and what are they

Formula columns: For doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable

Question: Can u have more than one layout in report

Answer: It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
Answer: Yes it is possible to run the report without parameter form by setting the PARAM value to Null

Question:  What is the lock option in reports layout

Answer:  By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.
Question: What is Flex

Answer: Flex is the property of moving the related fields together by setting the flex property on

Question: What is the minimum number of groups required for a matrix report

Question: How many different triggers are available in Report?

Answer: There are five types of triggers in report 6i

1) Before report trigger

2) After report trigger

3) Before Parameter trigger

4) After parameter trigger

5) Between pages trigger

Question: What is the Firing sequence of report trigger?

Answer: First the before parameter trigger will raise, after firing this trigger parameter form will displayed, after passing parameter after parameter trigger will fire query will parsed & then before report trigger will fired then if there are number of pages in your report then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire after closing the runtime parameter form is closed.

Question: What is bind variables?

Answer: Bind variables are used in report 6i for replacing the single parameter in the select statement

Question: What is lexical parameter?

Answer: Lexical Parameter is used to replace the where, order by ………conditions at run time.

Question: What are different types of column in reports?

Answer: There are three types of columns in the report 6i these are:

1) Placeholder Column – Placeholder column is used to store a value for a variable.

2) Formula Column

3) Summary Column

Answer: The minimum of groups required for a matrix report are 4

Question: Can u run the report with out a parameter form

Oracle Database related Questions


Here there are very basic still sometime become critical to know concept if you working in Oracle Database related activities. 

Q: What is an Oracle instance?Every running Oracle database is associated with an Oracle instance. When adatabase is started on a database server (regardless of the type of computer),Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.
The Instance and the Database

 
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database. In clustered and massively parallel systems (MPP),the Oracle Parallel Server allows multiple instances to mount a single database. Only the database administrator can start up an instance and open the database.If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains. Security for database startup and shutdown is controlled via connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.

 

 

 

Q: What is a view?

A view is a tailored presentation of the data contained in one or more tables(or other views). Unlike a table, a view is not allocated any storage space, nor does a view actually contain data; rather, a view is defined by a query that extracts or derives data from the tables the view references. These tables are called base tables. Views present a different representation of the data that resides within thebase tables. Views are very powerful because they allow you to tailor the presentation of data to different types of users. Views are often used to:

• provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table

• hide data complexity

• simplify commands for the user

• present the data in a different perspective from that of the base table

• isolate applications from changes in definitions of base tables

• express a query that cannot be expressed without using a view

Q: What is referential integrity?
 

 

Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table.

Q: What is a cursor?A cursor is a private sql work area used to perform manipulations on data using pl\sql, mainly used for multiple row manipulations and locking columns. Data which is populated into the cursor is known as active dataset.

 

Cursors are of two types 1.implicit   2.explicit

Implicit———attributes or properties for implicit cursor

1.sql%is open:attribute returns a boolean value stating wether the cursor is open or closed.

2.sql % found: returns boolean value stating whether the record is found in the cursor.

3.sql%notfound : returns a boolean value stating whether the record is not found in the cursor

4.sql %rowcount :returns a pneumeric value stating no.of rows executed in the cursor.

Explicit cursors—retrives multiple rows, users can perform locks on th data in the cursor attributes-

1.% is open

2.% found

3.% not found

4.% rowcount

WE CAN WRITE TWO CURSORS IN ONE PROGRAM

WE CAN WRITE A CURSOR SPECIFYING PARAMETERS

CURSOR WITH UPDATE CLAUSE IS USED TO PERFORM LOCKS ON DATA.

Q: Why Use Sql* Loader in Oracle Database? 
The Sql Loader utility loads data into an existing ORACLE table from an external files.

 

 

Excel output directly from Oracle Application Concurrent Request Output


Need to create Microsoft Excel style files directly openable from Oracle Applications concurrent request output … without using BI Publisher?

A little know file format with acronym SYKL is a handy tool for create files readable in Microsoft Excel. here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters. So, without further ado, here’s the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters

   1. Take a PL/SQL package based on the Oracle provided OWA SYLK ppckage (owasylk.sql / owa_sylk.sql) and make some changes: ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049 )

  • rename it to owa_sylk_apps
  • Change it to use fnd_file.put_line(fnd_file.output instead of utl_file.put_line(g_file
  • Remove parameters for p_file

   2. Create a PL/SQL package for the concurrent program that outputs SYLK file to the concurrent request output.

create or replace package XXXV8_USERS_SYLK_PKG AUTHID CURRENT_USER

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

);

end XXXV8_USERS_SYLK_PKG;

/

create or replace package body XXXV8_USERS_SYLK_PKG

AS

procedure main

( errbuf      out varchar2

, retcode     out varchar2

, p_date_from in  varchar2

, p_date_to   in  varchar2

) as

  l_date_from date;

  l_date_to   date;

begin

  l_date_from := fnd_date.canonical_to_date(p_date_from);

  l_date_to   := fnd_date.canonical_to_date(p_date_to);

  owa_sylk_apps.show(

        p_query => ‘select user_id user_id, user_name user_name, ‘

                   ‘       description description, creation_date created ‘

                   ‘from fnd_user ‘

                   ‘where trunc(creation_date) >  :DATE_FROM ‘

                   ‘and   trunc(creation_date) <= :DATE_TO ‘,

        p_parm_names =>

                 owa_sylk_apps.owaSylkArray( ‘DATE_FROM’, ‘DATE_TO’),

        p_parm_values =>

                 owa_sylk_apps.owaSylkArray(l_date_from ,l_date_to),

        p_widths =>

                 owa_sylk_apps.owaSylkArray(20,20,20,20)

                 );

end main;

END XXXV8_USERS_SYLK_PKG;

   3. Setup the concurrent program

   4. Hijack one of the little used Viewer Options (PCL) so that we can get the Concurrent Request output browser to automatically open Microsoft Excel. Note this is optional, you can just create a new Viewer Option but then you may get the “Choose Viewer” box when viewing concurrent request output. NB: Navigation path is System Administrator, Install, Viewer Options

update fnd_mime_types_tl

set    mime_type = ‘application/vnd.ms-excel’

,      description = ‘Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language’

,      last_updated_by = 0

,      last_update_date = sysdate

where  file_format_code = ‘PCL’

and    mime_type = ‘application/vnd.hp-PCL’;

commit;

   5. Assign the concurrent program to the appropriate request group (e.g. System Administrator, All Reports, Application Object Library) and run the concurrent program to test it all out! And there you have it – Excel style output direct from concurrent request generated by PL/SQL!

Article courtesy and credit goes to  —  http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html