Workflow-2

Part-2

Oracle Workflows interview Questions

·                  Workflow processes represent business process flows and information routing.

·                  Each workflow process consists of a set of activities and transitions.

·                  Activities can be process activities, notification activities, and function activities.

·                  The business process flow in Oracle Workflow can have decision points (branches), parallel flows, and loops.

·                  The Workflow Builder is a graphical interface for diagramming the workflow process.

·                  The Workflow Engine enforces and executes the workflow process and drives items through the workflow processes.

·                  The Workflow Monitor displays any instances/items and their status within a workflow process.

·                  The Workflow Definitions Loader loads the workflow definition from a flat file or from another database.

·                  Workflow Directory Services obtains the email addresses for roles from the directory repository.

·                  The Notification System sends out messages and solicits responses.

·                  The Workflow Engine is a PL/SQL program that resides on the server and drives items through workflow processes defined in Oracle Workflow.

·                  Each item has a set of attributed values that contains information about the specific item. Each one of these items, with its own set of attribute values in the middle of the workflow process, is referred to as one instance of the workflow process.

·                  Valid states of an activity are Active, Complete, Deferred, Error, Notified, Suspended, and Waiting.

·                  If the state is Error, the changes will roll back to the last savepoint and the error-handling process will be initiated.

·                  Active activities are deferred to the background Workflow Engine if the operating cost is higher than the threshold specified in the Workflow Engine.

·                  If an activity is completed, the Workflow Engine will issue a savepoint and the activities that depend on the completed activity will be set to Active, if appropriate.

·                  If Loop Reset is on and the Workflow Engine comes to an activity that has been executed, then all of the activities will be reversed; otherwise, the transition will be ignored.

·                  Workflow Engine APIs are found in the WF_ENGINE package.

·                  The CreateProcess API creates a new runtime process for an item of an instance.

·                  The StartProcess API begins execution of the workflow process.

·                  The CompleteActivity API notifies the Workflow Engine that an activity has been completed for an item.

·                  GetItemAttrInfo obtains type information about an item attribute.

·                  Oracle Workflow obtains information about a role from three views: WF_USERS, WF_ROLES, and WF_USER_ROLES.

·                  Groups of users, as well as individual users, are assigned to roles.

·                  The Email field in WF_ROLES is used if Expand Role is not checked; otherwise, the Email field of the users in the role will be used.

·                  To add more roles and users, you can use the three local tables: WF_LOCAL_USERS, WF_LOCAL_ROLES, and WF_LOCAL_USER_ROLES.

·                  An item type consists of six components/objects: attributes, processes, notifications, functions, messages, and lookup types.

·                  The internal name of Workflow Builder components must be unique, typed in uppercase, and should not contain spaces.

·                  If there is more than one process attached to an item type, a selector is needed to select the correct process.

·                  Each notification activity must have an attached message.

·                  A message can have its own attributes, and a respond attribute will solicit users to select a valid respond value.

·                  You can attach a time-out to the notification activity. If time passes and a response is not received within the specified time, a <Timeout> transition will be executed.

·                  Lookup types are used to define lists of lookup codes. Result types must be lookup types.

·                  To monitor workflow and to view its progress, you can use either the Workflow Status form in Oracle Applications or the Java-based Workflow Monitor tool.

·                  There are two modes for both forms: ADMIN and USER.

·                  To diagram a workflow process, drag and drop activities into the Process window and draw lines between them by holding down the right mouse button.

·                  Fill in the performer for a notification activity.

·                  The Continue Flow function and Wait for Flow function work together to coordinate the master/detail process flow.

·                  The Oracle Applications Implementation Wizard—wizard for short—is a module in Oracle Applications that guides you through the setup steps of Oracle Applications.

·                  The Oracle Applications Implementation Method (AIM) is a methodology that guides you through every phase of the implementation process and is complemented by the Implementation Wizard.

·                  The process hierarchy ensures that the common setups are finished before the specific setups.

·                  Primary processes are common processes that span the entire application, requiring setup steps that cross multiple modules across the financial, distribution, and manufacturing product families.

·                  Secondary processes are common processes that cross modules within the product family.

