Some Basic Interview Questions:

1. Report registration?
2. Report Parameters?
3. Sequence of execution of Report Parameters?
4. Difference B/W After parameter and before report Trigger?
5. Difference B/W bind and lexical parameter?
6. How to use Lexical parameter in report?
7. Can the value be passed dynamically?
8. How to design Apps Report from Scratch?
9. Difference B/W Apps and D2K report?
10. What does P_conc_request_id do in the report?
11. User exits in report?
12. SRW FND FORMAT CURRENCY?
13. Where you will use SRW EXIT AND SRW INIT?
14. Which report you find difficult in designing?

FORMS

1. How to design Apps Form from the scratch?
2. How to register the Form?
3. If you don’t want user to see the form name in the menu how will you do that?
4. Differences B/W call form, open form and new form?
5. Difference B/W record group and property class?
6. Types of canvases in forms?
7. Master Item form is based on which canvas?
8. You designed Form on view or table?
9. Advantage of using view or table?
10. How will you use Multi-Org in forms?
11. What changes you will do in desktop when designing the form?
12. When you navigate to a new field what is the sequence of trigger firing?
13.  How will you assign LOV dynamically to an Item?
14. Custom.pll?
15. What customization you made in the forms?

PL/SQL AND APPPS

1. Rating in Pl/Sql?
2. Triggers?
3. How we can use Commit in triggers?

Changes made within triggers should be committed or rolled back as part of the transaction in which they execute.

Thus, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers).

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction. Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements.

Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig

    AFTER insert ON tab1

  DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');

    COMMIT; -- only allowed in autonomous triggers

  END;

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

4. Autonomus Transaction?
5. Mutating Table Error?

A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-4091 error if you have a row trigger that reads or modifies the mutating table.

A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.

For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering off of.

6. How to remove that?

If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions.

For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table.

http://www.orafaq.com/forum/t/58034/0/

7. Exceptions? User defined and Pre defined?
8. Collections?

“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection. Collections are Oracle's version of arrays; collections are single-dimensioned lists

PL/SQL offers these collection types:

·         ·         Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

·         ·         Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

·         ·         Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

9. PL/SQL table?

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogeneous elements, indexed by integers. In technical terms, it is like an array; it is like a SQL table; yet it is not precisely the same as either of those data structures. This chapter explains these characteristics of the PL/SQL table in detail, so that you will understand the differences between PL/SQL tables and traditional arrays and SQL tables, and so that you will know how to use PL/SQL tables in your programs.

1  PROCEDURE set_name (name_in IN VARCHAR2, row_in in INTEGER)

2  IS

3

4     TYPE string_tabletype IS

5        TABLE OF VARCHAR2(30) INDEXED BY BINARY_INTEGER;

6

7     company_name_table string_tabletype;

8

9  BEGIN

10    company_name_table (row_in) := name_in;

11 END;

The TYPE statement in lines 4-5 defines the structure of a PL/SQL table: a collection of strings with a maximum length of 30 characters. The INDEXED BY BINARY_INTEGER clause defines the integer key for the data structure. The table declaration in line 7 declares a specific PL/SQL table based on that table structure. In line 10, traditional array syntax is used to assign a string to a specific row in that PL/SQL table.

10. Record Group?

A record group is an internal Oracle Forms that structure that
hs a column/row framework similar to a database table. However, unlike
database tables, record groups are separate objects that belong to
the form module which they are defined.

11. V Array?

CREATE OR REPLACE TYPE ftx_t (
ftx_code CHAR(8) ,
ftx_code_desc VARCHAR2(32),
primary_ftx_code_ind CHAR(1)
);
rem
rem ftx_v is a VARRAY of 6 elements
rem
CREATE OR REPLACE TYPE ftx_v AS VARRAY(6) OF ftx_t;