·                  Final processes are processes that are specific to an individual module and have no cross-module dependencies. Enter the context for setup steps that are dependent on organization context. Organization contexts are Business Group, Set of Books, Operating Unit, and Inventory Organization. Start the implementation process by launching it through the Implementation Wizard form.

·                  If you are assigned a setup step through the wizard, you will receive notification through Workflow, and you can see the notification in the Notifications Summary form.

·                  You can complete the setup and respond with a Done result.

·                  You can respond with a Skipped result and ask the wizard to skip the setup steps.

·                  You can reassign the setup steps to another role.

·                  You can run the wizard installation process to ensure that the wizard is installed correctly.

Sample Interview Questions

1.      When will an activity be deferred?

A.       Activity cost = 0

B.       Activity cost < Workflow Engine threshold

C.       Activity cost > Workflow Engine threshold

2.      What is the primary purpose of a workflow?

A.       Routes information

B.       Sends notifications

C.       Creates audit trails

D        Defines process rules

3.      What job does the SetItemUserKey API perform?

A.       Sets the unique identifier for an item

B.       Sets the role to be notified

C.       Sets the user-friendly identifier for an item

D.       Sets the activity ID for an item

4.      How will you define the possible results of an activity in order to drive different transitions?

A.       As item attributes for an item

B.       As lookup codes for a lookup type

C.       Separate the activity into two

D.       Use a process activity

5.      Time-out parameters apply to which activity?

A.       Function

B.       Process

C.       Notification

D.       All of the above

6.      What will solicit responses from a notification activity?

A.       Having a time-out parameter

B.       Having a performer

C.       Having a Send message attribute

D.       Having a Respond message attribute

7.      Which Work Engine API should you use to begin execution of an activity?

A.       CreateProcess

B.       StartProcess

C.       ResumeProcess

D.       BeginActivity

8.      Which implementation process group comes before the product family processes?

A.       Common application processes

B.       Product family processes

C.       Product-specific processes

D        Common financial

9.      What is the proper format for an internal name in the Workflow Builder component?

A.       Uppercase

B.       No spaces

C.       Unique within item type

D.       All of the above

10.  Which of the following statements is true?

A.       Role can be created in Workflow Builder.

B.       A user is a role if it has at least one active responsibility.

C.       A role can only have one user.

D.       A user must be a role.

11.  Which component in Oracle Workflow is the graphical interface for workflow processes?

A. Workflow Builder

B.       Workflow Engine

C.       Workflow Monitor

D.       Workflow Definitions Loader

12.  When do you need a selector for an item type?

A.       When you must perform role resolution

B.       When performing a voting activity

C.       When more than one process is associated with an item type

D.       When multiple result codes are possible

13.  What does the Workflow Engine do when a function activity has finished?

A.       Issue a savepoint

B.       Issue a commit

C.       Generate a log file

D.       Notify the user

Answer to the above Questions

1.      C. Activity cost > Workflow Engine threshold

Explanation An activity is deferred when the activity cost is greater than the Workflow Engine threshold. If the activity cost = 0 or if the activity cost is less than the Workflow Engine threshold, the activity will become Active.

2.      A., B., D. Routes information, Sends notifications, Defines process rules

Explanation The primary purposes of a workflow are to route information, send notification, and define process rule. Although some history is maintained automatically by Oracle Workflow, it will not be considered as creating audit trails.

3.      C. Set the user-friendly identifier for an item

Explanation The SetItemUserKey API is used for setting a user-friendly identifier for an item.

4.      B. As lookup codes for a lookup type

Explanation You define the possible results of an activity to drive different transitions using lookup codes for a lookup type. You cannot use item attributes. Separating the activity into two does not make sense since you want to drive two transactions with the same activity, but with two result codes. A process activity will not help in this scenario.

5.      C. Notification

Explanation A time-out parameter only applies to a notification activity since the time-out parameters are used to measure when a notification expires. It does not apply to a function activity or a process activity.

6.      D. Having a Respond message attribute

Explanation Having a Respond message attribute will solicit responses from a notification activity. The display name will become the prompt and the description will be the instruction. Nothing else, such as a time-out parameter, a performer, or a Send message attribute, will have 

an affect in soliciting responses.

7.      B. StartProcess

Explanation The StartProcess API begins the execution of an activity. The CreateProcess API creates a new runtime process for an item. The ResumeProcess API resumes a suspended item. The BeginActivity determines if the specified activity may currently be performed on the item.

8.      A. Common application processes

Explanation Common application processes come before the product family processes, which come before product-specific processes. Common financial is one of the product family processes. It does not come before product family processes.

9.      D. All of the above

Explanation An internal name for a Workflow Builder component must be in uppercase, have no spaces, and be unique within item type.

10.  D. A user must be a role.

Explanation A user must be a role. A role cannot be created in the Workflow Builder. Having one active responsibility is not a requirement for a user to be a role. A role can have more than one user.

11.  A. Workflow Builder

Explanation The Workflow Builder in Oracle Workflow is the graphical interface for workflow processes. The Workflow Engine drives items through workflow processes. The Workflow Monitor allows you to view and monitor workflow process instances and the Workflow Definitions Loader loads workflow definitions from a text file or database.

12.  C. More than one process associated with an item type

Explanation When you have more than one process associated with an item type, you need a selector to select a process. You use the Role Resolution standard activity to perform role resolution. Voting is another standard activity. Multiple result codes drive different transitions, but this is not related to the selector.

13.  A. Issue a savepoint

Explanation The Workflow Engine issues a savepoint when a function activity is completed. The Workflow Engine never issues a commit; the calling application issues a commit. The Workflow Engine does not generate a log file or notify users.

Workflow -1

1. What is workflow and what are the benefits of using Oracle workflow?
Oracle workflow is a graphical tool that allows you to create, track and modify business process, embedded in the oracle database server, it can monitor the workflow activity statuses.
Benefits:
1)      Create a clear business process definition
2)      Automate the business routings
3)      Monitor the process
4)      Allow users to define their own business process to suit their organization needs.
5)      Readily change the business process definitions in case of a change business process


2.what are the steps involved in oracle workflow?
 design & create a Workflow using Oracle Workflow Builder
 start the Oracle Workflow process from pl/sql
 integrate Oracle Workflow with pl/sql for validation and DML etc.
 build Oracle Workflow Notifications
 attach roles/people to notifications in Oracle Workflow

3.  How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode?.
If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won't be processed in online mode.

4. What are the various ways to kick-off a workflow?
You can eiter use wf_engine.start_process or 
you can attach a runnable process such that it subscribes to a workflow event.

5. When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
   a)if initiating the process using start_process, do the below
    wf_engine.threshold := -1;
    wf_engine.createprocess(l_itemtype,l_itemkey,'<YOUR PROCESS NAME>');
    wf_engine.startprocess(l_itemtype, l_itemkey)

   B) When initiating the workflow process through an event subscription, 
    set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.

6. Give me one example where apps uses partitioning in Oracle workflow?
WF_LOCAL_ROLES

7.Can you send blob attachments via workflow notifications?
    Yes, you can send BLOB Attachments. 

8.    When will an activity be deferred in Oracle workflow?   Activity cost > Workflow Engine threshold

activity is deferred when the activity cost is greater than the Workflow Engine threshold. 
    If the activity cost = 0 or if the activity cost is less than the Workflow Engine threshold, the activity will become Active.

9. What is the primary purpose of a oracle workflow?
    Routes information, Sends notifications, Defines process rules    The primary purposes of a workflow are to route information, send notification, and define process rule. 
     Although some history is maintained automatically by Oracle Workflow, it will not be considered as creating audit trails.

10. How will you define the possible results of an activity in order to drive different transitions?

   As lookup codes for a lookup type
 You define the possible results of an activity to drive different transitions using lookup codes for a lookup type. 
    You cannot use item attributes. Separating the activity into two does not make sense since you want to drive 
    two transactions with the same activity, but with two result codes. A process activity will not help in this scenario.

11. Time-out parameters apply to which activity in Oracle workflow?    Notification
A time-out parameter only applies to a notification activity since the time-out parameters are used to measure when a notification expires. 
It does not apply to a function activity or a process activity.