12. How to create log file from the procedure? Ans Fnd_file.put_line(Fnd_file.log      ‘sqlcode’)
13. How you have copied data from 10.7 to 11i?
14. From where data was coming?
15. How you load data from flat file to staging table?
16. How you run Sql* loader?
17.  How you run Procedure to load data from staging table to interface table?
18. How you registered the procedure in Apps?
19. What are the mandatory parameters?
20. What do they do?
21. How often you run that procedure?
22. What are the different methods of executions in Concurrent Program?
23. What work you have done on UNIX?
24. How to register shell scripts?
25. What extension we give to shell scripts?
26. How to create Soft link?
27. Where will we Place Shell Script in the oracle Application Directory?
28. What work you have done on the shell script?
29. Utl_File? Why you used that?
30. Exception in Utl_file?
31. How you run the Utl_file?
32. Sql tuning and Package tuning?
33. What is bulk collect?

A bulk collect is a method of fetching data where the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. The larger the number of rows processed, the more performance is gained by using a bulk collect. Bulk collecting data is easy.  First, we define the collection or collections that will be used in the bulk collect.  Next, define the cursor to retrieve the data and finally, bulk collect the data into the collections.  The example below demonstrates a simple bulk collect:

SQL> declare
  2    type number_array is varray(10000) of
                                           number;
  3    type string_array is varray(10000) of
                                    varchar2(100);
  4
  5    a_store string_array;
  6    a_qty   number_array;
  7
  8    cursor c1 is
  9      select store_key, sum(quantity) from sales
 10      group by store_key;
 11  begin
 12    open c1;
 13      fetch c1 bulk collect into a_store, a_qty;
 14    close c1;
 15    for indx in a_store.first..a_store.last loop
 16      dbms_output.put_line(
 17            a_store(indx)||'....'||a_qty(indx));
 18    end loop;
 19  end; /
 

S102....21860
S105....13000
S109....12120
S101....2180
S106....6080
S103....7900
S104....13700
S107....24700
S108....5400
S110....3610

There is nothing new in the above example except line 13.  Here instead of fetching one row, we bulk collect all the rows at once.  Notice that this also allowed us to close the cursor and free the database resources it had obtained immediately after collecting the rows.  The data can now be processed as needed in memory.    In this example, lines 16 and 17 print out the data from the in-memory collection.

Also, note that the last example also used two arrays.  The arrays were loaded together in the one BULK COLLECT INTO  statement.  As the SQL engine loaded the arrays, it places the data at the same index location in each array.  If a store key existed in the table with no orders (not the case in this example), the store key would be place in the a_store array and a null would be placed in the a_qty array.  This allows you to search the store array and retrieve the qty using the store array index.  This is demonstrated in lines 15 through 18 in the example.

In database versions 9iR2 and later, you can bulk collect into records:
 

SQL> declare
  2    type sales_tab is table of sales%rowtype;
  3    t_sal sales_tab;
  4  begin
  5    select * bulk collect into t_sal from sales;
  6    dbms_output.put_line(t_sal.count);
  7  end;
  8  / 

100

While a bulk collect  retrieves data in bulk, the bulk load will change data in bulk

SQL> Declare

  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;

  3    tabrec TYP_TAB_TEST ;

  4    CURSOR C_test is select A, B From TEST ;

  5  Begin

  6     -- Load the collection from the table --

  7     Select A, B BULK COLLECT into tabrec From TEST ;

  8    

  9     -- Insert into the table from the collection --

 10     Forall i in tabrec.first..tabrec.last

 11         Insert into TEST values tabrec(i) ;

 12     

 13     -- Update the table from the collection --

 14     For i in tabrec.first..tabrec.last Loop

 15         tabrec(i).B := tabrec(i).B * 2 ;   

 16     End loop ;

 17    

 18     -- Use of cursor --

 19     Open  C_test ;

 20     Fetch C_test BULK COLLECT Into tabrec ;

 21     Close C_test ;

 22        

 23  End ;

 24  /

34. Analytical Function in PL/SQL?

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

The Syntax

The Syntax of analytic functions is rather straightforward in appearance

Analytic-Function(<Argument>,<Argument>,...)
OVER (
  <Query-Partition-Clause>
  <Order-By-Clause>
  <Windowing-Clause>
)

35. Which APIs you have you used?
36. How will you use API to load data from the flat file to Oracle Base Tables?
37. Which conversion you have done?
38. What are the Mandatory columns in the interface tables?
39. How to register concurrent program from backend?