12.What will solicit responses from a notification activity?
Having a Respond message attribute
Having a Respond message attribute will solicit responses from a notification activity. 
    The display name will become the prompt and the description will be the instruction. 
    Nothing else, such as a time-out parameter, a performer, or a Send message attribute, will have 
    an affect in soliciting responses.

13. Which Work Engine API should you use to begin execution of an activity inOracle workflow?
StartProcess: The StartProcess API begins the execution of an activity. The CreateProcess API creates a new runtime process for an item. The ResumeProcess API resumes a suspended item. The BeginActivity determines if the specified activity may currently be performed on the item.

14.  Which implementation process group comes before the product family processes?
    
    Common application processes

    Common application processes come before the product family processes, which come before product-specific processes. 
    Common financial is one of the product family processes. It does not come before product family processes.



15.      What is the proper format for an internal name in the Workflow Builder component?

    An internal name for a Workflow Builder component must be in uppercase, have no spaces, and be unique within item type.



16.  Which of the following statements is true?
    A user must be a role. A role cannot be created in the Workflow Builder. 
    Having one active responsibility is not a requirement for a user to be a role. 
    A role can have more than one user.



17.  Which component in Oracle Workflow is the graphical interface for workflow processes?
    The Workflow Builder in Oracle Workflow is the graphical interface for workflow processes. 
    The Workflow Engine drives items through workflow processes. 
    The Workflow Monitor allows you to view and monitor workflow process instances and 
    the Workflow Definitions Loader loads workflow definitions from a text file or database.




18.  When do you need a selector for an item type?
    When you have more than one process associated with an item type, you need a selector to select a process. 
    You use the Role Resolution standard activity to perform role resolution. 
    Voting is another standard activity. 
    Multiple result codes drive different transitions, but this is not related to the selector.

19.  What does the Workflow Engine do when a function activity has finished?
    The Workflow Engine issues a savepoint when a function activity is completed. 
    The Workflow Engine never issues a commit; the calling application issues a commit. 
    The Workflow Engine does not generate a log file or notify users.

20.      What job does the SetItemUserKey API perform?
    
    Set the user-friendly identifier for an item
    The SetItemUserKey API is used for setting a user-friendly identifier for an item.

PL-SQL Interview Questions Collections

2.	What is the basic structure of PL/SQL?
	PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

5.

What are the datatypes a available in PL/SQL?

Some scalar data types such as
NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.

6.

What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are: I. need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

7.

What is difference between % ROWTYPE and TYPE RECORD ?

% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
e_rec emp% ROWTYPE
Cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.

8.

What is PL/SQL table?

Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

9.

What is a cursor? Why Cursor is required?

Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.

10.

Explain the two types of Cursors?

There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implic

it Cursors for queries. (SQL%NOTFOUND,%FOUND,%ROWCOUNT,%ISOPEN)
User defined cursors are called Explicit Cursors. They can be declared and used.

11.

What are the PL/SQL Statements used in cursor processing?

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO <variable list> or Record types, CLOSE cursor name.

12.

What are the cursor attributes used in PL/SQL?

 %ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows featched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are featched.
% NOT FOUND - to check whether cursor has featched any row. True if no rows are featched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.

13.

What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

14.

What will happen after commit statement ?

Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

15.

Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.

16.

What is a database trigger ? Name some usages of database trigger ?

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

17.

How many types of database triggers can be specified on a table? What are they?

                 Insert             Update             Delete

Before Row                 o.k.                  o.k.                o.k.

After Row                   o.k.                  o.k.                o.k.

Before Statement        o.k.                  o.k.                o.k.

After Statement           o.k.                  o.k.                o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the retruned boolean value.

18.

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

19.

What are two virtual tables available during database trigger execution?

The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.

20.

What happens if a procedure that updates a column of table X is called in a database trigger of the same table?

Mutation of table occurs.

21.

Write the order of precedence for validation of a column in a table ?

I. done using Database triggers.
ii. done using Integarity Constraints.

22.

What is an Exception? What are types of Exception?

Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.

CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

23.

What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

24.

What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

25.

What are the return values of functions SQLCODE and SQLERRM?

SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.

26.

Where the Pre_defined_exceptions are stored?

In the standard package.
Procedures, Functions & Packages;

27.

What is a stored procedure?

A stored procedure is a sequence of statements that perform specific function.

30.

What is difference between a PROCEDURE & FUNCTION?

A FUNCTION is alway returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

31.

What are advantages of Stored Procedures?

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

32.

What are the modes of parameters that can be passed to a procedure?

IN,OUT,IN-OUT parameters.

33.

What are the two parts of a procedure?

Procedure Specification and Procedure Body.

34.

Give the structure of the procedure?

PROCEDURE name (parameter list.....)
is
local variable declarations

BEGIN
Executable statements.
Exception.
exception handlers

end;

35.

Give the structure of the function?

FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

36.

Explain how procedures and functions are called in a PL/SQL block ?

Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

37.

What is Overloading of procedures?

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line

38.

What is a package? What are the advantages of packages?

Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, and Information.
Hiding,. Reusability and Better Performance.

39.

What are two parts of package?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

40.

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

41.

How packaged procedures and functions are called from the following ?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS

a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

42.

Name the tables where characteristics of Package, procedure and functions are stored?

User_objects, User_Source and User_error.

Part-2

·         What’s a PL/SQL table? Its purpose and Advantages?
A PL/SQL table is one dimensional, indexed, unbounded sparsed collection of homogeneous
Data.
PLSQL tables are used to move data into and out of the database and between client side applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete ,next . These attributes make PLSQL tables easier to use and applications easier to maintain.
Advantages:
1 PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
2 Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
3 They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.

·         What is a Cursor? How many types of Cursor are there?
A) Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are
1 Implicit cursors – created automatically by PL/SQL for all SQL Dml statements such as
Insert Update, delete and Select
2 Explicit cursors – Created explicitly. They create a storage area where the set of rows
Returned by a query are placed.
3 Dynamic Cursors – Ref Cursors( used for the runtime modification of the select querry).
Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors.

·         What is the difference between Function and Procedure?
1..Procedure is a sub program written to perform a set of actions and returns multiple valuesUsing out parameters or return no value at all.
2..Function is a subprogram written to perform certain computations and return a single value.

·         What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
1.IN - An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
2. OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
3.INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

·         What is the difference between Truncate and Delete Statement?
1.Truncate – Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback. Truncate command does not use rollback segment during its execution, hence it is fast.
2. Delete – Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.

·         What are Exceptions? How many types of Exceptions are there?
Exceptions are conditions that cause the termination of a block. There are two types of exceptions
1.Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
2.User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.)
Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.

·         What is a Pragma Exception_Init? Explain its usage?
Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking it to associate an exception to the oracle error. There by displaying a specific error message pertaining to the error occurred. Pragma Exception_Init (exception_name, oracle_error_name).

·         What is a Raise and Raise Application Error?
1.Raise statement is used to raise a user defined exception.
2. A raise application error is a procedure belonging to dbms_standard package. It allows to display a user defined error message from a stored subprogram.

·         What is the difference between Package, Procedure and Functions?
1.A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.
2.Procedure is a sub program written to perform a set of actions and can return multiple values.
3.Function is a subprogram written to perform certain computations and return a single value. Unlike subprograms packages cannot be called, passed parameters or nested.

·         How do you make a Function and Procedure as a Private?
Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.

·         How do you kick a Concurrent program from PL/SQL?
Using FND_REQUEST.SUBMIT_REQUEST.

·         What is an Anonymous block?
Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.

·         What are the two basic parameters that we have to pass while registering PL/SQL procedure?
Error code and Error Buffer.

·         How to display messages in Log file and Output file?
Using FND_FILE.PUT_LINE

·         What is a Trigger ? How many types of Triggers are there?
Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the base table of a view are fired if an insert/update/delete statement is issued against a view.
There are two types of triggers, Statement level trigger and Row level trigger.
Insert
After / For each row
Trigger is fired / Update /
Before / For Each statement
Delete

·         Can we use Commit in a Database Trigger, if ‘No’ then why?
No. Committing in a trigger will violate the integrity of the transaction.

·         What is Commit, Rollback and Save point?
Commit – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks.
Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
Rollback – This statement is used to undo work.

·         What is the difference between DDL, DML and DCL structures?
DDL statements are used for defining data. Ex: Create, Alter, Drop,Truncate,Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving.Ex; Commit,Rollback,Savepoint.

·         How can u create a table in PL/SQL procedure?
By using execute immediate statement we can create a table in PLSQL.
Begin
Execute immediate ‘create table amit as select * from emp’;
End;
All DDL,DML,DCL commands can be performed by using this command.

·         How do we Tune the Queries?
Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical analysis about the query using which appropriate actions can be taken.

·         What is Explain Plan? How do u use Explain Plan in TOAD?
It is a utility provided by toad that gives the statistics about the performance of the query. It gives information such as number of full table scans occurred, cost, and usage of indexes

·         What is a TK-PROF and its usage?
Tk-Prof is a utility that reads the trace files and generates more readable data that gives the statistics about the performance of the query on a line to line basis.

·         What is Optimization? How many types of Optimization are there?
Rule based Optimization and Cost Based Optimization.

·         What is the default optimization chosen by Oracle?
Cost based Optimization.

·         What is the difference between the snapshot and synonym?
7 A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
8 A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

·         What is the difference between data types char and varchar?
Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

·         Items are imported from the legacy system using the item import interface using the SRS. How are items imported using the UNIX /PLSQL commands with out using SRS?
1.From the operating system, use CONCSUB to submit a concurrent program. It's an easiest way to test a concurrent program.
Normally, CONCSUB submits a concurrent request and returns control to the OS prompt/shell script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to make CONCSUB wait until the request has completed before returning control to the OS prompt/shell script
By using the WAIT token, the utility checks the request status every 60 seconds and returns to the operating system prompt upon completion of the request. concurrent manager does not abort, shut down, or start up until the concurrent request completes. If your concurrent program is compatible with itself, we can check it for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.
Syntax: CONCSUB [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
To pass null parameters to CONCSUB, use '""' without spaces for each null parameter.
In words: single quote double quote double quote single quote
Following is an example of CONCSUB syntax with null parameters:
CONCSUB oe/oe OE 'Order Entry Super User' JWALSH CONCURRENT XOE XOEPACK 4 3 '""' 3
2. To Invoke a Concurrent Program using PL/SQL:
i) Just insert a row in FND_CONCURRENT_REQUESTS with the apropriate parameters and commit.
ii) Invoke the SUBMIT_REQUEST procedure in FND_REQUEST package.
FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '', FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP', '01-JAN-00', chr(0)

·         How can the duplicate records be deleted from the table?
delete from t1 a where rowid not in (select max(rowid) from t1 b where a.no=b.no)

·         What is the significance of _all tables?
All tables are multi-org tables which are associated with the company as a whole. Multiple Organizations is enabled in Oracle
Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by '_ALL'

·         What are mutating tables? And what is mutating error?
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.
A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating error.

·         What is difference between oracle 7 andoracle 8i?
A) Oracle 7 is a simple RDBMS, where as Oracle 8i is ORDBMS i.e., RDBMS with Object Support.
The main add-ons in version 8 are…
Abstract Data types
Varrays
PL/SQL Tables
Nested Tables
Partitioned Tables

·         What is Data cleaning and testing.
Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.
Data Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables.
Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit.

·         While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can u list them?
It requires two IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps. They are
1. Errcode IN VARCHAR2
2. Errbuff IN VARCHAR2

·         what is a trace file?
when ever an internal error is detected by a process in oracle it dumps the information about the error into a trace file.
Alter session set sql_trace=TRUE

·         When do you use Ref Cursors?
We base a query on a ref cursor when you want to:
1.More easily administer SQL
2. Avoid the use of lexical parameters in your reports
3. Share data sources with other applications, such as Form Builder
4. Increase control and securityv) Encapsulate logic within a subprogram

AOL/J Diagnostic Tests

http://HOST:<PORT>/OA_HTML/jsp/fnd/aoljtest.jsp

-> You can get dbc file content from here.