Interview Question and Answers

SQL and PL/SQL Interview Questions and Answers -1

What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What are the components of a PL/SQL Block?

Declarative part

Executable part

Exception part

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.

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.

What is difference between % ROWTYPE and TYPE RECORD?

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

Explain the two types of Cursors?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.

What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.

% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.

% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.

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.

What is the difference between implicit and explicit cursors?

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join: If comparison comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition.  The outer join operator Plus sign (+) will be included in the join condition.

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

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.

What is an autonomous transaction?

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

What is the difference between View and Materialized view?

Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.

What is dynamic SQL?

Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.

Can you use COMMIT in a trigger?

Yes but by defining an autonomous transaction.

What is the difference between anonymous blocks and stored procedures?

Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.

What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.

Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.

What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.

What is Sequence?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.

What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Explain forward declaration used in functions?

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.

Forward declarations are required in one specific situation: mutual recursion.

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

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.

What is the difference between Truncate and Delete Commands?

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

What is the Purpose of HAVING Clause?

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

What is INLINE View in SQL?

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

While creating a sequence, what does cache and nocache options mean?

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

Does the view exist if the table is dropped from the database?

Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

What types of index data structures can you have?

An index helps to faster search values in tables. The three most commonly used index-types are:

  • B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
  • Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
  • Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.

What is the difference between a “where” clause and a “having” clause?

“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.

Can a view be updated/inserted/deleted? If Yes – under what conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

What is tkprof and how is it used?

The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

What is explain plan and how is it used?

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

What are the Lock types?

Share Lock: It allows the other users for only reading not to insert or update or delete.

Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular objectothers can only read.

Update Lock: Multiple user can read, update delete .

What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.

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

What is Raise_application_error?

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

What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms

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.

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.

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 Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

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.

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.

What is the difference between the snapshot and synonym?

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.

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

Can one call DDL statements from PL/SQL?

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement.

Tell some new features in PL/SQL in 10g?

-Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and   REGEXP_SUBSTR

-Compile time warnings

– Conditional compilation

– Improvement to native compilation

– BINARY_INTEGER made similar to PLS_INTEGER

– Implicit conversion between CLOB and NCLOB

– Improved Overloading

– New datatypes BINARY_FLOAT, BINARY_DOUBLE

– Global optimization enabled

– PLS_INTEGER range increased to 32bit

– DYNAMIC WRAP using DBMS_DDL

What is Overloading in PL/SQL?

Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.

What is a mutating and constraining table?

“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

What is Nested Table?

A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows.  In a sense, it’s a way of storing a one-to many relationship within one table.

What is Varying Array?

A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.

Give some most often used predefined exceptions?

a) NO_DATA_FOUND (Select Statement returns no rows)

b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)

c) INVALID_CURSOR (Illegal cursor operations occurred)

d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)

e) INVALID_NUMBER (Conversion of Character to number fails)

f) ZERO_DIVIDE

g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

Give some important Oracle supplied packages?

DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.

DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.

UTL_FILE: With this package, you can read from & write to Operating system files

UTL_HTTP: This package allows to make HTTP Requests directly from the database.

What is Instead Of Trigger?

This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

What is the Sequence of Firing Database Triggers?

a) Before Row Level Trigger

b) Before Statement Level Trigger

c) After Row Level Trigger

d) Statement Operation

e) After Statement Level Trigger

What is the Difference between PL/SQL Table & Nested Table?

PL/SQL Table: Index by Tables are not Stored in Database.

Nested Table: Nested Tables are Stored in Database as Database Columns.

What is the Difference between Nested Table & Varray?

Nested Tablea) This are Sparse

b) We can Delete its Individual Elements

c) It do not have an Upper Boundary

d) This are Stored in System Generated Table

Varraya) This are Dense

b) We cannot Delete its Elements

c) This are Fixed Size & always need to specify the size

d) These are Stored in Tablespaces

What are the various SQL Statements?

a) Data Retrieval: Select

b) DML: Insert, Update, Delete

c) DDL: Create, Alter, Drop, Rename, Truncate

d) Transaction Control: Commit, Rollback, Savepoint

e) DCL: Grant, Revoke

f) Session Control: Alter Session, Set Role

g) System Control: Alter System

h) Embedded SQL Statements: Open, Close, Fetch & Execute.

What is Rowid?

It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

What is Partitioning?

It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.

They are of following Types:

a) Range Partitioning

b) Hash Partitioning

c) List Partitioning

d) Composite Range-Hash Partitioning

What is a Cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

What is the Difference between Nested Subquery & Correlated Subquery?

Nested Subquerya) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query

b) Outer query is driver by Inner Query

Correlated Subquerya) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query

b) Inner Query is Driven by Outer Query

What is the Difference between Translate & Replace?

Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.

Advertisements

SQL and PL/SQL Interview Questions and Answers -2

What are the various types of queries ?

Answer: The types of queries are:

Normal Queries

Sub Queries

Co-related queries

Nested queries

Compound queries

What is a transaction ?

Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

What is implicit cursor and how is it used by Oracle ?

Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?

Answer: Public synonyms

What is PL/SQL?

Answer: PL/SQL is Oracle’s Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

Is there a PL/SQL Engine in SQL*Plus?

Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Is there a limit on the size of a PL/SQL block?

Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = ‘procedure_name’

Can one read/write files from PL/SQL?

Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=…parameter).

Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.

DECLARE

fileHandler UTL_FILE.FILE_TYPE;

BEGIN

fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’);

UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));

UTL_FILE.FCLOSE(fileHandler);

END;

How can I protect my PL/SQL source code?

Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is:                      wrap name=myscript.sql

oname=xxxx.yyy

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.

Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS

cur integer;

rc integer;

BEGIN

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’,

DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

END;

What are the various types of Exceptions ?

Answer: User defined and Predefined Exceptions.

Can we define exceptions twice in same block ?

Answer: No.

What is the difference between a procedure and a function ?

Answer: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.

Can you have two functions with the same name in a PL/SQL block ?

Answer: Yes.

Can you have two stored functions with the same name ?

Answer: Yes.

Can you call a stored function in the constraint of a table ?

Answer: No.

What are the various types of parameter modes in a procedure ?

Answer: IN, OUT AND INOUT.

What is Over Loading and what are its restrictions ?

Answer: OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.

Can functions be overloaded ?

Answer: Yes.

Can 2 functions have same name & input parameters but differ only by return datatype

Answer: No.

What are the constructs of a procedure, function or a package ?

Answer: The constructs of a procedure, function or a package are :

variables and constants

cursors

exceptions

Why Create or Replace and not Drop and recreate procedures ?

Answer: So that Grants are not dropped.

Can you pass parameters in packages ? How ?

Answer: Yes.You can pass parameters to procedures or functions in a package.

What are the parts of a database trigger ?

Answer: The parts of a trigger are:

A triggering event or statement

A trigger restriction

A trigger action

What are the various types of database triggers ?

Answer: There are 12 types of triggers, they are combination of :

Insert, Delete and Update Triggers.

Before and After Triggers.

Row and Statement Triggers.

What is the advantage of a stored procedure over a database trigger ?

Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What is the maximum no.of statements that can be specified in a trigger statement ?

Answer: One.

Can views be specified in a trigger statement ?

Answer: No

What are the values of :new and :old in Insert/Delete/Update Triggers ?

Answer: INSERT : new = new value, old = NULL

DELETE : new = NULL, old = old value

UPDATE : new = new value, old = old value

What are cascading triggers? What is the maximum no of cascading triggers at a time?

Answer: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

What are mutating triggers ?

Answer: A trigger giving a SELECT on the table on which the trigger is written.

What are constraining triggers ?

Answer: A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

Describe Oracle database’s physical and logical structure ?

Answer:

Physical : Data files, Redo Log files, Control file.

Logical : Tables, Views, Tablespaces, etc.

Can you increase the size of a tablespace ? How ?

Answer: Yes, by adding datafiles to it.

Can you increase the size of datafiles ? How ?

Answer: No (for Oracle 7.0)

Yes (for Oracle 7.3 by using the Resize clause )

What is the use of Control files ?

Answer: Contains pointers to locations of various data files, redo log files, etc.

What is the use of Data Dictionary ?

Answer: It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc

What are the advantages of clusters ?

Answer: Access time reduced for joins.

What are the disadvantages of clusters ?

Answer: The time for Insert increases.

Can Long/Long RAW be clustered ?

Answer: No.

Can null keys be entered in cluster index, normal index ?

Answer: Yes.

Can Check constraint be used for self referential integrity ? How ?

Answer: Yes.In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

What are the min.extents allocated to a rollback extent ?

Answer: Two

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

Answer: The various states of a rollback segment are :

ONLINE

OFFLINE

PARTLY AVAILABLE

NEEDS RECOVERY

INVALID.

What is the difference between unique key and primary key ?

Answer: Unique key can be null; Primary key cannot be null.

An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?

Answer: No.

Can you define multiple savepoints ?

Answer: Yes.

Can you Rollback to any savepoint ?

Answer: Yes.

What is the maximum no.of columns a table can have ?

Answer: 254.

What is the significance of the & and && operators in PL SQL ?

Answer: The & operator means that the PL SQL block requires user input for a variable.The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable

Can you pass a parameter to a cursor ?

Answer: Explicit cursors can take parameters, as the example below shows.A cursor parameter can appear in a query wherever a constant can appear.

CURSOR c1 (median IN NUMBER) IS

SELECT job, ename FROM emp WHERE sal > median;

What are the various types of RollBack Segments ?

Answer: The types of Rollback sagments are as follows :

Public Available to all instances

Private Available to specific instance

Can you use %RowCount as a parameter to a cursor ?

Answer: Yes

Is the query below allowed :

Select sal, ename Into x From emp Where ename = ‘KING’ (Where x is a record of Number(4) and Char(15))

Answer: Yes

Is the assignment given below allowed :

ABC = PQR (Where ABC and PQR are records)

Answer: Yes

Is this for loop allowed : For x in &Start..&End Loop

Answer: Yes

How many rows will the following SQL return : Select * from emp Where rownum < 10;

Answer: 9 rows

How many rows will the following SQL return : Select * from emp Where rownum = 10;

Answer: No rows

Which symbol preceeds the path to the table in the remote database ?

Answer: @

Are views automatically updated when base tables are updated ?

Answer: Yes

Can a trigger written for a view ?

Answer: No

If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?

Answer: Last Record

A table has the following data : [[5, Null, 10]].What will the average function return ?

Answer: 7.5

Is Sysdate a system variable or a system function?

Answer: System Function

Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2.Suppose at this point we issue an rollback and again issue a nextval.What will the output be ?

Answer: 3

Definition of relational DataBase by Dr.Codd (IBM)?

Answer: A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.

What is Multi Threaded Server (MTA) ?

Answer: In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.

Which are initial RDBMS, Hierarchical & N/w database ?

Answer:

RDBMS – R system

Hierarchical – IMS

N/W – DBTG

Difference between Oracle 6 and Oracle 7

Answer:

ORACLE 7                                 ORACLE 6

Cost based optimizer                   Rule based optimizer

Shared SQL Area                        SQL area allocated for each user

Multi Threaded Server                  Single Threaded Server

Hash Clusters                             Only B-Tree indexing

Roll back Size                            Adjustment No provision

Truncate command                      No provision

Distributed Database                   Distributed Query

Table replication & snapshots       No provision

Client/Server Tech                       No provision

What is Functional Dependency?

Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

What is Auditing ?

Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

While designing in client/server what are the 2 imp.things to be considered ?

Answer: Network Overhead (traffic), Speed and Load of client server

What are the disadvantages of SQL ?

Answer: Disadvantages of SQL are :

Cannot drop a field

Cannot rename a field

Cannot manage memory

Procedural Language option not provided

Index on view or index on index not provided

View updation problem

When to create indexes ?

Answer: To be created when table is queried for less than 2% or 4% to 25% of the table rows.

How can you avoid indexes ?

Answer: To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. Use an expression in the Where Clause of the SQL.

What is the result of the following SQL : Select 1 from dual UNION Select ‘A’ from dual;

Answer: Error

Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.

Answer: Yes, database trigger would fire.

Can you alter synonym of view or view ?

Answer: No

Can you create index on view

Answer: No.

What is the difference between a view and a synonym ?

Answer: Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

What’s the length of SQL integer ?

Answer: 32 bit length

What is the difference between foreign key and reference key ?

Answer: Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Can dual table be deleted, dropped or altered or updated or inserted ?

Answer: Yes

If content of dual is updated to some value computation takes place or not ?

Answer: Yes

If any other table same as dual is created would it act similar to dual?

Answer: Yes

For which relational operators in where clause, index is not used ?

Answer: <> , like ‘%…’ is NOT functions, field +constant, field||”

Assume that there are multiple databases running on one machine.How can you switch from one to another ?

Answer: Changing the ORACLE_SID

What are the advantages of Oracle ?

Answer: Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a ‘tuned’ Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.

What is a forward declaration ? What is its use ?

Answer: PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.

What are actual and formal parameters ?

Answer: Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount:

Eg.raise_salary(emp_num, amount);Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase:

Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?

Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.

If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?

Answer: Yes

What are various types of joins ?

Answer: Types of joins are:

Equijoins

Non-equijoins

self join

outer join

What is a package cursor ?

Answer: A package cursor is a cursor which you declare in the package specification without an SQL statement.The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

If you insert a row in a table, then create another table and then say Rollback.In this case will the row be inserted ?

Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.

Oracle Apps interview Questions and Answers -1

1. What is the Diff between APPS Schema and other Schemas?

Apps schema contains only Synonyms we can’t create tables in apps schema,where as other schema s contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we  will connect to apps schema only.

 

2. What is meant by Custom Top and what is the Purpose?

Custom Top is nothing but Customer Top, which is created for customer only. we can have multiple custom

tops based on client requirement. It is used to store developed & customized components. whenever oracle

corp applying patches it will over ride on all the modules except custom top. that’s why we will use custom top.

 

3. What is the Significancy of US Folder?

It is nothing but language specification by default it is in american language. We can have multiple languages folders  based on installed languages. from backend we can get it from

FND_LANGUAGES — COL –INSTALLED_FLAG I,B,D

I–INSTALLED,

B–BASE,

D–DISABLE

select language_code,nls_language from fnd_languages where installed_flag like ‘B’

4. Where did U find the Applcation short name and basepath names?

select basepath,application_short_name from fnd_application from the backend. From the from end we can get it Navigation Application Developer.—–> Application—->Register The application name we will get from FND_APPLICATION_TL

 

5. Where can U find the release version from backend?

SELECT release_name from FND_PRODUCT_GROUPS; —11.5.10.2             .

6.

What are the Folders we will find below the 11.5.0 Folder?

Reports,forms,sql,lib,log,out,bin,admin,html,xml,msg,def, etc

7.

Can we create Tables in the Apps Schema?

No.

8.

Can we have custom schema when it it required?

yes, we can have custom schema, when we want to create a new table we required custom schema.

9.

What is meant by concurrent Program?

It is nothing but Instance of the execution along with parameters & Incompatables. Here Incompatables nothing but if we  are submiting cc programs if any one can be execute in those program , which programs r not imp yet this time we will  mention those programs in incompatables tab.

10.

What are the steps we will follow to register Reports as Concurrent Program?

First develop the report & save it in local machine. upload into custom_top/11.5.0/reports/us/ go to system  administrator  open executable form create executable by mentioning executable method as reports ,executable  as report name which  was created. go to cc program form create ccprogram by attach executable name in executable section. then attach this  ccprogram to request group, Request group to Responsibility.Responsibility to User.

11.

What is meant by Request group?

It is nothing but collection of cc programs.

 

12.

What is Application Top? What are the types and Purpose?

A) When we connect to the server we will find the top called application top. Under application top we have

Product top.

Custom top

Product top is the default top built by the manufacturer. Custom top is used to select the Client for his business purposes. Customizations are done with the Custom top.

13.

What is US folder in the Custom Top?

It is a language specific folder used to store the G.U.I like reports and forms.

14.

What are mandatory parameters of Procedures and what the use of those?

Errorbuf: It is used to returns the error messages and sent it to the log file.

Retcode: It is used to show the status of the Procedure with 0, 1, and 2 0 for Completed Normal

1 for Completed Warning

2 for Completed Error

15

What is Apps Schema and Schema?

Schema: Schema is the location in database contains database objects like views, tables, and synonyms.

Apps Schema: It is used to connect the all schemas to get the information from The database.

16.

What is Token?

a) Use to transfer values to report builder and it is not case sensitive.

17.

Difference between FORM, Function and Menu?

a) A menu is a hierarchical arrangement of functions and menus. Each responsibility has a menu assigned to it. A function is a part of an application that is registered under a unique name for the purpose of assigning it to be including it from a menu.

18.

Tell me something about SQL-LOADER.

 

Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.

Sql* loader supports various load formats, selective loading, and multi-tables loads.

1) Conventional –The conventional path loader essentially loads the data by using standard ‘insert’ statement.

2) Direct — The direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.

EX:- My data.csv file

1001, “scott tiger”,1000,40

1002,”gvreddy”,2345,50

Load data

Infile ‘c:\data\mydata.csv’

insert Into table emp Fields terminated by “,” optionally enclosed by‘”’

(empno, empname,sal,deptno)

>sqlldr scott/tiger@vis control=loader.ctl log= gvlog.log bad=gvbad.bad discard=gvdis.dsc .

19.

What is SET-OF-BOOKS?

 

Collection of Chart of Accounts and Currency and Calendars is called SOB

20.

Tell me what r the Base tables in the AR?

 

 

hz_parties (party_id) (store info about org, groups and people)

HZ_PARTIES stores information about parties such as organizations,

people, and groups, including the identifying address information for the party.

hz_cust_accounts (cust_account_id)

HZ_CUST_ACCOUNTS stores information about customer relationships. If a

party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.

hz_cust_acct_sites_all (cust_acct_site_id)

HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One

customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.

hz_cust_site_uses_all (site_use_id)

HZ_CUST_SITE_USES_ALL stores information about site uses or business

purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.

hz_party_sites (party_site_id)

HZ_PARTY_SITES stores information about the relationship between Parties

and Locations. The same party can have multiple party sites. Physical addresses are stored in HZ_LOCATIONS.

hz_locations (location_id)

HZ_LOCATIONS stores information about physical locations.

hz_Person_Profiles (person_profile_id)

HZ_PERSON_PROFILES stores detail information about people.

hz_Organization_Profiles (organization_profile_id)

HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics,

socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.

21.

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

22.

What is Value Set?

 

 

The value set is a collection (or) container of values.

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

12)What are the validation types?

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.

23.

Form development process?

 

a) Open template form

b) Save as <your form>.fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas (subclass info)

h) Assign canvas property class to the canvas

I) assign the window to the canvas and canvas to the window

j) Create a data block

k) Modify the form level properties. (sub class item Text item)

l) Modify the app_custom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties ((console window, First navigation

p) Save and compile the form.

Place the .fmx in the server directory.

24.

How does u customize the Reports?

 

a. Identify the Short name of the standard report in which module we have

to customize

Ex: – if u wants to customize in the AR module path is

Appl top\ar\11.5.0\reports\US\ .rdf

b. Open the .rdf file in Report builder and change the name of the module.

c. Open the data module and modify the query (what is client requirements)

assign the columns to the attributes.

d. Go to report wizard and select, what r the newly created columns.

e. Then Compile it. Then u will get a .rep file in the specified module. If it is

not in the specified directory then we have to put in the server directory.

f. Then Register in the AOL Concurrent Executable and

Concurrent Program.

g. Go to system administrator Security àResponsibility àrequest.

h. Add and assign a concurrent program to a request group

25.

FLEX FIELDS?

 

Used to capture the additional business information.

DFF

KFF

Additional    Unique Info, Mandatory

Captured in attribute prefixed columns    Segment prefixed

Not reported on standard reports    Is reported on standard reports

To provide expansion space on your form With  the  help of [].

[] Represents descriptive Flex field.

FLEX FILED : DESCRIPTIVE : REGISTER    Used for entering and displaying key information

For example Oracle General uses a key Flex field called Accounting Flex field to uniquely identify a general account.

FLEX FILED : KEY : REGISTER

26.

Difference between Bind and Lexical parameters?

BIND VARIABLE:

are used to replace a single value in sql, pl/sql

bind variable may be used to replace expressions in select, where, group, order

by, having, connect by, start with cause of queries.

bind reference may not be referenced in FROM clause (or) in place of

reserved words or clauses.

LEXICAL REFERENCE:

You can use lexical reference to replace the clauses appearing AFTER select,

from, group by, having, connect by, start with.

You can’t make lexical reference in pl/sql statements.

27.

what is Flex mode and Confine mode?

 

Confine mode:

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders “stretch” when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against

them.

28.

What is Place holder Columns?

 

A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object.

You can set the value of a placeholder column is in a Before Report trigger.

Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

29.

What is Formula Column?

 

A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

30.

What is Summary columns?

 

A summary column performs a computation on another column’s data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional

summaries: first, last, standard deviation, variance.

31.

What is TCA (Trading Community Architecture)?

 

Ans. Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

32.

Difference between Application Developer and System Administrator?

 

Ans.

Role of Technical Consultant:

a. Designing New Forms, Programs and Reports

b. Forms and Reports customization

c. Developing Interfaces

d. Developing PL/SQL stored procedures

e. Workflow automations

Role of System Administrator:

a. Define Logon Users

b. Define New/Custom Responsibility

c. Define Data Groups

d. Define Concurrent Managers

e. Define Printers

f. Test Network Preferences

g. Define/Add new Modules

Role of an Apps DBA:

a. Installing of Application

b. up gradation

c. Migration

d. Patches

e. Routing maintenance of QA

f. Cloning of OA

33.

What are Flex fields?

 

Ans.

Ans. A Flex field is a customizable field that opens in a window from a regular Oracle Applications window. Defining flex fields enables you to tailor Oracle Applications to your own business needs. By using flex fields, you can:

(a) Structure certain identifiers required by oracle applications according to your own business environment.

(b) Collect and display additional information for your business as needed.

Key Flex fields: You use key flex fields to define your own structure for many of the identifiers required by Oracle Applications. Profile – ‘Flexfields:Open Key Window’ (FND_ID_FLEXS)

Descriptive Flex field: You use descriptive flex fields to gather additional information about your business entities beyond the information required by Oracle Applications. Profile – Flex fields: Open Descr Window’ (FND_DESCRIPTIVE_FLEXS)

34.

Report registration process?

 

Ans.

1. Create the report using the report builder.

2. Place the report definition file in the module specific reports directory.

3. Create an executable for the report definition file.

4. Create a concurrent program to that executable.

5. Associate the concurrent program to a request group.

35.

Define Request Group?

 

Ans.

A request security group is the collection of requests, request sets, and concurrent programs that a user, operating under a given responsibility, can select from the Submit Requests window.

36.

Value Sets?

 

Ans.

Oracle Application Object Library uses values, value sets and validation tables as important components of key flex fields, descriptive flex fields, Flex Builder, and Standard Request Submission.

When you first define your flex fields, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values.

You define your value sets first, either before or while you define your flex field

segment structures. You typically define your individual values only after your flex field has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your flex field.

You can share value sets among segments in different flex fields, segments in

different structures of the same flex field, and even segments within the same flex field structure. You can share value sets across key and descriptive flex fields. You can also use value sets for report parameters for your reports that use the Standard Report Submission feature.

Navigation Path:

Login – Application Developer -> Application -> Validation -> Set

37.

Value Validation Types?

 

Ans.

1. Dependant

2. Independent

3. None

4. Pair

5. Special

6. Table

7. Translate Independent

8. Translate Dependent

38.

Incompatibility in report registration and Run Alone?

 

Ans.

Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution.

You can specify your program as being incompatible with itself.

Application: Although the default for this field is the application of your concurrent program, you can enter any valid application name.

Name: The program name and application you specify must uniquely identify a

concurrent program. Your list displays the user-friendly name of the program, the short name, and the description of the program.

Scope: Enter Set or Program Only to specify whether your concurrent program is zincompatible with this program and all its child requests (Set)

or only with this program (Program Only).

Run Alone: Indicate whether your program should run alone relative to all other programs in the same logical database. If the execution of your program

interferes with the execution of all other programs in the same logical database (in other words, if your program is incompatible with all programs in its

logical database, including itself), it should run alone.

 

Oracle interview questions

 

1.

What are the various types of Exceptions ?

User defined and Predefined Exceptions.

2.

Can we define exceptions twice in same block ?

No.

 

3.

What is the difference between a procedure and a function ?

Functions return a single variable by value where as procedures do not return any variable by  value. Rather they return  multiple variables by passing variables by reference through their OUT parameter.

 

4.

Can you have two functions with the same name in a PL/SQL block ?

Yes.

 

5.

Can you have two stored functions with the same name ?

Yes.

 

6.

Can you call a stored function in the constraint of a table ?

No.

 

7.

What are the various types of parameter modes in a procedure ?

IN, OUT AND INOUT.

 

8.

What is Over Loading and what are its restrictions ?

OverLoading means an object performing different functions depending upon the no. of parameters or the data type of  the parameters passed to it.

 

9.

Can functions be overloaded ?

Yes.

10.

Can 2 functions have same name & input parameters but differ only by return datatype

No.

11.

What is Ref Cursor in Oracle ?

By using Ref cursor we can change the select statement

dynamically at runtime which is available in the cursor

definition based on some validation.For this we need to

define ref cursor type by using following syntax.

type <type_name> is ref cursor return <return_type>;

here <type_name> is the name of the ref cursor and

<return_type> will be the return type of ref cursor it may

be %type/%rowtype/record type/plsql table type/nested table

type/varray type.

if <return_type> is mentioned we will called it as strong

ref cursor.

if <return_type> is not mentioned we will called it as weak

ref cursor.

after this we need to define ref cursor variable by using

the following syntax.

v_variable_name ref_cursor_type_name;

then based on validations we can change the ref cursor

dynamically.

for example

——————

declare

type ref_cur is ref cursor;

v_ref_cur ref_cur;

v_deptno emp.deptno%type;

begin

select deptno into v_deptno from emp where empno=7788;

if v_deptno=20 then

open v_ref_cur for select * from emp;

elsif v_deptno=30 then

open v_ref_cur for select * from dept;

else

open v_ref_cur for select * from salgrade;

end if;

end;

What Is a Collection?

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.

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.

‘INBOUND INTERFACES’

Invoice Inbound Interface

Interface Tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

AP_INTERFACE_REJECTIONS (Error Table)

Base Tables:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL (in R12)

AP_INVOICE_DISTRIBUTIONS_ALL

Concurrent Program:

Payables Open Interface Import

Supplier Inbound Interface

Interface tables:

AP_SUPPLIERS_INT

AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT

AP_SUPPLIER_INT_REJECTIONS (Error table for Supplier, Site and Contact inbound based on parent_table column)

Base Tables:

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS

Concurrent programs:

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import

Request Set:

Supplier Open Interface Request Set (Contain all 3 above mentioned concurrent programs)

Item Inbound Interface

Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

MTL_INTERFACE_ERRORS (Error Table)

Base Tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

Concurrent program:

Import Items

Receipt Inbound Interface

Interface Tables:

RCV_TRANSACTIONS_INTERFACE

RCV_HEADERS_INTERFACE

PO_INTERFACE_ERRORS (Error Table)

Base Tables:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_TRANSACTIONS

Concurrent Program:

Receiving Transaction Processor

Purchase Order Inbound Interface

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_LINE_LOCATIONS_INTERFACE

PO_INTERFACE_ERRORS where interface_type = ‘PO_DOCS_OPEN_INTERFACE’ (Error Table)

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

Concurrent Program:

Import Standard Purchase Orders (To import Standard Purchase Orders)

Import Price Catalogs(To import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements)

Requisition Inbound Interface

Interface Tables:

PO_REQUISITIONS_INTERFACE_ALL

PO_REQ_DIST_INTERFACE_ALL

PO_INTERFACE_ERRORS where interface_type =’REQIMPORT’ (Error Table)

Base Tables:

PO_REQUISITIONS_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

Requisition Import

Interview for general …….

*********************************************************………………………….***************************************************************

1) What is SET-OF-BOOKS OR Ledgers?

Collection of Chat of Accounts and Currency and Calendars, Convention is called SOB

2) How can u call a standard interface program from sql or pl/sql code

FND_REQUEST.SUBMIT_REQUEST (PO,EXECUTABLE NAME,,,,PARAMETERS)

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

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.

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

7) What is template

a) The TEMPLATE form is the required starting point for all development of new Forms.

b) The TEMPLATE form includes platform–independent attachments of several Libraries.

APPSCORE :- It contains package and procedures that are required of all forms to support the MENUS ,TOOLBARS.

APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.

FNDSQF :- it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.

CUSTOM :- it allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom ( such as moving to another form and querying up specific records)

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

FlexRpt The execution file is wrnitten using the FlexReport API.

FlexSql The execution file is written using the FlexSql API.

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.

Composite Data types :

PL/SQL TABLES / PL/SQL RECORDS / Nested TABLE / VARRAY

What is the sequence of functions – group by, having , order by in a select statements

Select…..Group by…Having…Order by..

Difference between User and Super User

User : login user or front end user

Super user : it has full access of particular module

11) Oracle E-Business suite

Oracle apps + analytical components software.

(Oracle discover) (Oracle sales analyzer) (Oracle financial analyzer) (Oracle marketing analyzer)

12) 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

13) What is ERP and 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.

14)How many types of Execution Methods

Host

Immediate

Java Stored Pricedure

Oracle reprots

PL/SQL Stored Procedures

Java concurrent Program

SQL * Loader

Sql * plus

spawned

perl concurrent program

request set stage function

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

16 In which tables FF are stored

A) FND – ID – FLEXS

B) FND-ID-FLEX-STRUCTURES 5

17)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 8i 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 standard internet brows

18) 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

19)How to get second parameter value based on first parameter

$FLEX$ value set name.

20)How to call WHO columns into the form

By using FND_STANDARD API‟S

1. FND_STANDARD.FORM_INFO 6

Provides information about the form.

Should be called form when_new_form – instance – instance trigger.

2. FND_standard.set_who

loads WHO columns with proper user information.

Should be called from PRE_UPDTE and PRE_INSERT

Triggers for each block with WHO fields

If this is used FND-GLOBAL need not be called. (FND_GLOBAL.WHO)

3. FND_STANDARD.SYSTEM_DATE

This is a function which returns date.

Behave exactly like SYSDATE built-in.

4. FNID_STANDARD.USER

This is a function which returns varchar2

Behaves exactly like built in USER.

21) APPCORE API’S

APP_COMBO

APP_DATE

APP_EXCEPTION

APP_FIELD

APP_FIND

APP_ITEM

APP_ITEM_PROPERTY

APP_NAVIGATE

APP_RECORD

APP_REGION 7

APP_STANDARD

APP_WINDOW

22)FNDSQF API’S

FND_CURRENCY

FND_DATE

FND_GLOBAL

FND_ORG

FND_STANDARD

FND_UTILITIES.OPEN_URL

FND_UTILITIES. PARAM_EXISTS

23)How to call flex fields in the form

By using FND_FLEX.EVENT (EVENT varchar 2)

How to register an executable and define a concurrent program through backend

By using concurrent processing API’S

1. FND_CONC_GLOBAL.REQUES_DATA

.SET_REQUEST_GLOBALS

2. FND_CONCURRENT.AF_COMMIT

.AF_ROLLBACK

.GET_REQUEST_STATUS

.WAIT_FOR_REQUEST

.SET_COMPLETION_STATUS

3. FND_FILE . PUT

. PUT_LINE

.NEW_NAME 8

.PUT_NAMES

.CLOSE

4. FND-PROGRAM . MESSAGE

. EXECUTABLE

. REGISTER

. PARAMETER

. IN COMPATIBILITY

. EXECUTABLE_EXISTS

5. FND_REQUEST . SET-OPTIONS

.SET_REPEAT_OPTIONS

.SET_PRINT_OPTIONS

.SUBMIT_REQUEST

.SET_MODE

6. FND_REQUEST_INFO . GET_PARAM_NUMBER

. GET_PARAM_INFO

. GET_PROGRAM

. GET_PARAMETER

7. FND_SET . MESSAGE

.ADD_PROGRAM

.ADD_STAGE

.IN COMPATIBILITY

8. FND_SUBMIT . SET_MODE

.SET_REQUEST_STATUS

.SUBMIT_PROGRAM 9

.SUBMIT_SET

FND_PROGRAM.EXECUTABLE

– is used to define a concurent program executable

– it takes 8 parameters ( all are IN mode )

syntax procedure FND_PROGRAM.EXECUTABLE

(executable IN varchar2,

(Full name)

description IN varchar2 default null

execution_method IN varchar2,

execution_file_name IN varchar2 default null,

icon_name IN varchar2 default null,

language_code IN varchar2 default (VS)

FND_PROGRAM.REGISTER

– this procedure no used to define a concument program.

– It has 30 IN paranmeters. Out of which 9 are mandatory, the remaining are default.

(program IN varchar2,

application IN varchar2,

enabled IN varchar2,

short_name IN varchar2,

description IN varchar2, default null, 10

executable_application IN varchar2,

mls_function_shelt_name IN varchar2,

mls_function_application IN varchar2,

inerementor IN varhcar2);

24. How to register a table and columns through back end

by using AD_DD package

– for registering a table

– AD_DD BPI doesn‟t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.

– It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.

– Views need not be registered.

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

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

26)Function FND_REQUEST.SUBMIT_REQUEST

( application in varchar2 default null,

program in varchar2 default null,

description in varchar2 default null,

start-time in varchar2 default null,

sub_request in bookan default False,

argument1,

arguemnt2,

argument 100) return number;

If this is submitted from oracle forms, all the arguments ( 1 to 100 ) must be specified. 12

27. How to submit concurrent programs through OS

– From the operating system the utility .CONCSUB is used to submit is concurrent propgram.

– This is basically used to test a concurrent program .

– By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.

– Concurrent manager doesnot abort, shutdown or start up until the concurrent request completes.

If the concurrent program is compatible with it self, it can be checked for data integrity and dead locks by submitting it many times so that it runs concurrently with it self.

PL/SQL procedures can submit a request to run a program as a concurrent process by calling.

FND_REQUEST. SUBMIT_REQUEST.

Before submitting a request, the following functions also should be called optionally.

FND_REQUEST.SET_OPTIONS

FND_REQUEST.SET_REPEAT_OPTIONS

FND_REQUEST.SET_PRINT_OPTIONS

FND_REQUEST.SET_MODE

28. How to checks the request states

– 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

– This function returns the status of a concurrent request

– If the request is already computed, it also returns the completion message.

– This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic. 13

( request_id in out number,

application in varchar2 default null,

program in varchar2 default null,

phase out varchar2,

status out varchar,

dev_phase out varchar2,

dev_status out varchar2,

message out varchar2) return BOOLEAN;

– when application and program are specified, the request_id of the last request for the specified program should be returned to request_id.

– Phase, and status values should be taken from

FND_LOOKUPS

dev_phase dev_status

pending normal, standby, scheduled, paused

running normal, waiting, resuming, terminating.

Complete normal, Error, warning, cancelled, terminated

Inactive disabled, on-hold, No-manager, supended

– FND_REQUEST.WAIT_FOR_REQUEST

– This function waits for request completion, then returns the request phase/status and completion message to the caller.

– Goes to sleep between checks for request completion.

Syntax

( request_id in number default null,

interval in number default 60,

max_wait in numbe default 0, 14

phase out varchar2,

status out varchar2,

dev_phase out varchar2,

dev_status out varchar2,

message out varchar2) return BOOLEN;

FND_CONCURRENT.SET_COMPLETION_STATUS

– this function should be called from a concurrent program to set its completion states.

– This function returns TRUE on success, other wise FALSE.

ENT.SET_COMPLETION_STATUS

( status in varchar2,

message in varchar2) return BOOLEAN;

normal

status warning

Error

29. What is the reason for not getting any data when a multi org view is quired

– to get the data correctly, the xxx-ALL must be referenced and the ORG_ID value should be specified to extract portioned data.

– Multiorg views are partitioned by using ORG_ID.

– So access through multiorg views will not return any roes, as the CLIENT_INFO Value is not set

– Use HR_OPERATING UNITS to identify the organization _id of the OU on which query is based.

– Use FND_CLIENT_INFO package to set the value in CLIENT INPO using set_org_contest.

– Execute fnd_client_info. Set_org_context („<org_id>‟);

– Now qurying of multiorg views can be done. 15

30. How do you find that muliorg is installed

– multi organization architecture is meant to allow muliple 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)

– if the result is „Y‟ means the database is group for multiorg

31. what are Handlers

Handler is a group of packaged procedures which is used by Oracle Applications to organize . PL/SQL code in forms.

– Handlers provide a way to centralize the code so that it becomes easier to develop, maintain and debug.

– The packaged procedures available in a handler are called form the triggers by passing the name of the trigger as an argument for the procedure to process.

Handlers are types :- 1) Item Handlers

2) Event Handlers

3) Table Handlers

4) Business Rules

– Handlers reside in program units in the form or in stored packed in the database.

32)Adding Table handler Logic

Coding logic for window and alternative region control.

Adding fin-windows and/or ROW-LOV‟S and enable query-find.

Coding logic for item relations such as dependent fields.

Coding messages to use message dictionary. 16

Adding FF logic if required.

Adding choices to the special mence and logic to modify choices the default menu and tool bar behavior is necessary.

Coding any other logic.

Creating a form function for the developed form and registering any sub functions.

Testing the form by it self.

Registering the form with AOL.

Adding the form function to a menu or creating custom mence.

Assigning the menu to the responsibility and assigning the responsibility to the user.

Testing the form within Oracle Applications.

33.what is the differnce between org_id and organization_id

org_id column tables are operating_unit tables,Organization_id tables are inventory Related tables.

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

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

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

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

38. What are steps involved in developing a flex field

– designing the table structure

– creating fields on the form (Visible/Hidden)

– calling appropriate routines

– registration of the flex field.

– Definition of the flex field.

<Flex fields / key/ Register>

<Flex fields/Descriptions / Register>

39. What is an application /Module

– Application is a collection of forms, function and menus

40)FND_PROGRAM Package

FND_PROGRAM.Executable:-

Procedure FND_PROGRAM. Executable IS 23

(executable in Varchar2,

application in varchar2, (full name)

short_name in varchar2, (executable short name)

description in varchar2 default NULL,

execution_method in varchar2,

execution_file_name in varchar2 default null,

Subrowline_name in varchar2 default null, (only for spawned immediate)

Icon_name in varchar2 default null,

Language_code in varchar2 default „US‟,

Execution_file_path in varchar2 default null);

For Java Concurrent Program.

FND. PROGRAM. REGISTER:-

Procedure FND_PROGRAM.Register IS

(Program in varchar2,

application in varchar2,

enabled in varchar2,

short_name in varchar2,

description in varchar2, default null,

executable_short_name in varchar2,

executable_application in varchar2,

execution_options in varchar2, default null,

priority in number default null,

save_output in varchar2 default „Y‟, 24

print in varchar2 dafault „Y‟,

cols in varchar2 default null,

rows in varchar2, default null,

style in varchar2, default null,

style_required in varchar2, default „N‟,

printer in varchar2, default null,

Requets_Type in varchar2, default null,

Request_type_Application in varchar2 default null,

Use_in_Srs in varchar2, default „N‟,

Allow_disabled_valuer in varchar2 default „N‟,

Run_alone in varchar2 default „N‟,

Output_type in varchar2 default „TEXT‟,

Enable_trace in varchar2 default „N‟,

Restart in varchar2 default „Y‟,

nls_complaint in varchar2 default „Y‟,

icon_name in varchar2 default null,

language_code in varchar2, default „US‟,

mls_function_short_name in varchar2 default null,

mls_function_application in varchar2 default null,

incrementor in varchar2 default null);

41) How to submit concurrent program through pl/sql

fnd_request.submit_request(parameters) by using this we can submit the concurrent program thru pl/sql.

FND_GLOBAL.APPS_INITIALIZE (user_id,resp_id, resp_appl_id)

42) What are the types of Concurrent Managers

3 MASTER CONCURRENT MANAGERS:

1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs

2. Standard Manager (SM) : This takes care of report running and batch jobs

3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.

We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

43) multi org set up in pl/sql block

Begin dbms_application_info.set_client_info(‘ORG_ID’);

end;

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

45. What is a function, how to create one

A function is apart of an application‟s functionality that is registered under a unique name for the purpose of assigning to it to, or excluding it from, a menu (and by extension, responsibility). There are several types of functions: – Form Functions, SubFunctions, and

Non-form functions. We often refer to a form function simply as a form.

46. What is meant by APPL_TOP

Environment variable

An operating System variable that describes an aspect of the environment in which your application runs. For example, you can define an environment variable to specify a directory path. 26

$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).

47. Explain briefly where are the Custom.pll, Forms, Reports, Sql Loader Control files, Shell Script source

code and executables files kept

Custom.pll – $AU_TOP/resource

Forms – $PROD_TOP/Forms/US

Reports – $PROD_TOP/Reports/US

SQL Control Files – $PROD_TOP/Bin

Shell Scripts – $PROD_TOP/Bin

48. When is Custom.pll used

Custom.pll is used while making new or customizing standard oraclke forms in apps. It contains all the forms libraries for apps.

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

50. How can you know the form (fmb) name when you open a form in Apps

Help

51. Where do you create a table and sequence in Apps  Is it APPS schema

In custom schema and then grant privileges on it to APPS schema.

52. Where are Views and Procedures created  27

Views: – Views are to be created only in APPS.

Procedures: – In custom schema and the grant it to APPS schema.

53. How can you tell who last updated a particular row in a form

54. You have logged onto a Responsibility, how do you know what operating unit are you on

55) Can new profiles be created  If so how

Yes. Application Developer.

56)How do you register a report  Explain passing of parameters between a concurrent program

Definition and report

After developing the report (.rdf), FTP it to the UNIX server.

Define executable.

Define concurrent program and attach the executable.

Attach the concurrent program to a request group.

57) What is the approach to create a new form to be used in Oracle Apps

The TEMPLATE form is the required starting point of all development of new forms. Start

Developing each new form by copying the TEMPLATE.fmb file, located in

$AU_TOP/forms/US(or your language and platform equivalent), to local directory and

Rename it as appropriate.

58. Explain Value Set  Difference between a Table Validated and Independent Value Set

A set of values against which Oracle Application Object Library validates values your end

users enter when running your program. You define your value set by specifying validation 28

rules, format constraints and other properties. For example, you could define a value set to

contain values that are character strings, validated from a table in your application. You can

Specify that oracle application Object Library use the same value set to validate different

Report parameters. You can also use value sets that you use in your flex fields to validate

Your report parameters.

59. How do you create a table validated value set dependent on another value set

Use :$FLEX$.<Value set name> in the where condition.

60. What is difference between a concurrent request and a concurrent request set

Concurrenct Request means containing only one concurrent program,Concurrent request set containing list of concurrenct programs(group of programs)

61. What are the two mandatory parameters required for running a PL/SQL Procedure based concurrent program

Errbuf, Retcode

62. How can you ensure that only one instance of a concurrent program runs

Check the “Run Alone” check box in Concurrent program registration window.

63. Within a PL/SQL procedure which API is to be used to extract a profile value

FND_PROFILE.GET

64. How do you set the operating unit context in a report

Begin

Dbms_application_info.set_client_info(<Organization_Id>);

End;

65. Can you submit a concurrent request from the operating system directly

Write a Shellscript.

Login to database

Run the function FND_REQUEST.Submit()

66. Explain how to generate a trace file for a pl/sql concurrent program for tuning

Check the‟Enable Trace‟ check box in concurrent program registration window.

67. How do you “write” to the concurrent request Log and Output file

FND_FILE.PUT(FND_FILE.LOG or FND_FILE.OUTPUT, <Text>);

68. What is the difference between Operating Unit and Inventory Organization

Operating Unit :- An Organization that uses Oracle Cash management, Order management and Shipping Execution, Oracle Payables, Oracle Purchasing, and Oracle Receivables. It may be a sales Office, a division, or a dept. An operating unit is associated with a legal entity. Information is secured by operating unit for these applications. Each user sees information only for their operating unit. To run any of these applications, you choose a responsibility associated with an organization classified as an operating unit.

An organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products. Examples, include (but are not limited to) manufacturing plants,  warehouses, distribution centers, and sales offices. The following applications secure information by inventory organization: Oracle inventory, Bills of Material, Engineering, and Work in Process, Master  Scheduling/MRP, Capacity, and Purchasing receiving functions. To run any of these applications, you must choose an organization that has been classified as an inventory organization.

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

70. What is Item Validation Organization

The organization that contains your master list of items. You define it by setting the OM: Item Validation Organization parameter. You must define all items and bills in your Item Validation Organization, but you also need to maintain your items and bills in separate organizations if you want to ship them from other warehouses.

71. Mention the table or views where Inventory Org, Items, Set of Books, GL Code Combinations,

Operating Unit, Location, Customers, Vendors, and Invoices are stored in Apps.

Inventory Org: – MTL_PARAMETERS/ORG_ORGANIZATION_DEFINITIONS

Items: – MTL_SYSTEM_ITEMS_B

Set of Books: – GL_SETS_OF_BOOKS

GL Code Combinations: – GL_CODE_COMBINATIONS

Operating Unit: – HR_ALL_OPERATING_UNITS

Location: – MTL_ITEM_LOCATIONS

Customers: – RA_CUSTOMERS

Vendors: – AP_SUPPLIER_CONTACTS_ALL

Invoices: – AP_INVOICES_ALL

72. What is the profile to be read to find out what Inventory Organization and Operating Unit are you on

(mfg_organization_id is the Inventory Org)

73)What is Inventory Master Organization

Items are defined in an Inventory Master Organization.

74)What is the difference between key flexfield and Descriptive flexfield

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number, a cost centre etc Desc Flex is used to just capture extra information. Key Flexfields have qualifiers whereas Desc Flexfields do not. Desc Flexfields can have context sensitive segments while Key flexfields cannot.

And one more differenct that KFF displays like text item but DFF displays like [ ] .

75)Which procedure should be called to enable a DFF in a form

FND_DESCR_FLEX.DEFINE (BLOCK => ‘BLOCK_NAME’ ,FIELD => ‘FORM_FIELD_NAME’ ,APPL_SHORT_NAME => ‘APP_NAME’ ,DESC_FLEX_NAME => ‘DFF_NAME’ );

76)Which procedure should be used to make the DFF read only at run time

FND_DESCR_FLEX.UPDATE_DEFINITION()

77)What is the difference between flexfield qualifier and segment qualifier

Flexfiled qualifier identifies segement in a flexfield and segment qualifier identifies value in a segment.

There are four types of flexfiled qualifier 1) Balancing segment qualifier 2) cost center 3) natural account and 4) intercompnay

segemtn qualifier :- 1) allow budgeting 2) allow posting 3) account type 4) contral account and 5) reconciliation flag

78)Where do concurrent request logfiles and output files go

The concurrent manager first looks for the environment variable $APPLCSF If this is set, it creates a path using two other environment variables: $APPLLOG and $APPLOUT It places log files in $APPLCSF/$APPLLOG Output files go in $APPLCSF/$APPLOUT So for example, if you have this environment set: $APPLCSF = /u01/appl/common $APPLLOG = log $APPLOUT = out The concurrent manager will place log files in /u01/appl/common/log, and output files in /u01/appl/common/out Note that $APPLCSF must be a full, absolute path, and the other two are directory names. If $APPLCSF is not set, it places the files under the product top of the application associated with the request. So for example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT Logfiles go to: /u01/appl/po/9.0/log Output files to: /u01/appl/po/9.0/out Of course, all these directories must exist and have the correct permissions. Note that all concurrent requests produce a log file, but not necessarily an output file.

79)How do I check if Multi-org is installed

SELECT MULTI_ORG_FLAG FROM FND_PRODUCT_GROUPS

If MULTI_ORG_FLAG is set to ‘Y’, Then its Multi Org.

80)How do I find out what the currently installed release of Applications is  /How do I find the name of a form

We can also find out through Help > About Oracle Applications

81)Why does Help->Tools->Examine ask for a password

Navigate to the Update System Profile Screen.

(\ navigate profile system) 32

– Select Level: Site

– Query up Utilities:Diagnostics in the User Profile Options Zone.

If the profile option Utilities:Diagnostics is set to NO, people with access to the Utilities Menu must enter the password for the ORACLE ID of the current responsibility to use Examine. If set to Yes, a password will not be required.

82)What are the API used in PO cancellation

Ans. For Partial cancellation -> To modify the Ordered quantity

v_return_flag := apps.gems_public_apis_pkg.po_update_po

( x_po_number => v_po_number

, x_release_number => NULL

, x_revision_number => v_revision_num

, x_line_number => v_line_number

, x_shipment_number => v_shipment_num

, new_quantity => p_quantity

, new_price => NULL

, new_promised_date => NULL

, launch_approvals_flag => ‘Y’

, update_source => NULL

, x_interface_type => NULL

, x_transaction_id => NULL

, version => ‘1.0’);

For Full cancellation ->

apps.gems_public_apis_pkg.po_control_document

( p_api_version => v_api_version_number

, p_init_msg_list => apps.fnd_api.g_true

, p_commit => apps.fnd_api.g_false

, x_return_status => p_return_status

, p_doc_type => ‘PO’

, p_doc_subtype => v_sub_type

, p_doc_id => v_po_header_id

, p_doc_num => NULL

, p_release_id => NULL

, p_release_num => NULL

, p_doc_line_id => v_po_line_id

, p_doc_line_num => NULL

, p_doc_line_loc_id => p_line_loc_id

, p_doc_shipment_num => NULL

, p_action => ‘CANCEL’

, p_action_date => SYSDATE

, p_cancel_reason => ‘GPO_WAREHOUSE_DENIAL’

, p_cancel_reqs_flag => ‘N’

, p_print_flag => ‘N’

, p_note_to_vendor => apps.fnd_api.g_miss_char); 33

83)How an API is initialized

Ans. apps.gems_public_apis_pkg.fnd_apps_initialize

( user_id => p_user_id

, resp_id => p_resp_id

, resp_appl_id => p_resp_appl_id)

84)What is the name of the API parameter when they are True,False and NULL

Ans. apps.fnd_api.g_true, apps.fnd_api.g_false and apps.fnd_api.g_miss_char respectively.

85)What are the different steps in sending a mail from PL/SQL

Ans. PROCEDURE glp_send_mail_po_cancel

( p_org_id IN VARCHAR2

, p_feeder_source IN VARCHAR2

, p_subject IN VARCHAR2

, p_message_body IN VARCHAR2

, p_return_status OUT VARCHAR2

, p_error_message OUT VARCHAR2

)

v_host_name := utl_inaddr.get_host_name();

v_host_ip := utl_inaddr.get_host_address(v_host_name);

v_mailconn := utl_smtp.open_connection(v_host_ip, 25);

utl_smtp.helo(v_mailconn,v_host_ip);

utl_smtp.mail(v_mailconn,v_from_email_id);

utl_smtp.rcpt(v_mailconn,v_to_email_tab(v_addr_cnt));

v_message := v_message || ‘To: ‘ || v_to_email_tab(v_addr_cnt) || ‘>’ || crlf;

utl_smtp.data(v_mailconn,v_message); — calling mail procedure

utl_smtp.quit(v_mailconn);

86)How do u call a mail program from Shell program

Ans. for file in `find . -name ” .com ~$5″ -print |cut -c3-120`

do

echo $file

frm=`echo $file | cut -d’~’ -f1`

tom=`echo $file | cut -d’~’ -f2 | sed ‘s/,/ /g’`

echo $frm

echo $tom

echo “Sending mail to $tom”

mailx -r “$frm” -s ‘Order Shipment Confirmation’ “$tom” < “$file”

rc=$

if [ $rc != 0 ]

then

echo ‘invalid file name’

fi 34

rm -f “$file”

rc=$

if [ $rc != 0 ]

then

echo ‘invalid file name’

fi

done

87)How do submit a concurrent program from PL/SQL

Ans. apps.fnd_global.apps_initialize

(user_id => p_user_id

,resp_id => p_resp_id

,resp_appl_id => p_resp_appl_id)

;

/ p_error_message := p_error_message ||’Calling Receiving transaction processor’||chr(10);

v_request_id := apps.fnd_request.submit_request

(‘PO’

,’RVCTP’

,NULL

,NULL

,FALSE

,’BATCH’

,p_batch_id

);

dbms_output.put_line(‘request id is :’||v_request_id); COMMIT;

p_error_message := p_error_message ||’Receiving Transaction Processing Request id :’||v_request_id ||chr(10) ;

IF (v_request_id > 0) THEN

v_complete := FND_CONCURRENT.wait_for_request ( request_id => v_request_id

, interval => 10

, max_wait => 0

, phase => v_phase

, status => v_status

, dev_phase => v_dev_phase 35

, dev_status => v_dev_status

, message => v_message);

88)How do u register a concurrent program from PL/SQL

Ans. apps.fnd_program.executable_exists -> To check if executable file exists

apps.fnd_program.executable -> To make executable file

fnd_program.program_exists -> To check if program is defined

apps.fnd_program.register -> To register/define the program

apps.fnd_program.parameter -> To add parameters

apps.fnd_program.request_group -> To add to a request group

89)How do u initialize an API

Ans. apps.gems_public_apis_pkg.fnd_apps_initialize

( user_id => p_user_id

, resp_id => p_resp_id

, resp_appl_id => p_resp_appl_id)

And U can get the parameters from the following script ->

SELECT DISTINCT f5.user_id

, f4.responsibility_name responsibility_name

, f4.responsibility_id responsibility_id

–INTO

–v_user_id

–, v_responsibility_name

–, v_responsibility_id

FROM applsys.fnd_user_resp_groups f6

, apps.fnd_user f5

, apps.fnd_profile_options f1

, apps.fnd_profile_option_values f2

, apps.fnd_responsibility f3

, apps.fnd_responsibility_tl f4

WHERE SYSDATE BETWEEN f6.start_date AND NVL(f6.end_date,SYSDATE)

AND f5.user_id = f6.user_id

AND UPPER(f5.user_name) like ‘%GLOBALPARTS%’

AND f6.responsibility_id = f4.responsibility_id

AND f2.profile_option_value = TO_CHAR(13) — Putting the ORG ID Value

AND f2.profile_option_id = f1.profile_option_id

AND f1.profile_option_name = ‘ORG_ID’

AND f3.application_id = 201

AND f2.level_value = f3.responsibility_id

AND f3.responsibility_id = f4.responsibility_id

AND UPPER(f4.responsibility_name) LIKE UPPER(‘GEMS%PO%MANAGER%’) 36

AND ROWNUM = 1;

90)How Do u register a table & a column

Ans. EXECUTE ad_dd.register_table( ‘GEMSQA’, ‘gems_qa_iqa_lookup_codes’, ‘T’, 512, 10, 70);

EXECUTE ad_dd.register_column(‘GEMSQA’, ‘gems_qa_iqa_lookup_codes’, ‘LOOKUP_CODE’, 1, ‘VARCHAR2’, 25, ‘N’, ‘N’);

91) What resources are provided for developing applications which will be

integrated into Oracle Applications Release 11

a. The Oracle Applications Developer’s Guide Release 11 and the Oracle

Applications User Interface Standards Release 11.

b. The AU_TOP/forms/US/TEMPLATE.fmb for developing a new form.

c. The AU_TOP/forms/US/APPSTAND.fmb contains standard property classes

for your runtime platform.

d. The AU_TOP/resource/FNDSQF.pll contains routines for Flexfields,

Function Security, User Profiles, Message Dictionary.

e. The AU_TOP/resource/APPCORE.pll contains standard User Interface routines.

f. The AU_TOP/resource/APPDAYPK.pll contains the Calendar Widget routines.

g. The AU_TOP/resource/CUSTOM.pll for adding custom code which affects Oracle

Applications forms without changing Oracle Applications code.

h. The AU_TOP/resource/GLOBE.pll allows Oracle Applications developers to

incorporate global or regional features into Oracle Applications forms

without modifying the base Oracle Applications forms. Globe calls routines

JA, JE, and JL libraries.

i. The AU_TOP/resource/JA.pll called from Globe and contains Asia/Pacific code.

j. The AU_TOP/resource/JE.pll called from Globe and contains EMEA

(Europe/Middle East/Africa) code.

k. The AU_TOP/resource/JL.pll called from Globe and contains Latin America code.

l. The AU_TOP/resource/VERT.pll allows Oracle Applications developers to

incorporate vertical industry features (for automotive, consumer packaged

goods, energy, and other industries) into Oracle Applications forms

without modifying the base Oracle Applications forms. 37

m. Oracle Developer/2000 Server Release 1.6.1.

NOTE: All FMB and PLL files must be migrated to your desktop if you intend to

develop and integrate custom applications into Oracle Applications

Release 11.

92. What are the supported versions of Forms and Reports used for developing

on Oracle Applications Release

Answer——

a. The following supported versions are provided in Developer/2000

Release 1.6.1:

i. Forms 4.5

ii. Reports 2.5

93. How do I compile and/or generate an Oracle Applications form

Answer——

a. UNIX

cd $AU_TOP/forms/US

f45gen module=FNDSCAUS.fmb userid=APPS/APPS output_file=

/appl/v1100000/fnd/11.0.28/forms/US/FNDSCAUS.fmx module_type=form

batch=no compile_all=special

b. Windows NT

cd F:\applr11\au\11.0.28\forms\US

f45gen32 userid=APPS/APPS module=FNDSCAUS.fmb output_file=

applr11\fnd\forms\US\FNDSCAUS.fmx module_type=form batch=no

compile_all=special

94. How do I open, compile and/or generate a custom Oracle Applications form on my desktop

Answer

a. To port the AU_TOP/forms/US and AU_TOP/resource files to your Windows

desktop:

i. Make copies of all required files.

ii. Replicate the AU_TOP directory structure on your desktop.

iii. Move the files to their appropriate AU_TOP/forms/US for FMB and

AU_TOP/resource for PLL.

iv. Include the AU_TOP/forms/US and AU_TOP/resource directories in

your FORMS45_PATH.

v. Open, compile and/or generate forms.

NOTE: The FORMS45_PATH is specified either in your Registry or oracle.ini.

NOTE: It may sometimes be necessary to convert FMB -> FMT and PLL -> PLD before 38

porting from a Unix platform to your desktop.

95. How do I add a CUSTOM_TOP to Oracle Applications

Answer

a. Replicate an existing Oracle Applications product directory structure underneath your APPL_TOP:

APPL_TOP

|

XXCUS_TOP

|

bin——forms—–html—–lib—–log—–mesg—–out—–reports

| |

US US

b. Make sure all the permissions on the files and directories are the same

as the other product directories.

c. Add the full path to this CUSTOM_TOP to your APPLSYS.env ( Source your

APPLSYS.env) or your Windows NT Registry:

UNIX: /u01/oracle/apps/vd11/xxcus/11.0.28

Windows NT: D:\oa\appltst\vd11\xxcus\11.0.28

d. Login to Oracle Applications using the System Administrator or Application

Developer Responsibility.

e. Navigate: Application -> Register and add your new CUSTOM_TOP to Oracle

Applications.

Application Short Name Basepath Description

——————————————————————————-

Custom Application XXCUS XXCUS_TOP Custom Application

f. Shutdown and restart your Internal Concurrent Manager (ICM) so that the

concurrent manager will recognize the change to the environment that was

made to the APPLSYS.env and Registry.

96. How to get the data from the views in Multi_ORG views

A. Using the profiles and client info package.

Eg:

SELECT   FROM po_headers – this is multi org view

If we can‟t get the data with the above query then we have to write a procedure as shown below.

DECLARE

x NUMBER:=0; 39

BEGIN

x:=fnd_profile.value(‘org_id’);

fnd_client_info.set_org_context (204);

END;

Compile and run the query once again.

97) What is Responsibility / Request Group

Ans: Responsibility is used for security reason like which Responsibility can do what type of jobs etc.

Set of Responsibility is attached with a Request group. When we attach the request group to a concurrent program, that can be perform using all the Responsibilities those are attached with Request group.

98) What is DFF

Ans: The Descriptive Flexi field is a field that we can customize to enter additional information for which Oracle Apps product has not provided a field.

Ex. ATP program calculates for those warehouses where Inventory Org Type is DC or Warehouse in DFF Attribute11 of MTL_PARAMETERS table.

99) What is Value Set

Ans: Value Sets define and store the valid items of data, which may be entered into a field.

Key Flexfields, Descriptive Flexfields and many standard fields use Value Sets.

Oracle already comes with hundreds of Value Sets.

We define additional Value Sets to support our own user-defined Key and Descriptive Flexfields (although we may use any existing standard Value Sets if they suit our purpose).

In defining a new Value Set, we are defining the physical format of valid data, which can reside in that Value Set.

Data in a Value Set can be of several types:

Independently loaded into a Value Set (through a standard form).

Resident in a table (to which we direct the Value Set definition).

There could be No Validation (any data can go into the field, but still subject to the formatting rules.)

Dependent on the value of data in a preceding Independent segment (loaded through a standard form).

Ex: For Supplies & Accessories CC in ATP, we define GEMS_GPO_ASSIGN_SET value set for the assignment set associated with that OU.

100) What is multi-org

Ans: It is data security functionality in Oracle 10.6 and above. Applicable User responsibilities are created and attached to specific Operating Unit. User can access the data that belongs to the Operating unit they login under. 40

The benefit is it enables multiple operating units to use a single installation of various modules, while keeping transaction data separate and secure by operating unit.

It has an effect on the following modules:

Order Entry

Receivable

Payable

Purchasing

Project Accounting

101) Can you explain the Organization structure

Ans:

a) Business Unit / Group : It represents the highest level in Organization structure and has no accounting impact. It determines which employees will be available to Set Of Books and Operating Units.

Ex: Consolidated Enterprise / a major division / an operating company.

b) Set Of Books: It is a financial reporting entity that uses a particular

Chart of accounts,

Functional currency and

Accounting calendar.

It is the highest level with accounting significance. Document Sequencing (important in Europe) is at the Set of Books level. Also Period open/close is at the Set of Books level.

A Set of Books is associated with only one Business Group. A Business Group may be assigned several Sets of Books i.e Multiple sets of books can share the same business group if they share the same business group attributes.

Base Table: apps.gl_sets_of_books

c) Legal Entity: A legal company for which you prepare fiscal or tax reports.

Each Legal Entity is associated with only one Set of Books. A Set of Books may have multiple Legal Entities.

Base Table: apps.hr_legal_entities

d) Operating Unit: An organization that uses oracle order management, cash management, shipping execution, payables, purchasing and receivables.

It may be a sales office, a division, or a department. Standard reports are at the Operating Unit level.

An operating unit is associated with a legal entity.

Base Table: apps.hr_operating_units

e) Inventory Organization : An organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products.

An Operating Unit may have multiple Inventory Organizations.

Ex: manufacturing plants, warehouses, distribution centers, and sales offices. 41

Base View: apps.org_organization_definitions

f) Subinventory: An inventory organization has a number of subinventories associated with it.

Base Table: apps.mtl_secondary_inventories (secondary_inventory_name = subinventory name)

g) Locator : These are the different locations in side a subinventory.

Base Table: apps.mtl_item_locations

105)What are the mandatory parameters in Concurrent program

Ans: errbuf and Retcode (In case pl/sql store procedure)

p_conc_request_id (In case Oracle Report, it is an user-parameter)

106)Why we use token field for Concurrent program

Ans: The Token is used as for binding purpose. The parameter value is passed to the .rdf/procedure through this token. The input (user) parameter value passes to the report / stored procedure after binding with this token. The concurrent program won‟t get impact even the user parameter names get changed, but got impacted when the token name changed.

107) What are the mandatory parameters in concurrent programs

Ans: errbuf

errcode.

108)Those are IN or OUT parameters.

Ans: Out Parameters

109)What is Request group

Ans: Responsibility is used for security reason like which Responsibility can do what type of jobs etc.

Set of Responsibility is attached with a Request group. When we attach the request group to a concurrent program, that can be perform using all the Responsibilities those are attached with Request group.

110)What is MultiOrg  42

Ans: It is data security functionality in Oracle 10.6 and above. Applicable User responsibilities are created and attached to specific Operating Unit. User can access the data that belongs to the Operating unit they login under.

The benefit is it enables multiple operating units to use a single installation of various modules, while keeping transaction data separate and secure by operating unit.

It has an effect on the following modules:

Order Entry

Receivable

Payable

Purchasing

Project Accounting

111)There is an Object type “Spawned” in concurrent program. What is the use

Ans: It is used for Proc C in executable field

112)How can we call a Report from Form in Apps

Thru concurrent prog

113) what are the setup we need to do before running into Oracle Apps.

Ans: Go to Oracle Apps > System Administraror (Responsibility) > Create a User using SSO > Add responsibility

Switch to COE Dev Sys Administrator (Responsibility) > Add the Responsibility that is attached to the newly created User > menue > exclude / include the function according to the requirement

114) What is multi_org

Ans: It is data security functionality in Oracle 10.6 and above. Applicable User responsibilities are created and attached to specific Operating Unit. User can access the data that belongs to the Operating unit they login under.

The benefit is it enables multiple operating units to use a single installation of various modules, while keeping transaction data separate and secure by operating unit.

It has an effect on the following modules:

Order Entry

Receivable

Payable

Purchasing

Project Accounting

115) What is the difference between po_headers & po_headers_all

Ans: Here comes the concept of multi_org. po_headers contains data that is irrespective of multi_org i.e any supplier can view all the records. In case of po_headers_all 43

116) What is the basic requirement that we need to set before starting a form in Oracle Apps

Ans:

a) FTP the templet.fmb From $au_top/bin To Local m/c

b) Rename the templet.fmb as User defined name

c) Trigger Level Change: Pre_Form : app_window.set_window_position(‘BLK_ORG’, ‘FIRST_WINDOW’); — 1st Window Name

set_window_property(‘BLK_ORG’,title,’Form name’) — 1st Window Name, caption

Block Level Change: Rename Default_block

Form Level Change: Property Panel > Navigation > First Navigation Data Block > (Set A Block Name)

Program Unit Level: App_custom > (set the 1st window name)

118) What is the API we use to see the message from log file

Ans:

When we use an API, it automatically stores the error message in a pl/sql table i.e creating a log of the errors.

We can see those error messages thougt fnd_message_pub API

119)There is a check box called Use in SRS. What is its use

SRS stands for Standard Request Submit. We can execute concurrent program by directly passing parameters. On checking this „Use In SRS‟ check box, we can independently use the concurrent program.

120)Do you know Extension table

Ans: Unlike database table, it store outside the database like BFile (content store outside the DB but the location store in DB like pointer). It contains metadata only. So no DML operation (except SELECT, Group by, Order By) can possible.

121)Why do we call FND SRWINIT from Before Report Trigger

A. FND SRWINIT fetches concurrent request information and sets up the profile options. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

122)Why do we call FND SRWEXIT from After Report Trigger

A. FND SRWEXIT frees all the memory allocations done in other Oracle Applications user exits. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

123) Why do we call FND FLEXSQL from the Before Report Trigger

A. One need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any description and values from switched value sets as needed. One gets this information by calling the AOL user exit FND FLEXSQL from the before report Trigger. 44

124. If u call the user exit FND FLEXSQL with MODE = “ WHERE” from the Before Report Trigger. What will it do

A. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.

125. If u call the user exit FND FLEXSQL with MODE = “ ORDER BY” from the Before Report Trigger. What will it do

A. This user Exit populates the lexical parameter that one specifies with the appropriate SQL fragment at run time. One includes this lexical parameter in the ORDER BY clause of the report query. This user exit is called once for each lexical to be changed.

126. How can we display flexfield segment values, descriptions, and prompts on the report

A. Create a formula Column. Call the user exit FND FLEXIDVAL as the formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that one does not has to use complicated table joins to the flex field tables.

127. Name some options of the FND FLEXSQL user exit

A CODE, APP_SHORT_NAME, OUTPUT, MODE, DISPLAY, SHOWDEPSEG, NUM or MULTINUM, TABLEALIAS, OPERATOR, OPERAND1, OPERAND2.

128. Describe CODE option of the FND FLEXSQL user exit

A. Specify the flex field code for the report (for example, GL#, MCAT).

129. Describe the APP_SHORT_NAME option of the FND FLEXSQL user exit

A. Specifies the short name of the application that owns the flex field (for example: SQLGL, INV)

130. Describe the OUTPUT option of the FND FLEXSQL user exit

A. Specify the name of the lexical parameter to store the SQl fragment. One uses this lexical later in the report when defining the SQL statement that selects the flexfield values. the datatype of this parameter should be character.

131. Describe the MODE option of the FND FLEXSQL user exit

A. Specify the mode to use to generate the SQL fragment . valid mode are :

SELECT: Retrieves all segments values in an internal (non- displayable format).

WHERE: Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if one specifies MULTINUM. One must also specify an OPERATOR and OPERANDS.

HAVING: Same calling procedures and functionality as WHERE.

ORDER BY: Order required information by flexfield columns. The fragment Orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement, one specifies in MULTINUM.

132. Describe the DISPLAY option of the FND FLEXSQL user exit

A. One uses the DISPLAY token with the MODE token . the DISPLAY parameter allows you to specify segments that represent specified flexfield qualifiers or specified segments numbers , 45

where the segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.

Eg. If your MODE is SELECT and you specify DISPLAY = “ALL” then the SELECT statement includes all the segments of the flexfield. . Similarly, if your MODE is WHERE and you specify DISPLAY = “ALL”, then your WHERE clause includes all segments.

133. Describe the SHOWDEPSEG option of the FND FLEXSQL user exit

A. SHOWDEPSEG = “N” disables automatic addition of depended upon segments to the order criteria. The default is “Y”. This token is valid only for MODE = “ODER BY” In FLEXSQL.

134. Describe the NUM option of the FND FLEXSQL user exit

A. Specify the name or lexical or source column that contains the flexfield structure information. If the flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If the flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. The default value is 101.

135. Describe the TABLE ALIAS option of the FND FLEXSQL user exit

A. You use TABLE ALIAS if your SELECT joins to other flexfield tables or uses a self – join.

136. Describe the OPERATOR option of the FND FLEXSQL user exit

A. Specify an operator to use in the WHERE clause.

137. Describe the OPERAND1 option of the FND FLEXSQL user exit

A. Specify an operand to use in the WHERE clause,

138. Describe the OPERAND2 option of the FND FLEXSQL user exit

A. Specify a second operand to use with OPERATOR = “BETWEEN”

139. Where is FND FLEXIDVAL user exit used

A. Call this user exit to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With this exit you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DECRIPTION<APROMPT or LPROMPT).

140) Name the interface tables used for the customer interface

A. 1. RA_CUSTOMERS_INTERFACE_ALL

2. RA_CUSTOMER_BANKS_INT_ALL

3. RA_CUST_PAY_METHOD_INT_ALL

4. RA_CUSTOMER_PROFILES_INT_ALL

5. RA_CONTACT_PHONES_INT_ALL

141) What is the name of the column in CUSTOMER_INTERFACE_TABLE that indicates whether you

are inserting new or updating existing information

A: When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you are inserting new or updating existing information. This column is required in RA_CUSTOMERS_INTERFACE. 46

142) If the INSERT_UPDATE_FLAG is not set correctly or the required column is missing the value, will CUSTOMER INTERFACE reject the entire record or just the attributes u want to update

A Reject the entire record.

143) List some of the required columns for the RA_CUSTOMERS_INTERFACE

A. ORIG_SYSTEM_CUSTOMER_REF

INSERT_UPDATE_FLAG

CUSTOMER_NAME

CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)

CUSTOMER_STATUS

LAST_UPDATED_BY

LAST_UPDATE_DATE

CREATED_BY

CREATION_DATE

If you are importing an address and a business purpose, you must also populate the following columns:

PRIMARY_SITE_USE_FLAG (if you are inserting an address)

LOCATION (if you are not using Automatic Site Numbering)

SITE_USE_CODE (if you are inserting an address)

ADDRESS1

144) List some of the production tables that Customer Interface transfers customer data from the interface tables into

A. AR_CUSTOMER_PROFILES

AR_CUSTOMER_PROFILE_AMOUNTS

RA_ADDRESSES

RA_CONTACTS

RA_CUSTOMERS

RA_CUSTOMER_RELATIONSHIPS

RA_CUST_RECEIPT_METHODS

RA_PHONES

RA_SITE_USES

AP_BANK_ACCOUNT_USES

AP_BANK_ACCOUNTS

AP_BANK_BRANCHES

145). What validation must be given on the customer_number

A Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.

146) What validation must be given on the CUSTOMER_STATUS

A Must equal ‘A’ for Active or ‟I‟ for Inactive.

147) Name some of the Oracle receivables Interfaces

A a) Auto Invoice

b) Auto Lockbox

c) Customer Interface 47

d) Sales Tax rate Interface

e) Tax Vendor Extension

148) Give some of the Oracle Payables interface

A. a) Credit Card Transaction Interface

b) Invoice Import Interface

c) Payables Open Interface

d) Purchase Order Matching

149). Name some of the oracle general ledger Interface

A a) Budget Upload

b) Importing Journals

c) Loading Daily rates

150). What are the names of the parameters u pass to the Procedure which u register in the apps

A. 1) retcode in varchar2

2) errbuf in varchar2

151). What is the use of Auto lock Box

A Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing.

152). Auto Lockbox is a three-step process, what are those

A. a) Import

b) Validation

c) PostQuickCash

153). What is the order in which Autolock box searches for the types of the matching number

A. 1. Transaction Number

2. Sales Order Number

3. Purchase Order Number

4. Consolidated Billing Invoice Number

5. Other, user-defined number.

154. What is application short name for General Ledger you specify in FND FLEXSQL user exit

A. SQLGL

155) . What are validations to be done in Journal Import interface.

A. Batch level: Set of Books, Period Name, and Batch Name

Journal Level: Set of books, Period name, Source name, Journal entry name, Currency code, Category name, Actual flag, Encumbrance type ID, User conversion type, Accounting date, Budget version ID

156) What subclass in forms6i

A Specifies module, storage & name information about the source object and source module for a referenced objects.

157) What is the clause in SQL   Loader to program to override data into table

A. REPLACE

158). How do you set profile in oracle applications In Application Developer responsibility

A Open “Profile” Function

159). What is the syntax for loading data through SQL   Loader from multiple files simultaneously

A. Sqlldr scott/tiger@orcl control = ctlfile

parfile — parameter file: name of file that contains parameter specifications

parallel — do parallel load (Default FALSE)

160) What is the table name for items in Oracle Inventory

A MTL_SYSTEM_ITEMS, MTL_CATEGORIES

161). Tell me names of important production tables & their purpose AP, AR, GL, PO

A AP: AP_INVOICES_ALL, AP_INVOICE_LINES_ALL

To store invoices

AR: RA_SHIPMENT_HEADERS/ _LINES, RA_CUSTOMERS, RA_CONTACTS

PO: PO_VENDORS, PO_VENDOR_SITES – For storing vendor data.

162). Name the interface tables used for the LockBox Interface

A Interface table : AR_PAYMENTS_INTERFACE_ALL

Lockbox transfers the receipts that pass validation to the Receivables interim tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL

When you run Post QuickCash, the receipt data is transferred from the QuickCash tables to the following Receipt tables:

AR_CASH_RECEIPTS_ALL

AR_RECEIVABLES_APPLICATIONS_ALL

AR_CASH_RECEIPT_HISTORY_ALL

163) Name the interface tables used for the Auto Invoice Interface.

A Auto Invoice transfers transaction data from the interface tables

RA_INTERFACE_LINES_ALL,

RA_INTERFACE_SALESCREDITS_ALL, and

RA_INTERFACE_DISTRIBUTIONS_ALL

into the following Receivables tables:

• RA_BATCHES_ALL

• RA_CUSTOMER_TRX _ALL

• RA_CUSTOMER_TRX_LINES _ALL

• RA_CUST_TRX_LINE_GL_DIST_ALL

• RA_CUST_TRX_LINE_SALESREPS_ALL

• AR_PAYMENT_SCHEDULES_ALL

• AR_RECEIVABLE_APPLICATIONS_ALL 49

• AR_ADJUSTMENTS_ALL

164). Different Type of Value Sets. What is exactly Translatable independent and Translatable Dependent Value Sets (Introduced in latest version of 11i).

Ans:- There are 8 types of Values Sets.

a. None (Non Validate at all) (Validation is Minimal)

b. Independent (Input must exist on previous defined list of values)

c. Dependent (Input is checked against a subset of values based on prior Value)

d. Table (Input is checked against a subset of values in an application table)

e. Special (advanced) (Value set uses a flexfield itself)

f. Pair (advanced) (Two Flexfields together specify a range of valid values)

g. Translatable Independent (Input must exist on previous defined list of values. Translated value can be used)

h. Translatable Dependent. (Input is checked against a subset of values based on a prior value; translated value can be used)

(Note:- When you first define your flexfields, you choose how many segments

You want to use and what order you want them to appear. You also

Choose how you want to validate each of your segments. The decisions

You make affect how you define your value sets and your values.)

165) How to run a concurrent program. What all concurrent programs u have

created.

Ans:- (Definition :- A concurrent program is an instance of an execution file, along with

parameter definitions and incompatibilities. Concurrent programs use concurrent program executables to locate the correct execution file.)

Oracle Tool Concurrent Program   A concurrent program written in

Oracle Reports, PL/SQL package procedures,

SQL Loader, SQL Plus, Host Scripting.

How to Run :   Write a execution file and place in correct directory.

Establish executables in Oracle apps specify execution file and method.

Define Concurrent Program (Program, Parameters and Incompatibilities)

Call your Program (- Thu application form, from other concurrent program.

– OR through standard request submission, you must check the „USE in SRS check box‟ and register your program parameters when you define your concurrent program. Add your program into the request security group for your custom application.)

I have created reports through concurrent program, load(sql loader/pl-sql pkg-proc) the file through concurrent program.

166) What is parameter in apps and from where u can create it.

Ans:- Parameters only using in report, you can create in defining the report in

apps (you can create the parameter there only).

167)What all are the tables used in the modules u have worked on.

In GL – I have worked on GL_JE_HEADERS(JOURNALS HEADER),GL_JE_LINES(JOURNAL LINES), GL_JE_BACHES(JOURNAL BATCHES), GL_SET_OF_BOOK(SET_OF_BOOK_ID),

In PO- I have worked on PO_HEADER_ALL…..

168)What is Profile  Explain different levels of Profile.

Ans:- A user profile is a set of changeable options that affects the way your

applications run. Oracle Application Object Library establishes a value

for each option in a user‟s profile when the user logs on or changes 50

responsibility. Your user can change the value of profile options at any

time a) To create Profile Option. ( Profile Option can created by developer in application developer area) b)set the value (Values of the profile option , who will have what value at various levels is set by SYSADMIN). Oracle Application Object Library provides many options that. (Edit profile feature for every user is available to set any value to allow the user).your users can set to alter the user interface of your applications to satisfy their individual preferences.

Profile Option – set at run time like – User Related, responsibility, Sequence, Printer, Security.

Values in 4 Levels(HIEARCHY WISE) :-

A. USER

B. RESPONSIBILITY

C. APPLICATION

D. SITE

Application Developer create the profile.

System Administrator make profile option.

(NOTE:- If any change in value, it will active when you re-login or switch

to the responsibility.)

( Usage in the multi-tier, the profile is biggest impact)

169)How to restrict the data for a responsibility as per the ORG-ID

Ans:-Through Multi – Org(MO) u can restrict the data for a responsibility as per the ORG-ID. Only in GL- Set of book Id you set the value to restrict the data for a responsibility.

170) What is Flexfield  What is flexfield qualifier and what is segment qualifier

Ans:- A flexfield is made up a segments (Which are actually table columns). Each segment has a name that can be assigned, and set of valid value.

Purpose and Application:-

Flexibility to implement code structure.

Flexibility to capture additional information.

Two Types of Flexfields in oracle apps.

1. Key Flexfields (KFF)

2. Descriptive Flexfields (DFF)

A key flexfield segment has a name you assign, and set of valid values you specify. Each value has a meaning which can be specified.

Flexfield Qualifier:-A flexfield qualifier identifies a particular segment of a key flexfield..

Usually an application needs some method of identifying a particular

segment for some application purpose such as security or computations. However, since a key flexfield can be customized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or segment order to

use for segment identification.

Segment Qualifier :- A segment qualifier identifies a particular type of value in a single

segment of a key flexfield.

In the Oracle Applications, only the. Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an ”identification tag” for a value. In the Accounting Flexfield, segment qualifiers can identify the account type

171) Which flexfield qualifiers are mandatory

Ans:- “Balancing Segment” flexfield qualifier is mandatory.

172) Difference Between versions of Apps.(Front end & Database)

Ans:- In backend- Client server architecture (old)/ Three tire architecture

In font end- Client Server Application (old)/ Web Based application

173)What is MULTI-ORG and what is structure of multi-org.

Ans:- Use a single installation of any oracle applications product to support any number of organizations. if those organizations use different set of books.

Support any number or legal entities with a single installation of oracle applications.

Secure access to data so that users can access only the information that is relevant to them.

Structure :- Business Unit

-HRMS(Employee)

-GL(Set of Books)(Currency, Calendar, Chart of Account)

|

Balancing Segment(You can do multiple balancing segment)

-Operating Units (Purchase, Selling, Fixed Asset, Payable,

Receivables)

-Inventory Organizations (Storing Items, Transaction Happening,

Ware Housing)

(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you

have inventory then its called MULTI-ORG)

174)What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org.

At where we can set ORG_ID and ORGANIZATION_ID level it comes in the

structure.

Ans:-A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID(or ORG_ID) for the multiple organization support feature.

Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable.(It comes in AP,PO,AR,OM level)

ORGANIZATION_ID – Its for Inventory, Mfg, & BOM.

175)ORG_ID can be set at master levels or transaction level.

Ans:- ORG_ID can be set at transaction Level.

176)Differnet type of execution methods in Conc.Progs. Explain Each Type.

Ans:- a.Oracle Reports- You can register your report as executable file type is oracle reports.

b. PL/SQL Package Procedure – You can register your PL/SQL Package Procedure as executable file type is oracle PL/SQL Package Procedure.

1. SQL Loader- You can register your SQL Loader SQL Loader is your executable file type.(for data loading)

2. SQL Plus :- You can register your SQL script as SQL Plus executable type.

3. Host Scripting:- You can write down Unix Host scripting and register here.

177) What is difference between oracle schema and apps schema.

Ans:-Database Schema-

The APPS schema- is an ORACLE schema that has access to the

complete Oracle Applications data model. This schema is maintained

by Auto Install .

178)What are the objects APPS schema contain. 52

Ans:- The APPS schema contains synonyms to all tables and

sequences as well as all server–side code (stored procedures, views,

and database triggers).

For ERP applications, data partitioning is performed by database

views. These views reside in the APPS Oracle schema and derive the

appropriate operating unit context from an RDBMS variable.

179)When will a New version of flint60 be released

flint60 is a developer/development tool. Since flint60 can change at any time, the most current flint60 will always obsolete all prior releases. In other words, the development standards implemented in the most current flint60 are the standards to which everyone using flint60 must adhere.

1. How do I register a custom concurrent program

Step 1: Register a concurrent program executable Navigate to the Define Executable form (AOL Reference manual pg 9-84) This determines the type of program being run, ie an Oracle Report, a C program, a shell script etc. Fill in the executable name, application and execution method. For the Execution File, fill in just the filename. The concurrent manager will look in the appropriate directory under the application’s top directory. For spawned programs, the file must be in the bin directory, for Oracle Reports the rdf file must be in the srw directory. For PLSQL concurrent programs, put the name of the stored procedure.

Step 2: Define the concurrent program Navigate to the Define Concurrent Program form (AOL Reference manual pg 9-87) This form links a concurrent program to the executable you just defined, as well as defines the programs parameters, incompatibilities, and other options. Enter the concurrent program name, application, short name and description. Check Standard Submission if you want to be able to submit this program from the Standard Report Submission form. Enter the name of the executable you defined and any report information if necessary. Also define any parameters your program needs here and any incompatibilities.

Step 3: Add the concurrent program to a Report Group First you will need to find the name of the Report Group to use. Go to Security->Responsibility and query the responsibility you want to run the program with. It should show a Report Group name. Query this name in Security->Responsibility->Report Add your new program to the list of available programs. Now when you go to submit a request with this responsibility, you will be able to submit your custom program

180)How do I compile a custom C program

Spawned programs:

Step 1: Write the code Self-explanatory

Step 2: Compile the source You must use the makefile under $FND_TOP/usrxit Use: make -f $FND_TOP/usrxit/Makefile program.o We do not support using any other makefile

Step 3: Link the program This part is a little tricky. You need to create a custom makefile for this step. Use $FND_TOP/lib/sample.mk as a starting point. Copy this file to the lib directory under your applications top directory. Rename it <short name>.mk (ie fnd.mk, gl.mk etc) Modify this file according to the directions in it. Basically you need to 53

add a target and build commands for your executable. Next, use adrelink to link the executable: adrelink force=y ranlib=y ‘shortname programname’

Step 4: Register the program as in the above question

Immediate programs: Just don’t do it.

181)How do I run a shell script as a concurrent program

1: Write the script and call it <name>.prog Place the script under the bin directory under your applications top directory. For example, call the script CUSTOM.prog and place it under $CUSTOM_TOP/bin

bin

2: Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called CUSTOM.prog use this: ln -s $FND_TOP/bin/fndcpesr CUSTOM This link should be named the same as your script without the .prog extension It should be in the same directory as the script.

3: Register a concurrent program as described above, using an execution method of ‘Host’ Use the name of your script without the .prog extension as the name of the executable For the example above, you would use CUSTOM CUSTOM

4: Your script will be passed at least 4 parameters, in $1 through $4 These will be: orauser/pwd, userid, username, request_id Any other parameters you define will be passed in $5 and higher. Make sure your script returns an exit status.

define will be passed in $5 and higher. Make sure your script returns an exit status.

5: If your script returns a failure exit status but the concurrent manager does not report the error (shows it as still running normal) apply patch 442824

182)How will u register RDF file and run it  Tell the Sequence

Steps a. Save the copy of ur reports in rdf file in ur local directory.

b. Transfer or copy the rdf file to cus_top under reports directory through ftp.

C. Then go concurrent program under executable menu where u define executable file and program name

d. Then go to define the program name (which ur executable file name ) and check the srs box and define the parameter and give the parameter name in token

e. Attach the program(request to ur responsibility )

d run the program and view the out put is srs through ur responsibility

What are different types of value sets

183)What is translatable Independent & Dependent

The value set used to support the multilingual value set.

185))How do I submit a concurrent request from PL/SQL

ans : using fnd_request.submit_request .

begin

v_request_id := fnd_request.submit_request(applicationshortname,

concurrentprogramshortname,

description,

paramers)

end

commit;

if v_request_id > 0 then

dbms_output.put_line(‘Successfully submitted’)

else

dbms_output.put_line(‘Not Submitted’);

end;

note : to submit a conc program from UNIX/shell scrip we use CONSUB

186) How do I cancel a running concurrent request

Navigate to the Concurrent Request Summary form Select a request The Sysadmin responsibility can cancel or hold any running request

187) What is the difference between organization id and org_id

Organization_id stores inventory organization id ( like 204 for M1)

Org_id stores the OU id corresponding to a operating unit .

188) What is the difference between conversion and interfaces

conversion means one time activity interface means periodic activity

example:- to transfer the data old version to new version it is called conversionto transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ……..

189) What are the different types of value sets and also explain each briefly

Different types of Value sets are,

1) Independent- This Value set contains list of values which does not depends on any other value

2) Dependant- It contains values which depends on any one of the Independant value

3) Pair- combines 2 flex field together to specify range of valid values

4) Special- Uses only 1 flex field structure to specify values

5) Table- This Value set contains list of values from 1 or more than 1 table columns

6) Translatable Dependant- Same as Dependant value set, only translated values are present

7) Translatable Independant- Same as Independant value set, only translated values are present

190) How do you register a table and columns in Oracle Apps>

To register the table and columns in AOL the navigation is: Open Appliaction Developer—> Appliaction—>Database—>table.(In table mention the table name(which you want to register), user table name,columns,user column name). The table & columns which you are going to register should be present in your module specific schema

195) What can we find TEMPLATE.FMB file

$AU_TOP/forms/US

Template.fmb file can be found in AU_TOP resource directory. This file contains all the Common characterstics all the forms. And also Contains Diffrent libraries. like CUSTOM.pll,APPCORE,APPCOREE2,FNDSQF, JE,JL,JA,VERT,GLOBE etc.. And Template.fmb cotains Diffrent propery classes for all the objects. This Template.fmb can be used for developing the new form 55

196) What are the libraries attached to TEMPLATE form

The Template form required 19 .pll in 11i version. Those pll names are :

APPCORE.pll APPCORE2.pll FNDSQF.pll APPDAYPK.pll GLOBE.pll JE.pll JL.pll JA.pll VERT.pll GHR.pll PQH_GEN.pll PSAC.pll PSB.pll PSA.pll IGILUTIL.pll

IGILUTIL2.pll CUSTOM.pll GMS.pll FV.pll OPM.pll

197)What is Concurrent Programming

Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with on line operations to fully utilize your hardware capacity.

Use Concurrent Programming for

Long Running – Data intensive tasks such as Posting a Journal or generating a report.

198)What is the Role of Concurrent Managers

A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.

199)What is AOL

Oracle Applications are constructed and maintained using the Application Object Library (AOL).

The Three main areas of AOL are

o Applications Security

o Operating Profile

o Concurrent Processing

1. What is the Flex field  What are the types of Flex field

o Flex Field is “Flexible Field”

o A Flexfield is made up of Segments.

o Each segment has a name that can be assigned and has set of valid values.

o There are two types of Flex field Key Flex Field and Descriptive Flex Fields.

1. What are the tables related to flex field

o FND_FLEX_VALUES

o FND_FLEX_VALUE_SETS

o FND_FLEX_VALUES_TL

1. What is AD_DD package

AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.

PROCEDURE REGISTER_TABLE

Arguments:

o P_APPL_SHORT_NAME

o P_TAB_NAME

o P_TAB_TYPE

o P_NEXT_EXTENT

o P_PCT_FREE

o P_PCT_USED

PROCEDURE REGISTER_COLUMN

Arguments

P_APPL_SHORT_NAME

P_TAB_NAME

P_COL_NAME

P_COL_SEQ

P_COL_TYPE

P_COL_WIDTH

P_NULLABLE

P_TRANSLATE

P_PRECISION

P_SCALE

***********************************************************……………………………..***********************************************************

224) What is parameter in apps and from where u can create it. Ans:- Parameters only using in report, you can create in defining the report in

apps (you can create the parameter there only).

225)What all are the tables used in the modules u have worked on.  In GL – I have worked on GL_JE_HEADERS(JOURNALS HEADER),GL_JE_LINES(JOURNAL LINES), GL_JE_BACHES(JOURNAL BATCHES), GL_SET_OF_BOOK(SET_OF_BOOK_ID),

In PO- I have worked on PO_HEADER_ALL…..

226)What is Profile  Explain different levels of Profile. Ans:- A user profile is a set of changeable options that affects the way your

applications run. Oracle Application Object Library establishes a value

for each option in a user‟s profile when the user logs on or changes

responsibility. Your user can change the value of profile options at any

time a) To create Profile Option. ( Profile Option can created by developer in application developer area) b)set the value (Values of the profile option , who will have what value at various levels is set by SYSADMIN). Oracle Application Object Library provides many options that. (Edit profile feature for every user is available to set any value to allow the user).your users can set to alter the user interface of your applications to satisfy their individual preferences. Profile Option – set at run time like – User Related, responsibility, Sequence, Printer, Security.

61 Values in 4 Levels(HIEARCHY WISE) :- E. USER

F. RESPONSIBILITY

G. APPLICATION

H. SITE

Application Developer create the profile. System Administrator make profile option. (NOTE:- If any change in value, it will active when you re-login or switch to the responsibility.)

( Usage in the multi-tier, the profile is biggest impact)

227)How to restrict the data for a responsibility as per the ORG-ID Ans:-Through Multi – Org(MO) u can restrict the data for a responsibility as per the ORG-ID. Only in GL- Set of book Id you set the value to restrict the data for a responsibility.

228) is Flexfield  What is flexfield qualifier and what is segment qualifier  Ans:- A flexfield is made up a segments (Which are actually table columns). Each segment has a name that can be assigned, and set of valid value.

Purpose and Application:-

Flexibility to implement code structure.

Flexibility to capture additional information.

Two Types of Flexfields in oracle apps.

1. Key Flexfields (KFF)

2. Descriptive Flexfields (DFF)

A key flexfield segment has a name you assign, and set of valid values you specify. Each value has a meaning which can be specified. Flexfield Qualifier:-A flexfield qualifier identifies a particular segment of a key flexfield.. Usually an application needs some method of identifying a particular

segment for some application purpose such as security or computations. However, since a key flexfield can be customized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or segment order to

use for segment identification.

Segment Qualifier :- A segment qualifier identifies a particular type of value in a single segment of a key flexfield.

In the Oracle Applications, only the. Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an identification tag for a value. In the Accounting Flexfield, segment qualifiers can identify the account type

229)Which flexfield qualifiers are mandatory  Ans:- „Balancing Segment‟ flex field qualifier is mandatory.

230)Difference Between versions of Apps.(Front end & Database) Ans:- In backend- Client server architecture (old)/ Three tire architecture

In font end- Client Server Application (old)/ Web Based application

231)What is MULTI-ORG and what is structure of multi-org. Ans:- Use a single installation of any oracle applications product to support any number of organizations. if those organizations use different set of books.

Support any number or legal entities with a single installation of oracle applications. 62 Secure access to data so that users can access only the information that is relevant to them. Structure :- Business Unit -HRMS(Employee)

-GL(Set of Books)(Currency, Calendar, Chart of Account)

|

Balancing Segment(You can do multiple balancing segment)

-Operating Units (Purchase, Selling, Fixed Asset, Payable,

Receivables)

-Inventory Organizations (Storing Items, Transaction Happening,

Ware Housing)

(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you

have inventory then its called MULTI-ORG)

232)What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org.

At where we can set ORG_ID and ORGANIZATION_ID level it comes in the

structure. Ans:-A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID(or ORG_ID) for the multiple organization support feature.

Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable.(It comes in AP,PO,AR,OM level)

ORGANIZATION_ID – Its for Inventory, Mfg, & BOM.

233)ORG_ID can be set at master levels or transaction level. Ans:- ORG_ID can be set at transaction Level.

234)Differnet type of execution methods in Conc.Progs. Explain Each Type. Ans:- a.Oracle Reports- You can register your report as executable file type is oracle reports.

b. PL/SQL Package Procedure – You can register your PL/SQL Package Procedure as executable file type is oracle PL/SQL Package Procedure.

1. SQL Loader- You can register your SQL Loader SQL Loader is your executable file type.(for data loading)

2. SQL Plus :- You can register your SQL script as SQL Plus executable type.

3. Host Scripting:- You can write down Unix Host scripting and register here.

235)What is difference between oracle schema and apps schema. Ans:-Database Schema-

The APPS schema- is an ORACLE schema that has access to the

complete Oracle Applications data model. This schema is maintained

by AutoInstall.

236) What are the objects APPS schema contain. Ans:- The APPS schema contains synonyms to all tables and

sequences as well as all server–side code (stored procedures, views,

and database triggers).

For ERP applications, data partitioning is performed by database

views. These views reside in the APPS Oracle schema and derive the 63 appropriate operating unit context from an RDBMS variable.

237)What are the names of the parameters u pass to the Procedure which u register in the apps  B. 1) retcode in varchar2

2) errbuf in varchar2

238) What is application short name for General Ledger you specify in FND FLEXSQL user exit  A. SQLGL

239)How do you set profile in oracle applications In Application Developer responsibility  A Open “Profile” Function

240) What is the syntax for loading data through SQL   Loader from multiple files simultaneously A. Sqlldr scott/tiger@orcl control = ctlfile

parfile — parameter file: name of file that contains parameter specifications

parallel — do parallel load (Default FALSE)

241)Give the relation between categories and items table. MTL_SYSTEM_ITEMS

MTL_CATEGORIES_B

Relation INVENTORY_ITEM_ID

242)In which tables are the categories of an item stored. MTL_CATEGORIES_B

MTL_ITEM_CATEGORIES

243)Significance of ALL in apps tables. Tables which are related with Multiorg is suffixed with ALL.

244)Explain about flexfield in GL.In what way it is useful  A KFF is a set of segments, each segment will identify a unique characterstic of an entity. it can be termed as intelligent key. we can define our business requirements without doing any programming.

A DFF is a set of segments which can be used to capture extra info. without any customisation.

245)Execution methods

Ans. a. Host b. Immediate

c. Java Stored Procedure

d. Java Concurrent Program

e. Multi Language Function

f. Oracle Reports

64 g. PL/SQL Stored Procedure

h. Request Set Stage Function

i. Spawned

j. SQL Loader

k. SQL Plus

246) What is TCA (Trading Community Architecture)

Ans. Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

247) Difference between Application Developer and System Administrator  Role of Technical Consultant:

1. Designing New Forms, Programs and Reports

2. Forms and Reports customization

3. Developing Interfaces

4. Developing PL/SQL stored procedures

5. Workflow automations

Role of System Administrator:

1. Define Logon Users

2. Define New/Custom Responsibility

3. Define Data Groups

4. Define Concurrent Managers

5. Define Printers

6. Test Network Preferences

7. Define/Add new Modules

Role of an Apps DBA:

1. Installing of Application

2. Upgradation

3. Migration

4. Patches

5. Routing maintenance of QA

6. Cloning of OA

248)What are Flexfields

Ans. A Flexfield is a customizable field that opens in a window from a regular Oracle Applications window. Defining flexfields enables you to tailor Oracle Applications to your own business needs. By using flexfields, you can: (a) Structure certain identifiers required by oracle applications according to your own business environment.

(b) Collect and display additional information for your business as needed.

Key Flexfields: You use key flexfields to define your own structure for many of the identifiers required by Oracle Applications. Profile – „Flexfields:Open Key Window‟ (FND_ID_FLEXS)

Descriptive Flexfield: You use descriptive flexfields to gather additional information about your business entities beyong the information required by Oracle Applications. Profile – Flexfields: Open Descr Window‟ (FND_DESCRIPTIVE_FLEXS) 249) Request Set and where do you define it

Ans. Request sets allow you to submit multiple requests together using multiple execution path. A request set is a collection of reports and /or programs that are grouped together. A stage is a component of a request set used to group requests within the set. All of the requests in a given stage are executed in parallel. Advantages of stages are the ability to execute several requests in parallel and then move sequentially to the next stage.

Responsibility: System Administrator

Nav: Concurrent -> Set

249) Which flexfield qualifiers are mandatory

Ans:- Balancing Segment flexfield qualifier is mandatory.

250) Difference Between versions of Apps.(Front end & Database)

Ans:- In backend- Client server architecture (old)/ Three tire architecture

In font end- Client Server Application (old)/ Web Based application

173)What is MULTI-ORG and what is structure of multi-org.

Ans:- Use a single installation of any oracle applications product to support any number of organizations. if those organizations use different set of books.

Support any number or legal entities with a single installation of oracle applications.

Secure access to data so that users can access only the information that is relevant to them.

Structure :- Business Unit

-HRMS(Employee)

-GL(Set of Books)(Currency, Calendar, Chart of Account)

|

Balancing Segment(You can do multiple balancing segment)

-Operating Units (Purchase, Selling, Fixed Asset, Payable,

Receivables)

-Inventory Organizations (Storing Items, Transaction Happening,

Ware Housing)

(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you

have inventory then its called MULTI-ORG)

174)What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org.

At where we can set ORG_ID and ORGANIZATION_ID level it comes in the

structure.

Ans:-A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID(or ORG_ID) for the multiple organization support feature.

Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable.(It comes in AP,PO,AR,OM level)

ORGANIZATION_ID – Its for Inventory, Mfg, & BOM.

15.Q.What are the default types of parameters. What is the use of each one of it.

Ans:-

175)ORG_ID can be set at master levels or transaction level.

Ans:- ORG_ID can be set at transaction Level.

176)Differnet type of execution methods in Conc.Progs. Explain Each Type.

Ans:- a.Oracle Reports- You can register your report as executable file type is oracle reports.

b. PL/SQL Package Procedure – You can register your PL/SQL Package Procedure as executable file type is oracle PL/SQL Package Procedure.

1. SQL Loader- You can register your SQL Loader SQL Loader is your executable file type.(for data loading)

2. SQL Plus :- You can register your SQL script as SQL Plus executable type.

3. Host Scripting:- You can write down Unix Host scripting and register here.

177) What is difference between oracle schema and apps schema.

Ans:-Database Schema-

The APPS schema- is an ORACLE schema that has access to the

complete Oracle Applications data model. This schema is maintained

by Auto Install .

178)What are the objects APPS schema contain. 52

Ans:- The APPS schema contains synonyms to all tables and

sequences as well as all server–side code (stored procedures, views,

and database triggers).

For ERP applications, data partitioning is performed by database

views. These views reside in the APPS Oracle schema and derive the

appropriate operating unit context from an RDBMS variable.

179)When will a New version of flint60 be released

flint60 is a developer/development tool. Since flint60 can change at any time, the most current flint60 will always obsolete all prior releases. In other words, the development standards implemented in the most current flint60 are the standards to which everyone using flint60 must adhere.

1. How do I register a custom concurrent program

Step 1: Register a concurrent program executable Navigate to the Define Executable form (AOL Reference manual pg 9-84) This determines the type of program being run, ie an Oracle Report, a C program, a shell script etc. Fill in the executable name, application and execution method. For the Execution File, fill in just the filename. The concurrent manager will look in the appropriate directory under the applications top directory. For spawned programs, the file must be in the bin directory, for Oracle Reports the rdf file must be in the srw directory. For PLSQL concurrent programs, put the name of the stored procedure.

Step 2: Define the concurrent program Navigate to the Define Concurrent Program form (AOL Reference manual pg 9-87) This form links a concurrent program to the executable you just defined, as well as defines the programs parameters, incompatibilities, and other options. Enter the concurrent program name, application, short name and description. Check Standard Submission if you want to be able to submit this program from the Standard Report Submission form. Enter the name of the executable you defined and any report information if necessary. Also define any parameters your program needs here and any incompatibilities.

Step 3: Add the concurrent program to a Report Group First you will need to find the name of the Report Group to use. Go to Security->Responsibility and query the responsibility you want to run the program with. It should show a Report Group name. Query this name in Security->Responsibility->Report Add your new program to the list of available programs. Now when you go to submit a request with this responsibility, you will be able to submit your custom program

180)How do I compile a custom C program

Spawned programs:

Step 1: Write the code Self-explanatory

Step 2: Compile the source You must use the makefile under $FND_TOP/usrxit Use: make -f $FND_TOP/usrxit/Makefile program.o We do not support using any other makefile

Step 3: Link the program This part is a little tricky. You need to create a custom makefile for this step. Use $FND_TOP/lib/sample.mk as a starting point. Copy this file to the lib directory under your applications top directory. Rename it <short name>.mk (ie fnd.mk, gl.mk etc) Modify this file according to the directions in it. Basically you need to 53

add a target and build commands for your executable. Next, use adrelink to link the executable: adrelink force=y ranlib=y shortname programname

Step 4: Register the program as in the above question

Immediate programs: Just dont do it.

181)How do I run a shell script as a concurrent program

1: Write the script and call it <name>.prog Place the script under the bin directory under your applications top directory. For example, call the script CUSTOM.prog and place it under $CUSTOM_TOP/bin

bin

2: Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called CUSTOM.prog use this: ln -s $FND_TOP/bin/fndcpesr CUSTOM This link should be named the same as your script without the .prog extension It should be in the same directory as the script.

3: Register a concurrent program as described above, using an execution method of Host Use the name of your script without the .prog extension as the name of the executable For the example above, you would use CUSTOM CUSTOM

4: Your script will be passed at least 4 parameters, in $1 through $4 These will be: orauser/pwd, userid, username, request_id Any other parameters you define will be passed in $5 and higher. Make sure your script returns an exit status.

define will be passed in $5 and higher. Make sure your script returns an exit status.

5: If your script returns a failure exit status but the concurrent manager does not report the error (shows it as still running normal) apply patch 442824

182)How will u register RDF file and run it  Tell the Sequence

Steps a. Save the copy of ur reports in rdf file in ur local directory.

b. Transfer or copy the rdf file to cus_top under reports directory through ftp.

C. Then go concurrent program under executable menu where u define executable file and program name

d. Then go to define the program name (which ur executable file name ) and check the srs box and define the parameter and give the parameter name in token

e. Attach the program(request to ur responsibility )

d run the program and view the out put is srs through ur responsibility

What are different types of value sets

183)What is translatable Independent & Dependent

The value set used to support the multilingual value set.

185))How do I submit a concurrent request from PL/SQL

ans : using fnd_request.submit_request .

begin

v_request_id := fnd_request.submit_request(applicationshortname,

concurrentprogramshortname,

description,

paramers)

end 54

commit;

if v_request_id > 0 then

dbms_output.put_line(‘Successfully submitted’)

else

dbms_output.put_line(‘Not Submitted’);

end;

note : to submit a conc program from UNIX/shell scrip we use CONSUB

186) How do I cancel a running concurrent request

Navigate to the Concurrent Request Summary form Select a request The Sysadmin responsibility can cancel or hold any running request

187) What is the difference between organization id and org_id

Organization_id stores inventory organization id ( like 204 for M1)

Org_id stores the OU id corresponding to a operating unit .

188) What is the difference between conversion and interfaces

conversion means one time activity interface means periodic activity

example:- to transfer the data old version to new version it is called conversionto transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ……..

189) What are the different types of value sets and also explain each briefly

Different types of Value sets are,

1) Independent- This Value set contains list of values which does not depends on any other value

2) Dependant- It contains values which depends on any one of the Independant value

3) Pair- combines 2 flex field together to specify range of valid values

4) Special- Uses only 1 flex field structure to specify values

5) Table- This Value set contains list of values from 1 or more than 1 table columns

6) Translatable Dependant- Same as Dependant value set, only translated values are present

7) Translatable Independant- Same as Independant value set, only translated values are present

190) How do you register a table and columns in Oracle Apps>

To register the table and columns in AOL the navigation is: Open Appliaction Developer—> Appliaction—>Database—>table.(In table mention the table name(which you want to register), user table name,columns,user column name). The table & columns which you are going to register should be present in your module specific schema

195) What can we find TEMPLATE.FMB file

$AU_TOP/forms/US

Template.fmb file can be found in AU_TOP resource directory. This file contains all the Common characterstics all the forms. And also Contains Diffrent libraries. like CUSTOM.pll,APPCORE,APPCOREE2,FNDSQF, JE,JL,JA,VERT,GLOBE etc.. And Template.fmb cotains Diffrent propery classes for all the objects. This Template.fmb can be used for developing the new form 55

196) What are the libraries attached to TEMPLATE form

The Template form required 19 .pll in 11i version. Those pll names are :

APPCORE.pll APPCORE2.pll FNDSQF.pll APPDAYPK.pll GLOBE.pll JE.pll JL.pll JA.pll VERT.pll GHR.pll PQH_GEN.pll PSAC.pll PSB.pll PSA.pll IGILUTIL.pll

IGILUTIL2.pll CUSTOM.pll GMS.pll FV.pll OPM.pll

197)What is Concurrent Programming

Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with on line operations to fully utilize your hardware capacity.

Use Concurrent Programming for

Long Running – Data intensive tasks such as Posting a Journal or generating a report.

198)What is the Role of Concurrent Managers

A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.

199)What is AOL

Oracle Applications are constructed and maintained using the Application Object Library (AOL).

The Three main areas of AOL are

o Applications Security

o Operating Profile

o Concurrent Processing

1. What is the Flex field  What are the types of Flex field

o Flex Field is “Flexible Field”

o A Flexfield is made up of Segments.

o Each segment has a name that can be assigned and has set of valid values.

o There are two types of Flex field Key Flex Field and Descriptive Flex Fields.

56

1. What are the tables related to flex field

o FND_FLEX_VALUES

o FND_FLEX_VALUE_SETS

o FND_FLEX_VALUES_TL

1. What is AD_DD package

AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.

PROCEDURE REGISTER_TABLE

Arguments:

o P_APPL_SHORT_NAME

o P_TAB_NAME

o P_TAB_TYPE

o P_NEXT_EXTENT

o P_PCT_FREE

o P_PCT_USED

PROCEDURE REGISTER_COLUMN

Arguments

P_APPL_SHORT_NAME

P_TAB_NAME

P_COL_NAME

P_COL_SEQ

P_COL_TYPE

P_COL_WIDTH

P_NULLABLE

P_TRANSLATE

P_PRECISION

P_SCALE

1. What are the Special and Pair Flex Field

Special – Value Sets uses FlexField itself

Pair – Two Flex Fields together specifies a range of valid values.

1. What are the Translatable Dependent and Independent Flex Fields

Translatable Independent – Input must exist on previously defined set List of

Values. Translated value can be used. 57

Translatable Dependent means Input is checked against a subset of values

Based on a prior value. Translated value can be used.

1. What is FND_REQUEST.SUBMIT_REQUEST

Submits a Concurrent Request for Processing by a Concurrent Manager.

Arguments – Application,program,description,start_time,sub_request,arg1..

1. What is Client Info

By calling this Program in SQL PLUS or reports with correct parameters user can achieve concurrent program environment for testing.

FND_CLIENT_INFO.setup_client_info(application_id Number,

Responsibility_id Number,

User_id Number,

Security_Group_id Number);

Workflow interview questions and answers -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 in Oracle 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.




All the best…….

PL/SQL Multiple choice Interview Question and answer

1. Examine this procedure: CREATE OR REPLACE PROCEDURE

DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID =

V_ID EXCEPTION WHEN STATS_EXI TS_EXCEPTI ON THEN DBMS_OUTPUT.

PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table);END;

What prevents this procedure from being created successfully?

A. A comma has been left after the STATS_EXIST_EXCEPTION exception.

B. The STATS_EXIST_EXCEPTION has not been declared as a number.

C. The STATS_EXIST_EXCEPTION has not been declared as an exception.

D. Only predefined exceptions are allowed in the EXCEPTION section.

Answer  C

2. Under which two circumstances do you design database triggers? (Choose two)

A. To duplicate the functionality of other triggers.

B. To replicate built-in constraints in the Oracle server such as primary key and foreign key.

C. To guarantee that when a specific operation is performed, related actions are performed.

D. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.

Answer C, D

3. Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure

A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TI MESTAMP. What happens when procedure A is invoked at 1 P.M?

A. There is no affect on procedure A and it runs successfully.

B. Procedure B is invalidated and recompiles when invoked.

C. Procedure A is invalidated and recompiles for the first time it is invoked.

D. Procedure A is invalidated and recompiles for the second time it is invoked.

Answer  D

4. This statement fails when executed:

CREATE OR REPLACE TRI GGER CALC_TEAM_AVG

AFTER I NSERT ON PLAYER

BEGIN

INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS)

VALUES ( : NEW. I D, 1 997, 0, 0) ;

END;

To which type must you convert the trigger to correct the error?

A. Row

B. Statement

C. ORACLE FORM trigger

D. Before

Answer  A

5. An internal LOB is _____.

A. A table.

B. A column that is a primary key.

C. Stored in the database.

D. A file stored outside of the database, with an internal pointer to it from a database column.

Answer   C

6. You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a

SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?

A. The trigger fires successfully.

B. The trigger fails because it needs to be a row level AFTER UPDATE trigger.

C. The trigger fails because a SELECT statement on the table being updated is not allowed.

D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

Answer   C

7. You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database.

What type of trigger needs to be created?

A. DML trigger

B. System event trigger

C. INSTEAD OF trigger

D. Application trigger

Answer  B

8. Examine this code:

CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER)

IS v_dept_id NUMBER(4);

BEGIN INSERT INTO departments

VALUES (5, .Education ., 150, p_location_id);

SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999;

END insert_dept; /

CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2)

IS BEGIN

INSERT INTO locations(location_id, city)

VALUES (p_location_id, p_city);

insert_dept(p_location_id);

END insert_location; /

You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, .San Francisco .) What is the result in this EXECUTE command?

A. The locations, departments, and employees tables are empty.

B. The departments table has one row. The locations and the employees tables are empty.

C. The location table has one row. The departments and the employees tables are empty.

D. The locations table and the departments table both have one row. The employees table is empty.

Answer  A

9. The OLD and NEW qualifiers can be used in which type of trigger?

A. Row level DML trigger

B. Row level system trigger

C. Statement level DML trigger

D. Row level application trigger

E. Statement level system trigger

F. Statement level application trigger

Answer  A

10. Which view displays indirect dependencies, indenting each dependency?

A. DEPTREE

B. IDEPTREE

C. INDENT_TREE

D. I_DEPT_TREE

Answer  B

12. Examine this code:

CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS

BEGIN INSERT INTO audit(schema_user) VALUES(p_who);

END audit_action; /

CREATE OR REPLACE TRIGGER watch_it

AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) /

What does this trigger do?

A. The trigger records an audit trail when a user makes changes to the database.

B. The trigger marks the user as logged on to the database before an audit statement is issued.

C. The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.

D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.

Answer  D

13. Examine this procedure:

CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS

BEGIN

UPDATE PLAYER_BAT_STAT

SET AT_BATS = AT_BATS + V_AB

WHERE PLAYER_ID = V_ID;

COMMIT;

END;

Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

A. EXECUTE UPD_BAT_STAT;

B. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

C. EXECUTE UPD_BAT_STAT(31, ‘FOUR’, ‘TWO’);

D. UPD_BAT_STAT(V_AB=>10, V_ID=>31);

E. RUN UPD_BAT_STAT;

Answer  A, B

14. Examine this code:

CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2,

p_last_name VARCHAR2, p_id NUMBER)

RETURN VARCHAR2 IS

v_email_name VARCHAR2(19);

BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) ||

.@Oracle.com .;

UPDATE employees SET email = v_email_name

WHERE employee_id = p_id; RETURN v_email_name;

END;

Which statement removes the function?

A. DROP gen_email_name;

B. REMOVE gen_email_name;

C. DELETE gen_email_name;

D. DROP FUNCTION gen_email_name;

Answer  D

15. Examine this code:

CREATE OR REPLACE PACKAGE comm_package IS

g_comm NUMBER := 10;

PROCEDURE reset_comm(p_comm IN NUMBER);

END comm_package; /

User Jones executes the following code at 9:01am:

EXECUTE comm_package.g_comm := 15

User Smith executes the following code at 9:05am:

EXECUTE comm_paclage.g_comm := 20 Which statement is true?

A. g_comm has a value of 15 at 9:06am for Smith.

B. g_comm has a value of 15 at 9:06am for Jones.

C. g_comm has a value of 20 at 9:06am for both Jones and Smith.

D. g_comm has a value of 15 at 9:03 am for both Jones and Smith.

E. g_comm has a value of 10 at 9:06am for both Jones and Smith.

F. g_comm has a value of 10 at 9:03am for both Jones and Smith

Answer  B

16. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY

NUMBER(12,2);

PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2,

V_SALARY NUMBER);

END BB_PACK; /

CREATE OR REPLACE PACKAGE BODY BB_PACK IS

V_PLAYER_AVG NUMBER(4,3);

PROCEDURE UPD_PLAYER_STAT V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4,

V_HITS IN NUMBER) IS

BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS +

V_HITS WHERE PLAYER_ID = V_ID;

COMMIT;

VALIDATE_PLAYER_STAT(V_ID);

END UPD_PLAYER_STAT;

PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2,

V_SALARY NUMBER) IS

BEGIN INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID,

V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0);

END ADD_PLAYER;

END BB_PACK /

Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a

procedure outside the package?

A. V_PLAYER_AVG := .333;

B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;

C. BB_PACK.V_PLAYER_AVG := .333;

D. This variable cannot be assigned a value from outside of the package.

Answer: D

17. Examine this package:

CREATE OR REPLACE PACKAGE manage_emps IS

tax_rate CONSTANT NUMBER(5,2) := .28;

v_id NUMBER;

PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);

PROCEDURE delete_emp; PROCEDURE update_emp;

FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER;

END manage_emps; /

CREATE OR REPLACE PACKAGE BODY manage_emps IS

PROCEDURE update_sal (p_raise_amt NUMBER) IS

BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id;

END;

PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS

BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal);

END insert_emp;

PROCEDURE delete_emp IS

BEGIN DELETE FROM emp WHERE empno = v_id;

END delete_emp;

PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2);

BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id;

IF v_sal < 500 THEN v_raise := .05;

ELSIF v_sal < 1000 THEN v_raise := .07;

ELSE v_raise := .04;

END IF; update_sal(v_raise);

END update_emp;

FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS

BEGIN RETURN p_sal * tax_rate;

END calc_tax;

END manage_emps; /

What is the name of the private procedure in this package?

A. CALC_TAX

B. INSERT_EMP

C. UPDATE_SAL

D. DELETE_EMP

E. UPDATE_EMP

F. MANAGE_EMPS

Answer  C

18. Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two)

A. The view associated with the trigger.

B. The table associated with the trigger.

C. The event associated with the trigger.

D. The package associated with the trigger.

E. The statement level or for each row association to the trigger.

Answer  A, C

19. Which three are valid ways to minimize dependency failure? (Choose three)

A. Querying with the SELECT * notification.

B. Declaring variables with the %TYPE attribute.

C. Specifying schema names when referencing objects.

D. Declaring records by using the %ROWTYPE attribute.

E. Specifying package.procedure notation while executing procedures.

Answer  A, B, D

20. Examine this code: CREATE OR REPLACE PROCEDURE add_dept ( p_name

departments.department_name%TYPE DEFAULT .unknown ., p_loc

departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO

departments(department_id, department_name, loclation_id)

VALUES(dept_seq.NEXTVAL,p_name, p_loc); END add_dept; / You created the add_dept

procedure above, and you now invoke the procedure in SQL *Plus.

Which four are valid invocations? (Choose four)

A. EXECUTE add_dept(p_loc=>2500)

B. EXECUTE add_dept(‘Education’, 2500)

C. EXECUTE add_dept(’2500′, p_loc =>2500)

D. EXECUTE add_dept(p_name=>’Education’, 2500)

E. EXECUTE add_dept(p_loc=>2500, p_name=>’Education’)

Answer  A, B, C, E

21. Examine the procedure:

CREATE OR REPLACE PROCEDURE INSERT TEAM

(V_ID in NUMBER,V_CITY in VARCHER2 DEFAULT ‘AUSTIN’V_NAME

in VARCHER2)

IS

BEGIN

INSERT INTO TEAM (id, city,name)

VALUES (v_id,v_city,v_name);

COMMIT;

END;

Which two statements will successfully invoke this procedure in SQL Plus? (Choose two)

A. EXECUTE INSERT_TEAM;

B. EXECUTE INSERT_TEAM (3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);

C. EXECUTE INSERT_TEAM (3, ‘AUSTIN’, ‘LONGHORNS’);

D. EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY := ‘AUSTIN’);

E. EXECUTE INSERT_TEAM (3, ‘LONGHORNS’);

Answer:  B, C

22. How can you migrate from a LONG to a LOB data type for a column?

A. Use the DBMS_MANAGE_LOB.MIGRATE procedure.

B. Use the UTL_MANAGE_LOB.MIGRATE procedure.

C. Use the DBMS_LOB.MIGRATE procedure.

D. Use the ALTER TABLE command.

E. You cannot migrate from a LONG to a LOB date type for a column.

Answer  D

23. You need to remove the database trigger BUSINESS_HOUR . Which command do you use

to remove the trigger in the SQL *Plus environment?

A. DROP TRIGGER business_hour;

B. DELETE TRIGGER business_hour;

C. REMOVE TRIGGER business_hour;

D. ALTER TRIGGER business_hour REMOVE;

E. DELETE FROM USER_TRIGGERS WHERE TRIGGER_NAME = .BUSINESS_HOUR;

Answer  A

24. You are about to change the arguments of the CALC_TEAM_AVG function. Which

dictionary view can you query to determine the names of the procedures and functions that

invoke the CALC_TEAM_AVG function?

A. USER_PROC_DEPENDS

B. USER_DEPENDENCIES

C. USER_REFERENCES

D. USER_SOURCE

Answer  B

25. You create a DML trigger. For the timing information, which is valid with a DML trigger?

A. DURING

B. INSTEAD

C. ON SHUTDOWN

D. BEFORE

E. ON STATEMENT EXECUTION

Answer  D

26. You want to create a PL/SQL block of code that calculates discounts on customer orders.

This code will be invoked from several places, but only within the program unit

ORDERTOTAL. What is the most appropriate location to store the code that calculates the

discounts?

A. A stored procedure on the server.

B. A block of code in a PL/SQL library.

C. A standalone procedure on the client machine.

D. A block of code in the body of the program unit ORDERTOTAL.

E. A local subprogram defined within the program unit ORDERTOTAL.

Answer  E

27. Which statement about triggers is true?

A. You use an application trigger to fire when a DELETE statement occurs.

B. You use a database trigger to fire when an INSERT statement occurs.

C. You use a system event trigger to fire when an UPDATE statement occurs.

D. You use INSTEAD OF trigger to fire when a SELECT statement occurs.

Answer B

28. Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN

NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER

(ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must

invoke the APD_BAT_STAT procedure and pass a parameter. Which statement,

when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

A. EXECUTE UPD_BAT_STAT(V_ID);

B. UPD_BAT_STAT(V_ID);

C. RUN UPD_BAT_STAT(V_ID);

D. START UPD_BAT_STAT(V_ID);

Answer  B

29. Which four triggering events can cause a trigger to fire? (Choose four)

A. A specific error or any errors occurs.

B. A database is shut down or started up.

C. A specific user or any user logs on or off.

D. A user executes a CREATE or an ALTER table statement.

E. A user executes a SELECT statement with an ORDER BY clause.

F. A user executes a JOIN statement that uses four or more tables.

Answer  A, B, C, D

30. There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you

are granted all object privileges on it. You have a procedure PROCESS_CUSTOMER that

processes customer information that is in the public synonym CUSTOMER table. You have just

created a new table called CUSTOMER within your schema. Which statement is true?

A. Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from

public synonym CUSTOMER table.

B. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table

then the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors.

C. If the structure of your CUSTOMER table is entirely different from the public synonym

CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses

your CUSTOMER table.

D. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table

then the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your

CUSTOMER table.

Answer  D

31. Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS

V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN

NUMBER, V_LAST_NAME VARCHAR2, V_SALARY_NUMBER; END BB_PACK; /

CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE

UPD_PLAYER_STAT (V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN

NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB,

HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID) COMMIT; END

UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME

VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO

PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY);

UPD_PLAYER_STAT(V_ID,0.0); END ADD_PLAYER; END BB_PACK; Which statement

will successfully assign $75,000,000 to the V_MAX_TEAM_SALARY variable from within a

stand-alone procedure?

A. V_MAX_TEAM_SALARY := 7500000;

B. BB_PACK.ADD_PLAYER.V_MAX_TEAM_SALARY := 75000000;

C. BB_PACK.V_MAX_TEAM_SALARY := 75000000;

D. This variable cannot be assigned a value from outside the package.

Answer  C

32. Examine this code: CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON

emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; You

issue an UPDATE command in the EMP table that results in changing 10 rows. How many rows

are inserted into the AUDIT_TABLE ?

A. 1

B. 10

C. None

D. A value equal to the number of rows in the EMP table.

Answer  A

33. Examine this package

CREATE OR REPLACE PACKAGE discounts IS

G_ID NUMBER:=7839;

DISCOUNT_RATE NUMBER 0. 00;

PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER);

END DISCOUNTS;

/

CREATE OR REPLACE PACKAGE BODY discounts

IS

PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER)

IS

BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4

(V_PRICE*NVL(DISCOUNT_RATE, 1)))

END DISPLAY_PRICE;

BEGIN DISCOUNT_RATE;=0. 10;

END DISCOUNTS;

/

Which statement is true?

A. The value of DISCOUNT_RATE always remain 0. 00 in a session.

B. The value of DISCOUNT_RATE is set to 0. 10 each time the package are invoked in a session.

C. The value of DISCOUNT_RATE is set to 1 each time the procedure DISPLAY_PRICE is invoked.

D. The value of DISCOUNT_RATE is set to 0. 10 when the package is invoked for first time in a

session.

Answer:  D

34. Examine this code:CREATE OR REPLACE TRIGGER secure_emp BEFORE LOGON ON

employees BEGIN IF (TO_CHAR(SYSDATE, .DY.) IN ( .SAT., .SUN.)) OR

(TO_CHAR(SYSDATE, .HH24:MI .) NOT BETWEEN .08:00 AND .18:00 )

THEN RAISE_APPLICATION_ERROR (-20500, .You may insert into the EMPLOYEES table

only during business hours. .);

END IF;

END;

What type of trigger is it?

A. DML trigger

B. INSTEAD OF trigger

C. Application trigger

D. System event trigger

E. This is an invalid trigger.

Answer  E

35. Which table should you query to determine when your procedure was last compiled?

A. USER_PROCEDURES

B. USER_PROCS

C. USER_OBJECTS

D. USER_PLSQL_UNITS

Answer  C

36. Examine this code:CREATE OR REPLACE FUNCTION gen_email_name (p_first_name

VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is

v_email_name VARCHAR2(19);

BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) ||

.@Oracle.com .;

UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN

v_email_name;

END;

You run this SELECT statement:

SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM

employees; What occurs?

A. Employee 108 has his email name updated based on the return result of the function.

B. The statement fails because functions called from SQL expressions cannot perform DML.

C. The statement fails because the functions does not contain code to end the transaction.

D. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring

in stored functions called from SQL expressions.

E. The SQL statement executes successfully and control is passed to the calling environment.

Answer  B

37. What part of a database trigger determines the number of times the trigger body executes?

A. Trigger type

B. Trigger body

C. Trigger event

D. Trigger timing

Answer  A

38. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and

DELETE operations?

A. The rows are selected and ordered.

B. The validity of the SQL statement is established.

C. An area of memory is established to process the SQL statement.

D. The SQL statement is run and the number of rows processed is returned.

E. The area of memory established to process the SQL statement is released.

Answer  D

39. Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (sal

NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END;

If you want to run the above function from the SQL *Plus prompt, which statement is true?

A. You need to execute the command CALCTAX(1000); .

B. You need to execute the command EXECUTE FUNCTION calc tax; .

C. You need to create a SQL *Plus environment variable X and issue the command :X :=

CALCTAX(1000); .

D. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X :=

CALCTAX;

E. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X :=

CALCTAX(1000);

Answer  E

40. Which statements are true? (Choose all that apply)

A. If errors occur during the compilation of a trigger, the trigger is still created.

B. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the

USER_TRIGGERS data dictionary view to see the compilation errors.

C. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command

within iSQL *Plus to see the compilation errors.

D. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the

USER_ERRORS data dictionary view to see compilation errors.

Answer  A, C, D

41. You need to create a trigger on the EMP table that monitors every row that is changed and

places this information into the AUDIT_TABLE. What type of trigger do you create?

A. FOR EACH ROW trigger on the EMP table.

B. Statement-level trigger on the EMP table.

C. FOR EACH ROW trigger on the AUDIT_TABLE table.

D. Statement-level trigger on the AUDIT_TABLE table.

E. FOR EACH ROW statement-level trigger on the EMP table.

Answer  A

42. Examine this package: CREATE OR REPLACE PACKAGE BB:PACK IS

V_MAX_TEAM:SALAR NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER,

V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR

REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT (V_ID IN

NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE

PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE

PLAYER_ID = V_ID; COMMIT; END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER

(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN

INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME,

V_SALARY); UPD_PLAYER_STAT(V_ID,0,0); END ADD_PLAYER; END BB_PACK; You

make a change to the body of the BB_PACK package. The BB_PACK body is recompiled. What

happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?

A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated.

B. VALIDATE_PLAYER_STAT is not invalidated.

C. VALDIATE_PLAYER_STAT is invalidated.

D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.

Answer  B

43. Which code can you use to ensure that the salary is not increased by more than 10% at a

time nor is it ever decreased?

A. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);

B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH

ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN

RAISE_APPLICATION_ERROR ( – 20508, .Do not decrease salary not increase by more than 10% );

END;

C. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN

(new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( – 20508,

.Do not decrease salary not increase by more than 10% ); END;

D. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN

(new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( – 20508,

.Do not decrease salary not increase by more than 10% ); END;

Answer  B

44. Which two statements describe the state of a package variable after executing the package in

which it is declared? (Choose two)

A. It persists across transactions within a session.

B. It persists from session to session for the same user.

C. It does not persist across transaction within a session.

D. It persists from user to user when the package is invoked.

E. It does not persist from session to session for the same user.

Answer  A, E

45. Which two programming constructs can be grouped within a package? (Choose two)

A. Cursor

B. Constant

C. Trigger

D. Sequence

E. View

Answer  A, B

46. Examine this code:CREATE OR REPLACE PRODECURE add_dept (p_dept_name

VARCHAR2 DEFAULT .placeholder ., p_location VARCHAR2 DEFAULT .Boston .)

IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name,

p_location);

END add_dept; /

Which three are valid calls to the add_dep procedure ? (Choose three)

A. add_dept;

B. add_dept( .Accounting .);

C. add_dept(, .New York .);

D. add_dept(p_location=> .New York .);

Answer  A, B, D

47. You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to

remove a table in your schema. You have granted the EXECUTE privilege to user A on this

procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose

privileges are the operations performed by default?

A. SYS privileges

B. Your privileges

C. Public privileges

D. User A.s privileges

E. User A cannot execute your procedure that has dynamic SQL.

Answer  B

48. Examine this function:

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in

PLAYER_BAT_STAT.PLAYER_ID%TYPE)

RETURN NUMBER IS V_AVG NUMBER;

BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE

PLAYER_ID = V_ID;

RETURN (V_AVG);

END;

Which statement will successfully invoke this function in SQL *Plus?

A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

B. EXECUTE CALC_PLAYER_AVG (31);

C. CALC_PLAYER (.RUTH.);

D. CALC_PLAYER_AVG(31);

E. START CALC_PLAYER_AVG(31)

Answer  A

49. The number of cascading triggers is limited by which data base initialization parameter?

A. CASCADE_TRIGGER_CNT.

B. OPEN_CURSORS.

C. OPEN_TRIGGERS.

D. OPEN_DB_TRIGGERS.

Answer: B

50. When creating store procedures and functions which construct allows you to transfer values

to and from the calling environment?

A. Local variables.

B. Arguments.

C. Boolean variables.

D. Substitution variables.

Answer:  B

51. You need to remove database trigger BUSINESS_RULE. Which command do you use to

remove the trigger in the SQL*Plus environment?

A. DROP TRIGGER business_rule;

B. DELETE TRIGGER business_rule;

C. REMOVE TRIGGER business_rule;

D. ALTER TRIGGER business_rule;

E. DELETE FROM USER_TRIGGER

F. WHERE TRIGGER_NAME= ‘BUSINESS_RULE’;

Answer:  A

52. Which two tables are fused track object dependencies? (Choose two)

A. USER_DEPENDENSIES.

B. USER_IDEPTREE.

C. IDEPTREE.

D. USER_DEPTREE.

E. USER_DEPENDS.

Answer:  A, C

53. The QUERY_PRODUCT procedure directly references the product table. There is a

NEW_PRODUCT_VIEW view created based on the NOT NULL columns of the table. The

ADD_PRODUCT procedure updates the table indirectly by the way of

NEW_PRODUCT_VIEW view. Under which circumstances does the procedure

ADD_PRODUCT get invalidated but automatically get complied when invoked?

A. When the NEW_PRODUCT_VIEW is dropped.

B. When rows of the product table are updated through SQI Plus.

C. When the internal logic of the QUERY_PRODUCT procedure is modified.

D. When a new column that can contain null values is added to the product table.

E. When a new procedure s created that updates rows in the product table directly.

Answer:  D

54. You need to recompile several program units you have recently modified through a PL/SQL

program. Which statement is true?

17

A. You cannot recompile program units using a PL/SQL program.

B. You can use the DBMS_DDL. REOMPILE package procedure to recompile the program units.

C. You can use the DBMS_ALTER. COMPILE packaged procedure to recompile the program units.

D. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program

units.

E. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to recompile the program

units.

Answer:  D

55. Which type of argument passes a value from a calling environment?

A. VARCHER2.

B. BOOLEAN.

C. OUT.

D. IN.

Answer:  D

56. In order for you to create run a package MAINTAIN_DATA which privilege do you need?

A. EXECUTE privilege on the MAINTAIN_DATA package.

B. INVOKE privilege on the MAINTAIN_DATA package.

C. EXECUTE privilege on the program units in the MAINTAIN_DATA package.

D. Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.

E. Execute privilege on the program units inside the MAINTAIN_DATA package and execute

privilege on the MAINTAIN_DATA package.

Answer:  A

57. You have created a script file EMP_PROC.SQL that holds the text to create a procedure

PROCESS_EMP. You have compiled the procedure for SQL Plus environment by running the

script file EMP_PROC.SQL. What happens if there are syntax errors in the procedure

PROCESS_EMP?

A. The errors are stored in the EMP_PROC.ERR file.

B. The errors are displayed to the screen when the script file is run.

C. The errors are stored in the procedure_errors data dictionary view.

D. YOU need to issue the SHOW ERRORS command in the SQL Plus environment to see the errors.

E. YOU need to issue the display errors command in the SQL Plus environment to see the errors.

Answer:  D

58. Which statement about the local dependent object is TRUE?

A. They are on different nodes.

B. They are in a different database.

C. They are on the same node in the same database.

D. They are on the same node in a different database.

Answer:  C

59. You need to create a stored procedure, which deletes rows from a table. The name

of the table from which the rows are to be deleted is unknown until run time.

Which method do you implement while creating such a procedure?

A. Use SQL command delete in the procedure to delete the rows.

B. Use DBMS_SQL packaged routines in the procedure to delete the rows.

C. Use DBMS_DML packaged routines in the procedure to delete the rows.

D. Use DBMSDELETE packaged routines in the procedure to delete the rows.

E. You cannot have a delete statement without providing a table name before compile time.

Answer:  B

60. Under which situation do you create a server side procedure?

A. When the procedure contains no SQL statements.

B. When the procedure contains no PL/SQL commands.

C. When the procedure needs to be used by many client applications accessing several remote

databases.

D. When the procedure needs to be used by many users accessing the same schema objects on a local

database.

Answer:  D

61. Which code successfully calculates tax?

A. CREATE OR REPLACE PROCEDURE calc (p_no IN NUMBER)

RETURN tax IS

V_sal NUMBER;

Tax NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE EMPNO=p_no;

Tax:=v_sal * 0. 05;

END;

B. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER)

RETURN NUMBER IS

V_sal NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno =p_no;

RETURN (v_sal* 0. 05);

END;

C. CRETAE OR REPLACE FUNCTION calctax(p_no NUMBER)

RETURN NUMBER IS

V_sal NUMBER;

Tax NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno =p_no;

Tax:=v_sal * 0. 05;

END;

D. CREATE OR REPLACE FUNCTION calctax(p_no NUMBER)IS

V_sal NUMBER;

Tax NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno =p_no;

Tax :=v_sal * 0. 05;

RETURN(tax);

END;

Answer:  B

62. The programmer view developed a procedure ACCOUNT_TRANSACTION left

organization. You were assigned a task to modify this procedure. YOU want to find all the

program units invoking the ACCOUNT_TRANSACTION procedure.

How can you find this information?

A. Query the USER_SOURCE data dictionary view.

B. Query the USER_PROCEDURES data dictionary view.

C. Query the USER_DEPENDENCIES data dictionary views.

D. Set the SQL Plus environment variable trade code=true and run the ACCOUNT_TRANSACTION

procedure.

E. Set the SQL Plus environment variable DEPENDENCIES=TRUE and run the

Account_Transaction procedure.

Answer:  C

63. All users currently have the INSERT privileges on the PLAYER table. You want only your

users to insert into this table using the ADD_PLAYER procedure. Which two actions must you

take? (Choose two)

A. GRANT SELECT ON ADD_PLAYER TO PUBLIC;

B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

C. GRANT INSERT ON PLAYER TO PUBLIC;

D. GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

E.  REVOKE INSERT ON PLAYER FROM PUBLIC;

Answer:  B, E

64. Which Oracle supply package allows you to run jobs at use defined times?

A. DBMS_JOB

B. DBMS_RUN

C. DBMS_PIPE

D. DBMS_SQL

Answer:  A

65. You need to drop a table from within a stored procedure. How do you implement this?

A. You cannot drop a table from a stored procedure.

B. Use the DROP command in the procedure to drop the table.

C. Use the DBMS_DDL packaged routines in the procedure to drop the table.

D. Use the DBMS_SQL packaged routines in the procedure to drop the table.

E. Use the DBMS_DROP packaged routines in the procedure to drop the table.

Answer:  D

66. Examine this package

CREATE OR REPLACE PACKAGE BB_PACK

IS

V_MAX_TEAM_SALARY NUMBER(12,2);

PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME)

VARCHAR2(V_SALARY NUMBER);

END BB_PACK;

/

CREATE OR REPLACE PACKAGE BODY BB_PACK

IS

V_PLAYER_AVG NUMBER(4,3);

PROCEDURE UPD_PLAYER_STAT

V_ID IN NUMBER, V_AB IN NUMBER DEFAULT4, V_HITS IN NUMBER)

IS

BEGIN

UPDATE PLAYER_BAT_STAT

SET ADD_BAT=ADD_BATS+V_AB,

HITS=HITS+V_HITS

WHERE PLAYER_ID=V_ID;

COMMIT;

VALIDATE_PLAYER_STAT(V_ID);

END UPD_PLAYER_STAT;

PROCEDURE ADD_PLAYER

(V_ID IN NUMBER, V_LAST_NAME, VARCHAR2, V_SALARY IN NUMBER);

IS

BEGIN

INSERT INTO PLAYER (ID, LAST_NAME, SALARY)

VALUES(V_ID, V_LAST_NAME, V_SALARY);

UPD_PLAYER_STAT(V_ID,0,0);

END ADD_PLAYER;

END BB_PACK;

Which kind of packaged variables is V_MAX_TEAM_SALARY?

A. PRIVATE

B. PUBLIC

C. IN

D. OUT

Answer:  B

67. Examine this trigger.

CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY

AFTER INSERT OR UPDATE OR DELETE ON PLAYER

FOR EACH ROW

BEGIN

UPDATE TEAM

SET TOT_SALARY=TOT_SALARY+:NEW SALARY.

WHERE ID=:NEW:TEAM_ID;

You will be adding additional coat later but for now you want the current block to

fire when updated the salary column. Which solution should you use to verify that

the user is performing an update on the salary column?

A. ROW_UPDATE(‘SALARY’)

B. UPDATING(‘SALARY’)

C. CHANGING(‘SALARY’)

D. COLUMN_UPDATE(‘SALARY’)

Answer:  B

68. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK

V_MAX_TEAM_SALARY NUMBER(12,2);

PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME

VARCHAR2, V_SALARY NUMBER);

DB_PACK;/ CREATE OR REPLACE PACKAGE BODY BB_PACK

IS

V_WHERE_AVG NUMBER(4,3);

PROCEDURE UPD_PLAYER_STAT

(V_ID IN NUMBER, V_AVG IN NUMBER DEFAULT 4,V_HITS IN NUMBER)

IS

BEGIN

UPDATE PLAYER_BAT_STAT

SET AT_BATS=AT_BATS+V_AB,

HITS=HITS+V_HITS

WHERE PLAYER_ID=V_ID;

COMMIT;

VALIDATE_PLAYER_STAT(V_ID);

END UPD_PLAYER_STAT;

PROCEDURE ADD-PLAYER

(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)

IS

BEGIN

INSERT INTO PLAYER(ID, LAST_NAME, SALARY)

VALUES(V_ID, V_LAST_NAME, V_SALARY);

UPD_PLAYER_STAT(V_ID,0,0);

END ADD_PLAYER;

END BB_PACK;

An outside procedure VALIDATE_PLAYER_STAT is executed from this package.

What will happen when this procedure changes?

A. The package specification is dropped.

B. The package specification is invalidated.

C. The package is invalidate.

D. The package body is invalidated.

Answer:  D

69. Examine this package

CREATE OR REPLACE PACKAGE PACK_CUR

IS

CURSOR C1 IS

SELECT PRODID

FROM PRODUCT ORDER BY PRODID DESC;

PROCEDURE PROC1;

PROCEDURE PROC2;

END PACK_CUR;

/

CREATE OR REPLACE PACKAGE BODY PACK_CUR

IS

V_ID NUMBER;

PROCEDURE PROC1 IS

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO V_PRODID;

DBMS_OUTPUT. PUT_LINE (ROW IS :,||C1/ROWCOUNT);

EXIT WHEN C1/ROWCOUNT>=3;

END LOOP;

END PROC1;

PROCEDURE PROC2 IS

BEGIN

LOOP

FETCH C1 TO V_PRODID

DBMS_OUTPUT. PUT_LINE (ROW IS :,||C1/ROWCOUNT);

EXIT WHEN C1/ROWCOUNT>=6;

END LOOP;

CLOSE C1;

END PROC2;

END PACK_CUR;

/

The products table has more than 1000 rows. The SQL plus server output setting is

turned on in your session. You execute procedure proc1 fromsql plus with the

command:

EXECUTE PACK_CUR.PROC1. What is the output in your session?

A.  Error at line 1

B.   Row is:

Row is:

Row is:

C.  Row is:1

Row is:2

Row is:3

D.  Row is:4

Row is:5

Row is:6

Answer:  C

70. Which two statements about packages are true? (Choose two)

A. Both specifications and body are required components of a package.

B. Package specification is optional but the package body is required.

C. A package specification is required but the package body is optional.

D. The specification and body of the package is stored together in a database.

E. The specification and body of the package are stored separately in the database.

Answer:  C, E

71. You want to send a message to another session connected to the same instance.

Which Oracle supplied package will you use to achieve this task?

A. DBMS_JOB

B. DBMS_PIPES

C. DBMS_OUTPUT

D. DBMS_MESSAGE

E. SEND_MESSAGE

Answer:  B

72. Which system privileges must you have to manually recompile a stored procedure

owned by another application developer?

A. ALTER PROCEDURE

B. ALTER ANY PROCEDURE

C. ALTER ALL PROCEDURE

D. COMPILE ANY PROCEDURE

Answer:  B

73. Which situation requires a before update statement level trigger on the table?

A. When you need to populate values of each updated row into another table.

B. When a trigger must fire for each row affected by the triggering statement.

C. When you need to make sure that user making modifications to the table as necessary privileges.

D. When you need to store the information of the use who successfully modified tables and in audit

table.

Answer:  C

74. Examine the trigger

Create a replace trigger cascade_updates

After update (Deptno) on Dept

For each row

BEGIN

UPDATE EMP

SET emp_deptno=: new. Deptno

WHERE emp.Deptno=: old.Deptno;

END

When this trigger will fire successfully?

A. Only when the dept no in the emp table holds a NULL value.

B. Irrespective of any referential integrity constraints between two tables.

C. When there is no referential integrity between the dept number columns of the emp and the dept

tables within their table definitions.

D. Only when there is referential integrity constraint between the emp no columns of the emp and dept

tables within their table definitions.

Answer:  C

75. Examine this code:

CREATE OR REPLACE PROCEUDRE AUDIT_EMP;

(P_ID IN EMP. EMPNO%TYPE)

IS

V_ID NUMBER;

PROCEDURE LOG_EXEC

IS

BEGIN

INSERT INTO LOG_TABLE (USER_ID,LOG_DATE)

VALUES (USERS,SYSDATE);

END LOG_EXEC

V_NAME VARCHAR2(20)

BEGIN

DELECT FROM EMP

WHERE EMPNO = P_ID;

LOG_EXEC;

SELECT ENAME,EMPNO

INTO V_NAME,V_ID

FROM EMP

WHERE EMPNO=P_ID

END AUDIT_EMP;

Why does this code cause and error when compiled?

A. An insert statement is not allowed in a sub program declaration.

B. The LOG_exec procedure should be declared before any identifiers.

C. The V_NAME variable should be declared before declaring the LOG_EXEC procedure.

D. The LOG_EXEC procedure should be invoked as execute log_exec with in the AUDIT_EMP

procedure.

Answer:  C

76. When creating a function in which section will you typically find a return key word?

A. Header Only

B. Declarative

C. Executable and header

D. Executable and exception handling

Answer:  C

77. Examine this package

CREATE OR REPLACE PACKAGE COMPILE_THIS

IS

G_VALUE VARCHAR2(100);

PROCEDURE A;

PROCEDURE B;

END COMPILE_THIS;

/

CREATE OR REPLACE PACKAGE BODY COMPILE_THIS

IS

PROCEDURE A

IS

BEGIN

G_VALUE := (‘HELLO WORLD’);

END A;

PROCEDURE B

IS

BEGIN

C;

DBMS_OUTPUT. PUT_LINE (‘PROCEDURE B CALLING C’);

END B;

PROCEDURE C

IS

BEGIN

B;

DBMS_OUTPUT. PUT_LINE (‘PROCEDURE C CALLING B’);

END;

END COMILE_THIS; /

Procedure C is a local construct to the package. What happens when this package is

compiled?

A. It produces the output Procedure B calling C

B. It produces the output Procedure C calling B

C. It produces a compilation error because procedure C requires a forward declaration.

D. It produces a compilation error because procedure B requires a forward declaration.

E. It produces a compilation error because identified g_value is not declared in procedure A

Answer:  C

78. The ADD_PLAYER, UPD_PLAYER_STAT and UPD_PITCHER_STAT procedures are

grouped together in a package. A variable must be shared among only these procedures. Where

should you declare this variable?

A. In the package body.

B. In the data base triggers.

C. In the package specification.

D. In the procedures declare section using the exact name in each.

Answer:  A

79. Examine the trigger heading

CREATE OR REPLACE TRIGGER SALARY_CHECK

Before update (sal,job) on emp

For each row

Under what conditions does this trigger fire?

A. When a row is inserted to EMP table.

B. When the value of the SAL or JOB column in a row is updated in a emp table.

C. When any column other than the sal or job columns in a row are updated in the EMP table.

D. Only when both values of sal or jobs column in a row are updated together in the EMP table.

Answer:  B

80. Which code can you use to ensure that the salary is neither increased by more than

10% at a time nor is ever decreased?

A. ALTER TABLE emp ADD

constraint_ck_sal CALC(sal BETWEEN sal AND sal*1.1);

B. CREATE OR REPLACE TRIGGER check_sal

BEFORE UPDATE OF sal ON emp

FOR EACH ROW

WHEN(NEW.SAL OR

NEW.SAL>OLD.SAL*1.1)

BEGIN

RAISE_APPLICATION_ERROR(-20508, ‘do not decrease salary nor

increase by more than 10%’);

END;

C. CREATE OR REPLACE TRIGGER check_sal

BEFORE UPDATE OF sal OR emp

WHEN (NEW.SAL OR

NEW.SAL>OLD.SAL*1.1)

BEGIN

RAISE_APPLICATION_ERROR(-20508, ‘Do not decrease salary nor

increase by more than 10%’);

D. CREATE OR REPLACE TRIGGER check_sal

AFTER UPDATE OF sal OR emp

WHEN (NEW.SAL OR

NEW.SAL>OLD.SAL*1.1)

BEGIN

RAISE_APPLICATION_ERROR(-20508, ‘Do not decrease salary nor

increase by more than 10%’);

END;

Answer:  B

81. Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on

the TEAM table?

A. GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC;

B. GRANT SELECT, INSERT, UPDATE, DELETE ONUPD_TEAM_STAT TO PUBLIC;

C. GRANT EXECUTE ON TEAM TO PUBLIC;

D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;

Answer:  A

82. Which compiler directive to check the purity level of functions?

A. PRAGMA SECURITY_LEVEL.

B. PRAGMA SEARIALLY_REUSABLE.

C. PRAGMA RESTRICT_REFERRENCES.

D. PRAGMA RESTRICT_PURITY_LEVEL.

E. PRAGMA RESTRICT_FUNCTION_REFERRENCE.

Answer: C

83. You have an AFTER UPDATE row-level trigger on the table EMP. This trigger queries the

EMP table and inserts the updating users information into the AUDIT_TABLE. What happens

when the users update rows on the EMP table?

A. A compile time error occurs.

B. A run time error occurs. The effect of the trigger body and the triggering statement are rolled back.

C. A run time error occurs. The effect of the trigger body is rolled back but the update on the EMP

table takes place.

D. The trigger file successfully update the EMP file on the EMP table occurs and the data is asserted

into the AUDIT_TABLE.

E. A run time error occurs. The update on the EMP table does not take place but the insert into the

AUDIT_TABLE occurs.

Answer:  B

84. Given the header of a procedure ACCOUNT_TRANSACTION:CREATE OR

REPLACE PROCEDURE ACCOUNT_TRANSACTION

IS

BEGIN

END;

Which command will execute the PROCEDURE ACCOUNT_TRANSACTION

from the SQL Plus prompt?

A. ACCOUNT_TRANSACTION;

B. RUN ACCOUNT_TRANSACTION;

C. START ACCOUNT_TRANSACTION;

D. EXECUTE ACCOUNT_TRANSACTION;

Answer:  D

85. Examine this trigger:

CREATE OR REPLACE TRIGGER UPD_PLAYER_STAT_TRIG

AFTER INSERT ON PLAYER

FOR EACH ROW

BEGIN

INSERT INTO PLAYER_BAT_STAT(PLAYER_ID,

SEASON_YEAR,AT_BATS,HITS)

VALUES(player_id_seq.currval, 1997, 0, 0 );

END;

After creating this trigger, you test it by inserting a row into the PAYER table. You

receive this error message:

ORA-04091: table SCOTT.PLAYER is mutating,trigger/function may not see it.

How can you avoid getting this error?

A. Drop the foreign key contraint on the PLAYER_ID column of the PLAYER_BAT_STAT table.

B. Drop the primary key contraint on the PLAYER_ID column of the PLAYER_BAT_STAT table.

C. Drop the primary key constraint on the ID column of the PLAYER table.

D. The code of the trigger is invalid. Drop and recreate the trigger.

Answer:  A

86. Examine this package:

CREATE OR REPLACE PACKAGE manage_emps

IS

Tax_rate CONSTRAINT NUMBER(5,2):=. 28;

v_id NUMBER;

PROCEDURE insert_emp(p_dept NO NUMBER, p_sal NUMBER);

PROCEDURE delete_emp;

PROCEDURE update_emp;

FUNCTION calc_text(p_sal NUMBER)

RETURN NUMBER;

END manage_emps;

/

CREATE OR REPLACE PACKAGE BODY manage_emps

IS

PROCEDURE update_sal

(p_raise_amt NUMBER)

IS

BEGIN

UPDATE EMP

SET SAL=(SAL*p_raise_AMP)+SAL WHERE EMPNO=v_id;

END;

PROCEDURE insert_emp

(p_deptno NUMBER,p_sal NUMBER)

IS

BEGIN

INSERT INTO EMP(EMPNO,DEPTNO,SAL)

VALUES(v_id,p_deptno,p_sal);

INERT INTO EMP;

PROCEURE delete_emp

IS

BEGIN

DELETE FROM EMP

WHERE EMPNO=v_id;

END delete_emp;

PROCEDURE audit_emp;

IS

V_sal NUMBER(10,2);

V_raise NUMBER(10,2);

IS

SELECT SAL

INTO v_sal

FROM EMP

WHERE EMPNO=v_id;

IF v_sal<500 THEN v_raise:=. 05;ELSE

v_sal<1000 THEN v_raise:=. 07;ELSE

v_raise:=. 04;

END IF; update_sal (v_raise);

END update_emp; FUNCTION calc_tax

(p_sal NUMBER)

RETURN NUMBER

IS

BEGIN

RETURN p_sal*tax_rate;

END calc_tax;

END manage_emps;

/

How many public procedures are there in the MANAGE_EMPS package?

A. 1.

B. 2.

C. 3.

D. 4.

E. 5.

F. None.

Answer:  C

87. You want to execute a procedure from SQL Plus. However you are not sure of the

argument list for this procedure. Which command will display the argument list?

A. DESCRIBE.

B. SHOWLIST.

C. SHOW ARG_LIST.

D. SHOW PROCEDURE.

Answer:  A

88. You are creating a stored procedure in the SQL Plus environment. The text of the

procedure is stored in a script file. You run the script file to compile the procedure.

What happens if the procedure contains syntax error?

A. Neither the source code nor the errors are stored in the database.

B. Both the source code and the compilation errors are stored in the database.

C. Compilation errors are appended to the script file that contains the source code.

D. The source code is stored in the database and the errors are stored in an output file.

E. The only compilation errors are written to the database and source code remains in the script file.

Answer:  B

89. Which statement is true?

A. Server side procedures are stored in script files on the server.

B. Server side procedures are visible in the ALL_SOURCE dictionary view.

C. Server side procedures are visible in the SERVER_SOURCE dictionary view.

D. Server side procedures are visible in the SERVER_PROCEDURE data dictionary view.

Answer:  A

90. Examine this package specification:

CREATE OR REPLACE PACKAGE concat_all

IS

V_string VARCHER2(100);

PROCEDURE combine(p_num_val NUMBER);

PROCEDURE combine (p_dateval DATE);

PROCEDURE combine(p_char_val VARCHER2,p_num_val NUMBER);

END concat_all;

/

Which overloaded COMBINE procedure declaration can be added to this package

specification?

A. PROCEDURE combine;

B. PROCEDURE combine (p_no NUMBER);

C. PROCEDURE combine (p_val_1 VARCHER2,p_val_2 NUMBER);

D. PROCEDURE concat_all (p_num_val VARCHER2,p_char_val NUMBER);

Answer:  A

91. Examine this package body:

CREATE OR REPLACE PACKAGE BODY forward_pack

IS

V_sum NUMBER;

– 44 –

PROCEDURE calc_ord(. . . );

PROCEDURE generate_summary(. . . )

IS

BEGIN

Calc_ord(. . . );

. . .

END calc_ord;

END forward_pack;

/

Which construct has a forward declaration?

A. V_SUM

B. CALC_ORD.

C. FORWARD_PACK

D. GENERATE_SUMMARY.

Answer:  B

92. CREATE OR REPLACE PROCEDURE manage_emp(p_eno NUMBER)

IS

V_sal emp.sal%TYPE;

V_job emp.job%TYPE;

BEGIN

SELECT sal,job

INTO v_sal,v_job

FROM emp

WHERE empno=p_eno;

IF(v_sal<1000)THEN

DBMS_OUTPUT.PUT_LINE(‘Delete employees who earn less than$1000’);

DELETE FROM emp

WHERE empno=p_eno;

ELSE

DBMS_OUTPUT.PUT_LINE(‘Updating employee salaries.’);

UPDATE emp

SET sal=sal+100

WHERE empno=p_eno;

END IF;

END;

/

What privileges do you need in order to invoke this procedure?

A. No privileges are required.

B. EXECUTE privilege on the procedure.

C. EXECUTE privilege on the DBMS_OUTPUT package.

D. DELETE and UPDATE privilege on the table EMP.

E. EXECUTE privilege on the procedure, and delete and update privileges on the table EMP.

Answer:  B

93. The ADD_PLAYER procedure inserts rows into the player table. Which command will

show this direct dependency?

A. SELECT * FROM USER_DEPENDENCIES WHERE REFFERENCE_NAME= ‘PLAYER’;

B. SELECT * FROM USER_DEPENDENCIES WHERE REFFERENCE_NAME=

‘ADD_PLAYER’;

C. SELECT * FROM USER_DEPENDENCIES WHERE TYPE= ‘DIR’;

D. SELECT * FROM USER_DEPENDENCIES WHERE REFFERENCE_NAME= ‘TABLE’;

Answer:  A

94. Examine this procedure:

CREATE OR REPLACE PROCEDURE ADD_PLAYER

(V_ID IN NUMBER, V_LAST_NAME VARCHER2(30))

IS

BEGIN

INSERT INTO PLAYER(ID, LAST_NAME)

VALUES(V_ID, V_LAST_NAME);

COMMIT;

END;

Why does this command fail when executed?

A. When declaring arguments length is not allowed.

B. When declaring arguments each argument must have a mode specified.

C. When declaring arguments each argument must have a length specified.

D. When declaring a VARCHAR2 argument it must be specified.

Answer:  A

95. Examine this trigger:

CREATE OR REPLACE TRIGGER CHECK_TOT_SALARY

AFTER INSERT OR UPDATE OF SALARY ON PLAYER

FOR EACH ROW

DECLARE

V_TOT_SALS NUMBER(12, 2);

BEGIN

SELECT SUM(SALARY)

INTO V_TOT_SAL

FROM PLAYER

WHER TEAM_ID=:NEW. SALARY;

END;

Why does this trigger fail when inserting a row into player table?

A. You can’t read data from a table that is being affected by the same trigger.

B. You can’t use the sum function with row triggers.

C. You can’t use the sum function with statement triggers.

D. You can’t reference :NEW with row triggers.

Answer:  A

96. Which procedure of the dbms_output supply package would you use to append text

to the current line of the output buffer?

A. GET.

B. GET_LINE.

C. PUT_TEXT_LINE.

D. PUT_LINE.

Answer:  D

97. What happens during the parse phase with dynamic SQL?

A. Rows are selected and ordered.

B. The number of rows processed is returned.

C. The validity of the SQL statement is established.

D. An area of memory is established to process the SQL statement.

E. An area of memory is established to process the SQL statement is released.

Answer:  C

98. Which script file must be executed before you can determine indirect independence’s using

the DEPTREE AND IDEPTREE VIEWS?

A. UTL_IDEPT.SQL.

B. UTLIDD.SQL.

C. UTLINDD.SQL.

D. UTLDTREE.SQL

Answer:  D

99. Debug the logic in a stored procedure. How do you monitor the value of variables in the

procedure using SQL Plus environment?

A. INSERT TEXT_IO.PUT_LINE statement to view data on the screen when the stored procedure is

executed.

B. Insert break points in the code and observe the variable values displayed to the screen as the

procedure is executed.

C. Insert DBMS_OUTPUT.PUT_LINE statement to view data on the screen when the stored

procedure is executed.

D. Insert DEBUG VARIABLE statements to view the variable values on the screen as the procedure is

executed.

Answer:  C

100. Which two statements are true? (Choose two)

A. A function must return a value.

B. A procedure must return a value.

C. A function executes a PL/SQL statement.

D. A function is invoked as part of an expression.

E. A procedure must have a return data type specify in its declaration.

Answer:  A, D

101. Which allows a PL/SQL user define a function?

A. NEXTVAL.

B. HAVING clause of the SELECT COMMAND.

C. ALTER TABLE command.

D. FROM clause of the SELECT AN UPDATE COMMANDS.

Answer:  B

102. CREATE OR REPLACE PROCEDURE set_bonus

(p_cutoff IN VARCHAR2 DEFAULT ‘WEEKLY’

p_employee_id IN employees_employee_id%TYPE

p_salary IN employees_salary%TYPE,

p_bonus_percent IN OUT NUMBER DEFAULT 1.5,

p_margin OUT NUMBER DEFAULT 2,

p_bonus_value OUT NUMBER)

IS

BEGIN

UPDATE emp_bonus

SET bonus_amount =(p_salary * p_bonus_percent)/p_margin

WHERE employee_id = p_employee_id;

END set_bonus;

You execute the CREATE PROCEDURE statement above and notice that it fails. What are two

reasons why it fails? (Choose two)

A. The syntax of the UPDATE statement is incorrect.

B. You cannot update a table using a stored procedure.

C. The format parameter p_bonus_value is declared but is not used anywhere.

D. The formal parameter p_cutoff cannot have a DEFAULT clause.

E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.

F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.

Answer:  E, F

103. Which three statements are true regarding database triggers? (Choose three)

A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view,

schema, or the database.

B. A database trigger needs to be executed explicitly whenever a particular event takes place.

C. A database trigger executes implicitly whenever a particular event takes place.

D. A database trigger fires whenever a data event (such as DML) or system event (such as logon,

shutdown) occurs on a schema or database.

E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event

for that specific user.

Answer:  A, C, D

104. Examine this package:

CREATE OR REPLACE PACKAGE pack_cur

IS

CURSOR c1 IS

SELECT prodid

FROM product

ORDER BY Prodid DESC;

PROCEDURE Proc1;

PROCEDURE Proc2;

END pack_cur;

/

CREATE OR REPLACE PACKAGE BODY pack_cur

IS

v_prodif NUMBER;

PROCEDURE proc1 IS

BEGIN

OPEN C1;

LOOP

PROCEDURE proc2 IS

BEGIN

LOOP

FETCH C1 INTO v_prodid;

DBMS_OUTPUT-PUT_LINE ( ‘ Row is: ‘ ll c1 %ROWCOUNT);

EXIT WHEN C1%ROWCOUNT >= 3;

END LOOP;

END Procl;

/

The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned

on in your session.

You execute the procedure PROC1 from SQL *Plus with the command:

EXECUTE pack_cur. PROC1;

You then execute the procedure PROC2 from SQL *Plus with the command:

EXECUTE pack_cur. PROC2;

What is the output in your session from the PROC2 procedure?

A.  ERROR at line 1:

B.  Row is:

Row is:

Rows is:

C.  Row is: 1

Row is: 2

Row is: 3

D.  Row is: 4

Row is: 5

Row is: 6

Answer:  D

105. You have the following table:

CREATE TABLE Emp_log (

Emp_id NUMBER

Log_date DATE,

New_salary NUMBER,

Action VARCHAR (20));

You have the following data in the EMPLOYEES table:

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

———– ——————- ———— ————-

100 King 24000 90

101 Kochhar 17000 90

102 De Haan 17000 90

103 Hunold 9000 60

104 Ernst 6000 60

105 Austin 4800 60

106 Pataballa 4800 60

107 Lorentz 4200 60

108 Greenberg 12000 100

201 Hartstein 13000 20

202 Fay 6000 20

You create this trigger:

CREATE OR REPLACE TRIGGER Log_salary_increase

AFTER UPDATE ON employees

FOR EACH ROW

WHEN (new.Salary > 1000)

BEGIN

INSERT INTO Emp_log (Emp_id, Log_date, New_Salary, Action)

VALUES (: new.Employee_id, SYSDATE, :new.salary, ‘NEW SAL’ );

END

/

Then, you enter the following SQL statement:

UPDATE Employee SET Salary = Salary + 1000.0

Where Department_id = 20M

What are the result in the EMP_LOG table?

A.

EMP_ID LOG_DATE NEW_SALARY ACTION

———- ——– ———- ———-

201 24-SEP-02 13000 NEW SAL

202 24-SEP-02 600 NEW SAL

B.

EMP_ID LOG_DATE NEW_SALARY ACTION

———- ——– ———- ———-

201 24-SEP-02 14000 NEW SAL

202 24-SEP-02 7000 NEW SAL

C.

EMP_ID LOG_DATE NEW_SALARY ACTION

———- ——– ———- ———-

201 24-SEP-02 NEW SAL

202 24-SEP-02 NEW SAL

D. No rows are inserted.

Answer:  B

106. Examine this code:

CREATE OR REPLACE FUNCTION gen_email_name

(p_first VARCHAR2, p_last VARCHAR2)

RETURN VARCHAR2

IS

v_email_name VARCHAR (19) ;

BEGIN

v_email_bame := SUBSTR(p_first, 1, 1) || SUBSRE(p_last, 1, 7) ||

RETURN v_email_name;

END

/

Which two statements are true?

A. This function is invalid.

B. This function can be used against any table.

C. This function cannot be used in a SELECT statement.

D. This function can be used only if the two parameters passed in are not bull values.

E. This function will generate a string based on 2 character values passed into the function.

F. This function can be used only on tables where there is a p_first and p_last column.

Answer:  D, E

107. Examine the code examples. Which one is correct?

A. CREATE OR REPLACE TRIGGER authorize_action BEFORE INSERT ON EMPLOYEES

CALL log_exectution; /

B. CREATE OR REPLACE TRIGGER authorize_action BEFORE EMPLOYEES INSERT

CALL log_exectution;

C. CREATE OR REPLACE TRIGGER authorize_action BEFORE EMPLOYEES INSERT

CALL log_exectution;

D. CREATE OR REPLACE TRIGGER authorize_action CALL log_exectution; BEFORE INSERT

ON EMPLOYEES; /

Answer: A

108. Which of the following statements about LOB are true? (Choose Three)

A. LOB is a database object

B. LOB represents a data type that is used to store large, unstructured data.

C. LOB can be stored inside or outside a database.

D. Internal LOB is a category of LOB.

Answer: B, C, D

109. Examine the following statement:

CREATE OR REPLACE TRIGGER Check_sal BEFORE UPDATE OF SALARY ON

EMPLOYEES for each ROW

WHEN (NEW.salary < OLD. Salary OR NEW.Salary > OLD.salary * 1.2)

BEGIN

RAISE_APPLICATION_ERROR(-20004,’You cannot increase salary by more than 10% nor

can you decrease it’);

END;

What will happen when you execute the statement?

A. the statement will fail because the OLD and NEW qualifiers are not prefixed with a colon (:).

B. the statement will fail because a trigger cannot be defined on a particular column of a table.

C. The statement will execute successfully and the trigger will be created.

D. The statement will execute successfully and the trigger will be created, but the trigger will fail

when the salary column of the Employees table is updated.

Answer: C

110. You work as an application developer for Dolliver Inc. The company uses an oracle

database. You own subprograms that reference to other subprograms on remote locations.

Oracle server uses the signature mode of remote dependency in order to manage remote

dependencies among the subprograms. Which of the following statements about the signature

mode of dependency are true? (Choose two)

A. Oracle Server records only the signature for each PL/SQL program unit.

B. Using the signature mode prevents the unnecessary recompilation of dependent local procedures, as

it allows remote procedures to be recompiled without affecting the dependent local procedures.

C. Signature mode is the default mode of remote dependency.

D. Oracle server records both the timestamp and the signature for each PL/SQL program unit.

Answer: B, D

111. You work as an application developer for federal Inc. the company uses an Oracle

database. You have created a function named My_Func in the database. You want to change the

arguments declared for the function. Before changing the arguments you want to see the names

of the procedures and other functions that invoke the My_Func function. Which of the following

data dictionary views will you query to accomplish this? (choose two)

A. USER_DB_LINKS

B. ALL_DEPENDENCIES

C. USER_DEPENDENCIES

D. USER_SOURCE.

Answer:  B, C

112. You work as an application developer for federal Inc. the company uses an oracle database.

The database contains a package named G_Comm. You want to remove the package

specification from the database while retaining the package body. Which of the following

statements will you use to accomplish this?

A. DROP Package G_Comm;

B. DROP Package Specification G_Comm;

C. DROP Package Body G_Comm;

D. You cannot accomplish this;

Answer D

113. Which of the following Oracle supplied package is used to enable HTTP callouts from

PL/SQL and SQL to access data on the Internet?

A. DBMS_DDL

B. UTL_HTTP

C. UTL_SMTP

D. UTL_URL

Answer: B

114. If there is any changes applied to the package specification or body of a stored sub-program

which statement is true about it?

A. Package Specification only requires recompilation

B. Package body only requires recompilation

C. both package & body requires recompilation

D. both package & body does not require recompilation.

Answer: A

115. You disabled all triggers on the EMPLOYEES table to perform a data load. Now, you need

to enable all triggers on the EMPLOYEES table.

Which command accomplished this?

A. You cannot enable multiple triggers on a table in one command.

B. ALTER TRIGGERS ON TABLE employees ENABLE;

C. ALTER employees ENABLE ALL TRIGGERS;

D. ALTER TABLE employees ENABLE ALL TRIGGERS;

Answer:  D

116. Examine this code:

CREATE OR REPLACE STORED FUNCTION get_sal

(p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)

RETURN NUMBER

IS

v_salary NUMBER;

v_raise NUMBER(8,2);

BEGIN

SELECT salary

INTO v_salary

FROM employees

WHERE employee_id = p_employee_id;

v_raise := p_raise_amt * v_salary;

RETURN v_raise;

END;

Which statement is true?

A. This statement creates a stored procedure named get_sal.

B. This statement returns a raise amount based on an employee id.

C. This statement creates a stored function named get_sal with a status of invalid.

D. This statement creates a stored function named get_sal.

E. This statement fails.

Answer:  E

117. Examine this code: CREATE OR REPLACE PACKAGE metric_converter IS c_height

CONSTRAINT NUMBER := 2.54; c_weight CONSTRAINT NUMBER := .454; FUNCTION

calc_height (p_height_in_inches NUMBER) RETURN NUMBER; FUNCTION calc_weight

(p_weight_in_pounds NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE

PACKAGE BODY metric_converter IS FUNCTION calc_height (p_height_in_inches

NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * c_height; END

calc_height; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER IS

BEGIN RETURN p_weight_in_pounds * c_weight END calc_weight END metric_converter; /

CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER) RETURN

NUMBER IS BEGIN RETURN p_height_in_inches * metric_converter.c_height; END

calc_height; / Which statement is true?

A. If you remove the package specification, then the package body and the stand alone stored function

CALC_HEIGHT are removed.

B. If you remove the package body, then the package specification and the stand alone stored function

CALC_HEIGHT are removed.

C. If you remove the package specification, then the package body is removed.

D. If you remove the package body, then the package specification is removed.

E. If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER

package body and the package specification are removed.

F. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged

function.

Answer:  C

118. Procedure PROCESS_EMP references the table EMP.

Procedure UPDATE_EMP updates rows if table EMP through procedure

PROCESS_EMP.

There is a remote procedure QUERY_EMP that queries the EMP table

through the local procedure PROCESS_EMP.

The dependency mode is set to TIMESTAMP in this session.

Which two statements are true? (Choose two)

A. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP

table is invalidated.

B. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles,

UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

C. If the signature of procedure PROCESS_EMP is modified and successfully recompiles,

UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

D. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles,

QUERY_EMP gets invalidated and will recompile when invoked for the first time.

E. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles,

QUERY_EMP gets invalidated and will recompile when invoked for the second time.

Answer:  B, E

119. When using a packaged function in a query, what is true?

A. The COMMIT and ROLLBACK commands are allowed in the packaged function.

B. You can not use packaged functions in a query statement.

C. The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the

table that is being queried.

D. The packaged function can execute and INSERT, UPDATE, or DELETE statement against the

table that is being queried if it is used in a subquery.

E. The packaged function can execute an INSERT, UPDATEM or DELETE statement against the

table that is being queried if the pragma RESTRICT REFERENCE is used.

Answer:  C

120. Which three are true regarding error propagation? (Choose three)

A. An exception cannot propagate across remote procedure calls.

B. An exception raised inside a declaration immediately propagates to the current block.

C. The use of the RAISE; statement in an exception handler reprises the current exception

D. An exception raised inside an exception handler immediately propagates to the enclosing block.

Answer:  A, C, D

Thanks

Sajal

Apps Interview Ques and Ans 2

Q1: Difference between customization, enhancement and implementation?

Ans: Customization: Customization is the developing of the forms, reports and SQL script from the beginning or changing the existing.

Enhancement:  Enhancement is the modification of forms & Other components according to client user requirement.

ImplementationImplementation is the testing of Applications.

 Q2: What are the Types of Customizations?

Ans: There are two types of customizations.

1). Customization by extensions

2). Customizations by Modifications.

Customization by extensions:  Customization by extension means developing new:

Component for existing Oracle applications and develop new application using the

Development feature of AOL (Application object Library).

Customization by extensions means Copying an Existing Oracle Application Component (Forms, Report, PL/SQL etc.) to a custom application directory and modifying the Copy.

Customizations by Modifications:  Modifying   existing oracle application Component to meet your specific Requirement.

Q3:  What are the most Common Types of Customization?

Ans:      TYPE 1:      # Changing Forms:

1)      Changing Forms

2)     Validation logic

3)      Behavior

TYPE2:      # Changing Report or Program

                                              1)    Appearance

                                              2)    Logic

TYPE3:   # Database Customizations:

                                              1)    Adding read only Schema

                                              2)   Augment (add) logic with database Triggers.

TYPE4:     # integrating third Party Software

 

Q4:  What is Legacy system?

Ans: System other than Oracle is legacy System. Like FoxPro, spreadsheet.

Q5:  What is ERP?

Ans: Resource Planning with in Enterprise. ERP is a term that covers whole Product line. ERP means integration of different module. Any business will greatly benefits by

adapting this feature because u can customize it or integrate it with other Packages         to satisfy unique requirements.

BENEFITS OF ERP: 1)Flow of Information Effectively.

2) Maintaining Standardizations.

Q6:  What is Oracle Apps ?

Ans:  Oracle-apps is an ERP Package. The Key Feature of all the oracle-Application

module is Data Integration.

Master data is Integrated: All the application share common files of customers, suppliers, employee, items and other entities that are used by multiple applications.

Transaction data is Integrated: Oracle automatically bridge transactions from one system to another.

Financial data is integrated: Financial data is carried in a common format, and financial data is transmitted from one application to another.

 Q7:  What is ad-hoc Report?

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

Q8:  What is Localization?

Ans: Localization is designed to meet the specific needs of certain territories or countries. Most localization is necessary because the local laws or accountings practice differ from country to country.

Region of Localization:  Three Region of Localization.

1) EMEA REGION: Europe, Middle East, Asia pacific and Africa.

2) America REGION: Canada plus Latin America.

3)Global REGION: localization that applies territories through the world.  For example

Localization used in both Europe and Latin Americaare classified in the Global Region.

Q9: Library used in Localization?

Ans: #Globe: Globe library allows Oracle Application developer to incorporate global

Or regional feature into oracle application forms without modification of

The base Oracle Application forms.

# JA:      JA library contains codes specific to Asia\Pacific Region. And is called

Globe Library.

# JE:      JA library contains codes specific to EMEA Region. And is called

By Globe Library.

# JL:      The JL Library contains code specific to Latin America Region.

And is called by Globe Library.

Q10:  How forms are attached.

Ans: STEP- ONE: First put the form in corresponding module like AP, AR, GL

In appropriate server directory.

STEP-TWO:     Second step register form with AOL.

STEP-THREE: Attach form with Function.

STEP-FOUR:   Attach function with menu.

STEP-FIVE:     Attach menu with responsibility.

STEP-SIX:        Attach responsibility to user.

Q11: How Report is attached.

Ans11: STEP- ONE: Register the application.

STEP-TWO:   Put Report in appropriate server directory.

STEP-THREE: Define Executables. (NavigatoràConcurrentàProgram

àExecutables)

          STEP-FOUR:   Define Program (Concurrent à Program à Define)

          STEP_FIVE:     Define Responsibility (Sysadmin responsibility).

(SecurityàResponsibilityà Define).

 STEP-SIX:        Define Request Group. (Navigatoràsecurity

àResponsibilityàRequest)

STEP-SEVEN: Define Data Group. (Navigatorà oracleàData group).

STEP-EIGHT: Run the request through SRS. A request Id is created

Through which u can view the request.

Q12:  What is workflow?

Ans:   To automate and continuously increase business process we use workflow.

Workflow processes represent business process flows and information routings.

Main Function:

1). Routing Information’s (sending or receiving information).

2). Defining & modifying Business Rule.

3). Delivering electronic notification. (By emails).

Q13: What is main workflow Component?

Ans13:   1). Workflow Builder. Workflow is the component that provides user interface For creating, reviewing and maintaining workflow Definitions.

2). Workflow Engine.:workflow is the component that executes and enforces The defined workflow Process.

3). Workflow Monitor Workflow is the component of oracle workflow that

Allow you to review the state or status of an item through any particular workflow process.

4). Workflow Definition Loader:  allows u to download the text file.

5). Workflow Directory Services: Tells workflow how to find users.

6). Notification System: Send emails and receives responses from the Oracle Workflow notification system.

Q14: What are Interface table in AP, AR & GL?

Ans:

AP INTERFACE TABLE:

1) AP_INTERFACE_CONTROLS.

2) AP_INTERFACE_REJECTIONS

3) AP_INVOICE_INTERFACE

4) AP_INVOICE_LINES_INTERFACE.

AR INTERFACE TABLE:

1) AR_PAYMENTS_INTERFACE_ALL

2) AR_TAX_INTERFACE

3) HZ_PARTY_INTERFACE

4) HZ_PARTY_INTERFACE_ERRORS

5) RA_CUSTOMERS_INTERFACE_ALL

6) RA_INTERFACE_DISTRIBUTIONS_ALL

7) RA_INTERFACE_ERRORS_ALL

8) RA_INTERFACE_LINES_ALL

9) RA_INTERFACE_SALESCREDITS_ALL

GLINTERFACE TABLE:

1) GL_BUDGET_INTERFACE

2) GL_DAILY_RATES_INTERFACE

3) GL_IEA_INTERFACE

4) GL_INTERFACE

5) GL_INTERFACE_CONTROL

6) GL_INTERFACE_HISTORY

Q15 Total numbers of Tables in AP, AR, GL?

Ans;

AP 173

AR 294

GL 165

FA 160

PO 132

OE 109

Q16: How will u customize a form?

Ans: STEP1: Copy the template.fmb and Appstand.fmb from AU_TOP/forms/us.

Then put in custom directory. The libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, JL, VERT) are automatically attached.

STEP2:         Create or open new Forms. Then customize.

STEP3:        Save this Form in Corresponding Modules.

Q17:   What are non-financial modules?

Ans:  

1) Projects

2) Manufacturing

3) Supply chain management

4) HR

5) Front Office

6) Strategic Enterprise management.

Q18: Explain Order- cycle in OE.

Ans: Step1: Enter sales order.

Step2: Book the sales order.

Step3: Pick release order.

Step4: Ship or confirm order.

Step5: Backorder Release

Step6:  Receivable Interface

Step7: Complete line

Step8: Complete order

Q19: What is AU_TOP.

Ans: This is the Application utility contains PL/SQL library used by oracle forms, reports, oracle form source files and a copy of all Java used to generate the desktop Client.

Q20:  What is ad_top?

Ans: ad_top (Application DBA). Contain installation and maintenance utility.

Such as Auto upgrade, Auto Patch and Admin Utility.

Q21: Can we make transaction in close Periods?

Ans: No, we can make only reports.

Q22: If Period is closed how we can enter transactions? (Doubt)

Ans:   No, we cannot enter transaction.

Q23: what is SQl*Loader?

Ans: This tool is used to move data from a legacy system to oracle database.

In this two type of inputs to be provided to SQL * Loader.

First is data file, containing the actual data.

Second is the control file containing the specification which drive the

SQL* Loader.

Q24: How can u relate order management with AR?

Ans: sales orders are displayed after confirm release of sales in order management.

Q25:  What is the Field of GL_interface?

Ans:

1) SET_OF_BOOKS_ID

2) ACCOUNTING_DATE
3) CURRENCY_CODE
4)DATE_CREATED

5) CREATED_BY
6) CURRENCY_CONVERSION_DATE

7) ENCUMBRANCE_TYPE_ID
8) BUDGET_VERSION_ID
9) CURRENCY_CONVERSION_RATE

10) ACCOUNTED_DR
11)ACCOUNTED_CR
12)TRANSACTION_DATE

Q26: In which directory u store your custom form?

Ans:

App_Top is top directory. We have Core directory Adm., ad (application dba),

Au (application utility), fnd (Foundation), Cust-Dem is Custom directory where

Have 11.0.28 version then we have forms directory. Inside the form we have US

Directory. Where we stand forms.

Q27: Who is Holder of Alerts?

Ans:  ALERT Manager.

Q28: Steps for upgradation of 11 to 11i?

STEP1: Perform category 1,2,3. (Preupgrade steps).

 STEP2:   Run auto grade

STEP3:  Apply database patch to bring your database to the Current oracle apps release level.

STEP4: Install online help (optional).

STEP5: Perform Category 4, 5, 6 Steps (Post-upgrade steps).

STEP6: Perform product specific implementation steps as listed in your products Users guide.

STEP7: perform upgrade finishing step.

Q28: How interface program is written and for what purpose

Ans28: Interface Program is written through SQL, PL/SQL.

PURPOSE: 1)Basic Integration

2) Imports valid data that is meaningful to Organization

3) Validate the integrity of any data Before introducing into oracle apps.

4) Imports data from legacy system.

5)  Import data from one module to another.

Q29: What is AOL.

Ans:  AOL stands for Application Object Library used for customization And implementation of forms and Reports.

Q30: which Columns are taking care of descriptive flex fields?

Ans: Attribute Columns

Q31: Can u attach two sets of books with single profile?

Ans: yes we can attach.

Q32:  How U Can u attaches two sets of books with single profile.

Ans:  we can attach different set of Books with different responsibility In a single profile.

Q33: can we run FSG report other than GL?

Ans: No, we cannot run. Because FSG reports can only run in GL.

Q34: What are the common libraries in AOL.

Ans34: libraries contain reusable client-side code.

Common Libraries in AOL.

FNDSQF: Contain packages for procedures for Message Dictionary, Flex fields, profiles, and concurrent processing’s.

APPCORE: Contain packages for procedures for Menus and Toolbar.

APPDAYPK: contain packages that control application Calendar.

APPFLDR: packages for Folder.

Qns35:  What is Multilanguage support.

Ans35: Oracle Application provides some feature to support multi language support.

Qns36: Can u delete the posted Journals? Can U make Changes in Posted Journals?

Ans36: No, once the posting program in oracle financial has updated accounts balances, you cannot alter the posted journals; you can only post additional entries that negate the original values. These entries contain either the negative values of the original posted amounts or the original values but with the debit amounts and credit amounts reversed.

These approaches are known as reversal method.

Qns37: When u r taking bulk of reports.

Ans37: At midnight because traffic is less.

Qns38: Who is Holder of Alerts?

Ans38: Alert Manager.

Qns39: What is TOAD.

Ans39: Tool for managing database activity,

Qns40: What is Flexfield?

Ans40: Oracle Application uses Flexfield to capture information about

Your organization. Flexfield have flexible structure for storing key information.

Like Company, Cost Center, and Account. They also give u highly adaptable

Structure for storing customized information in oracle Applications.

Qns41: What are the elements of Flex field?

Ans41:  1) Structure            2) Segment  3) Segment value   4) Value set


Qns42: What do u means by structure?

Ans42:  Structure as the name implies defines how Flexfield is constructed. A Flex field    structure determines how many Segments it has, as well as how the segments are sequenced. Each structure is mapped to a structure ID Column in the database table for key Flexfield. Each Structure is mapped with context sensitive column in the database table for descriptive Flexfield.

Qns43:  What do u means by Segment?

Ans 43: Each Segment represents an element of your business structure Such as Employee, Cost Center, Account. A Flexfield can have Multiple Field. A segment is a single field with in a Flexfield.

Qns44: What do u means by Value set?

Ans 44: Value set identifies a list of valid value for the segment. Value set also governs the segment value’s length, its data type.

Qns45: What do u means by Segment value?

Ans45:  Value for each segment of flex field.

Qns46: What is Key and Descriptive Flexfield.

Ans46: Key Flexfield: #unique identifier, storing key information

# Used for entering and displaying key information.

For example Oracle General uses a key Flexfield called Accounting Flexfield to uniquely identifies a general account.

Descriptive Flexfield: # To Capture additional information.

# To provide expansion space on your form

With the help of []. [] Represents descriptive flexfield.

Qns47: Difference between Key and Descriptive Flexfield?

Ans47:

Key Flexfield Descriptive Flefield
1. Unique Identifier 1.To capture extra information
2. Key Flexfield are stored in segment 2.Stored in attributes
3.For key flex field there are flex field Qualifier and segment Qualifier 3. Context-sensitive flex field is a featureof DFF.(descriptive flex field)

 

Qns48: Difference between Flexfield Qualifier and Segment Qualifier.

Ans48:  Flexfield qualifier is used to identify a particular segment within a Key flexfield. While segment qualifier is used to capture value for any particular Segment.

Qns49:       What is Cross Validation Rule?

Ans 49: To prevent users from entering invalid combinations of segments Oracle General Ledger allows u to set up cross validation rule. There are two types of cross-validation

Rule element: include and exclude.   For example, to secure a balance sheet account to be associated with the balance sheet cost center or the corporate cost center only,U must include every possible combination then exclude the balance Sheet account range for the cost center.

Qns50:    Purpose of Cross Validation rule.

Ans50:  u can use Cross Validation rule to perform certain validations in your            Accounting flex field. For example, u can use Cross Validation rule  To secure all balance sheet account to be associated only with the balance Sheet cost center, corporate cost center and profit and loss account to be associated with the specific cost center other than the corporate Center.

Qns51:  What are types of segment for Descriptive Flexfield.

Ans51: Two types

1). Global segments

2). Context-sensitive segment.

Global Segment: global segment maps one to one to a database column.

DFF segment stored in ATTRIBUTE. Global segment always Displayed in a descriptive flex field.

 Context-Sensitive Segment: Context sensitive segment can share a single database

Column because the context – sensitive will be  Mutually exclusive and will never overlap.

Qns52:  What is Key Flexfield in AP, AR, GL.

Ans52: Key Flexfield in GL: Accounting Flexfield.

Accounting Flexfield is chart of account flex field.

It is used for identifying an account combination.

It must have a balancing segment, cost center segment, Natural account segment.

Combination table in Acct. FF: GL_CODE_COMBINATION_ID.

Structure column: chart_of_accounts_id.

Maximum number of Segments: 30.

Key flex field in AR: 1). Sales Tax Location Flexfield.

2) Territory Flexfield

Sales Tax Location Flexfield: to calculate sales tax.

Combination table: AR_LOCATION_COMBINATION

Max number of segment: 10

Territory Flexfield: This is used to group territories according to company  needs

Combination table: RA_TERRITORIES.

Qns53:  What is purpose of Token Field.

Ans53: To define parameter name defined in oracle reports.

Qns54: What is Template form?

Ans54  Template form is the starting point for all development of new form.

Start developing new form by copying template.fmb file located in

AU_TOP/forms/us to local directory and renaming it as appropriate.

Template Form Contains

–Several libraries like FNDSQF, APPDAYPK, and APPCORE.

–STANDARD_TOOLBAR, STANDARD_CALENDER

–Several form level trigger with required code.

Qns55: What are Handlers?

Ans55: Oracle application uses group of packaged procedure called handlers,

To organize PL/SQL code in the form so that it is easier to develop,

Maintain and debug.

Types Of handler: 1). Item handler

2). Event handler

3). Table handler.

Item handler: An item handler is a PL/SQL Procedure.

That encapsulates all of the code that acts upon an item.

Event handler: An item handler is a PL/SQL Procedure.

That encapsulates all of the code that acts upon an event.

Table handler: An item handler is a PL/SQL Procedure.

That manages interaction between block and base table.

Qns56: What is Appstand Form.

Ans56: Appstand form contains the Following.

1) Object Group STANDARD_PC_AND_VA.

Which contain the visual attribute and property class.

2) Object group STANDARD_TOOLBAR which contains the windows

Canvasses blocks and item of application toolbar.

3) Object group STANDARD_CALENDER which contains the windows

Canvasses blocks and item of application calendar.

4) Object groups QUERY_FIND, which contains a window, blocks and item

Used as a starting point for coding a find window.

Qns56: What is set of books.

Ans56: A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar. You must define at least one set of books for each business location.

Qns57: what are four options that are tied to defined set of books.

Ans57: 1. Standard option (supenseposting, automatic posting, Average balance posting)

2). Average Balance option.

3). Budgetary control option.

4). Reporting Currency option.

Qns58: What is FSG.

ns58: A powerful and flexible tool you can use to build your own custom

Reports without programming.

Qns59:  What are the components of FSG?

Ans59: 1) Row set

2) Column set

3) Row order

4) Display set

5) Content set.

Qns60: What is MRC.

Ans60: The Multi Reporting Currency Feature allows u to report and maintain records at the transaction level in more than one  Functional currency. You can do by defining one or more set of books in adition to primary set of books.

Qns61:  What are Alerts.

Ans61: Oracle alert is an application module that reports exception actions based on  detected exceptions. U can create alert when specific event occur or that run periodically. Oracle alert provides a reliable way to monitor database activity. As well as keeping u informed of unusual condition. We can monitor your business performance through alerts.

Qns62:  Types of alerts?

Ans62:  Two types of alerts.

1.      Event alert

2.       Periodic Alert

Event alerts: An event alert is a database trigger that notifies u when a specified database event occurs and a particular condition is met.

Periodic event: A periodic alert on the other hand is not immediate.It is executed according to a predefined frequency

Qns63: What are three alert action types?

Ans63:1.Detail(An action defined atdetail level is initiated once for each exception found   – Meaning once for each row returned by the select statement in the alert definition.

2). Summary (An exception defined at the summary level is initiated  Once for all exceptions found or once for each unique output combination.)

3).  No Exception (An action defined at the no-exception level is initiated once if no data is returned from the select statement)

Qns64: What are the advantages of alert.

Ans64: 1)   Integration with email.

2)  Automatic processing

3)  Performing routine transactions

4)  Maintaining information flow without a paper trail.

Qns65: What is Currency.

Ans65:  Two types of Currency.

1) Foreign Currency: A currency that you define for your set of books for recording and conducting accounting transactions in a currency other than your functional currency

2) Functional Currency: The principal currency you use to record transactions and maintain accounting data within General  Ledger. The functional currency is usually the Currency in which you perform most of your Business transactions. You specify the functional currency for each set of books in the Set of  Books window.

Qns66: Types of matching.

Ans66: Two way Matching: The process of verifying that purchase order and invoice information matches within accepted tolerance levels. Payables uses the following criteria to verify two-way matching:
Invoice price <= Order price
Quantity billed <= Quantity ordered

Three way matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables uses the following criteria to verify three-way matching:
Invoice price <= Purchase Order price
Quantity billed <= Quantity ordered
Quantity billed <= Quantity received

Four way Matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables uses the following criteria to verify four-way matching:
Invoice price <= Order price
Quantity billed <= Quantity ordered
Quantity billed <= Quantity received
Quantity billed <= Quantity accepted

Qns67: What is the difference between Master table, setup table, and transaction table.

Ans 67: Master table: Created in any module and accessible across the application.

Like GL_CODE_COMBINATIONS, GL_SET_OF_BOOKS.

Transaction –Table: transaction tables are tables that store day-to-day transaction

Data. Such as payable invoice, receivable invoice.

Set-Up table: Created once with in Application. Like FND_CURRENCY.

Qns68: Name Few Master tables, Set up table I, transaction table in AP, AR, GL.

Ans68:

Module Name Master table setup table Transaction table

GL

1.GL_SET_OF_BOOKS2.GL_CODE_COMBINATIONS FND_CURRENCY GL_JE_LINESGL_JE_HEADRES

GL_JE_BATCHES

GL_interface

GL_CONSOLIDATION

GL_SUSPENSE_ACCOUNTS

GL_INTERCOMPANY_ACCOUNTS

AP

PO_VENDORSAP_BANK_BRANCHES

PO_VENDOR_SITES
AP_HOLD_CODES

FND_CURRENCY AP_BATCHES_ALLAP_INVOICE_ALL

AP_DISTRIBUTION_ALL

AP_CHECKS_ALL

AP_PAYMENTS_HISTOTRY_ALL

AR

HZ_CUST_ACCOUNT FND_CURRENCY AR_ADJUSTEMENT_ALL
AR_PAYMENTS_SCHEDULE_ALL
AR_CASH_RECEIPT_ALL
AR_DISTRIDUTION_ALL
AR_RECEIVABLE_APPLICATION_ALL.

 

Qns69: What do u means by FIFO pick and FIFO ship.

Ans69: FIFO pick: First in first out. (Order comes from customer).

FIFO ship: order ship to customer.

 Qns70: Difference between SC and NCA.

Ans70:

SC NCA
1. SMART CLIENT 1. Network computing Architecture
2. No form server in SC. All form is in directory, which is on the client. 2. Forms are in the server. Thus making security higher.

 

Qns71: What is first step in GL.

Ans71: Creating chart of account.

Qns72: What are standard reports in GL?

Ans72: Trial Balance Report

Journal Report

FSG REPORT

Account Analysis Report.

Qns73: What are standard reports in AP?

Ans73: 1.  Supplier Report

2). Payment Report

Qns74: What are standards reports in AR.

Ans74:              1. Tax Report         2.  Customer Profile Report   3.      Aging Report       4. Dunning Letter Report

Qns75.What are customer table, transaction table, and Receipt table in AR.

Ans

Module Customer Table Transaction Table
AR HZ_CUST_PROFILE_CLASSHZ_CUST_PROF_CLASS_AMTS

HZ_CUSTOMERS_PROFILES

HZ_CUST_PROFILE_AMTS

HZ_CUST_ACCOUNTS

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_CONTACT_POINTS

HZ_CUST_ACCT_RELATES_ALL

HZ_CUST_SITES_USES_ALL

 

RA_CUTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALLRA_CUST_TRX_TYPES_ALL
RA_CUST_TRX_LINE_SALESREPS_ALL

 

RECEIPT Table
AR_CASH_RECEIPTS_ALLAR_RECEIPT_METHOD

AR_CASH_RECEIPT_HISTORY_ALL

AR_INTERIM_CASH_RECEIPT_ALL

 

Qns76: What is Custom-Library.

Ans76: The custom library allows extension of oracle application without modification

of oracle application code. U can use the custom library for customization  Such as zoom (moving to another form), enforcing business rule (for example Vendor name must be in uppercase letters) and disabling field that do not apply for your site.

Custom library is placed in AU_TOP / resource directory.

Event Passed to Custom-Library:

1). WHEN_FORM_NAVIGATE

2). WHEN_NEW_FORM_INSTANCE

3). WHEN_NEW_BLOCK_INSTANCE

4). WHEN_NEW_RECORD_INSTANCE

5). WHEN_NEW_ITEM_INSTANCE.

Qns78: What is the Component of alerts.

Ans78:  1. Message

2.SQL SCRIPT

3.Operating system script

4. Concurrent request.

Qns79: What is difference between charge back and adjustment?

Ans79:

CHARGEBACK ADJUSTMENT
A new debit item that u assign to your customer closing an existing, outstanding debit item. A receivable feature that allows u to increase or decrease the amount due of your invoice, debit memos, charge back.

Qns80: What are types of invoice?

Ans80:

TYPES OF INVOICES
NINE Type:StandardCredit memoDebit memoExpense Report

PO default

Prepayment

Quick match

Withholding tax

Mixed

Qns81: What are sub modules in Financials?

Ans81:

Sub module in Financials
GL
AP
AR
FA
CM (cash management)
Financial Analyzer

Qns82: Concept of Multiorganisation, Explain?

Ans82:   Multi organization allows u to setup multiple legal entities within a single installation of oracle applications.

ARCHITECTURE OF MULTIPLE ORGANISATIONS

SET OF BOOKS : Within one set of books u may define one or more legal entities.

LEGAL ENTITY: each legal entity has its own employer tax identification number.

And prepare its own tax forms. Each legal entity has its own Tax forms. Each legal entity has its own set of federal tax rule, State tax rule and local tax rule. Legal entities consist of one or More operating units.

OPERATING UNIT: operating units’ represents buying and selling units with in your Organization. Oracle order Entry, Oracle receivables, Oracle Purchasing,  And Oracle Payables.

INVENTORY ORGANIZATION:  an Inventory organization is a unit that has inventory transactions. Possibly manufactures and or distribute products.

Qns83: How will u attach SOB?

Ans83: STEP1: Create a new Responsibility.

STEP2: Attach the new responsibility to an existing user.

STEP3: Defining a new Period Type.

STEP4: Defining an accounting calendar.

STEP5: Defining a set of books.

STEP6: Attach the set of books to your responsibility.(NàProfileàSystem)

STEP7: Signing on as new responsibility.

Qns84: What are key functions provided by Oracle General Ledger?

Ans84:

Function Provided by GL
General AccountingBudgetingMultiple CurrenciesIntercompany AccountingCost Accounting

Consolidation

Financial Reporting

Qns85: What do u means by cost center?

Ans85: COST center gives the information about investment and returns on different projects.

Qns86: what is Fiscal Year.

Ans86: Any yearly accounting Period without relationship to a calendar year.

Qns87: What is Credit-memo?

Ans87: A document that partially or reverse an original invoice.

Qns88: How data is transferred from legacy system to Oracleapps table.

Ans88: A system other than oracle apps system is called legacy System.

Qns89: What is Chart of Accounts?

Ans89:  The account structure your organization uses to record transaction and maintain account balances.

Qns90: What are different types of budgets?

Ans90:

Types of Budgets
OperatingCapitalMaster Production ScheduleVariableTime-Phased

Qns91: How others modules are integrate through GL.

Ans91:  Integration of module With GL

Qns92: Explain Payable Cycles

Ans92: Four steps in AP Cycle

PAYABLE CYCLE                  

Four steps in Payable Cycles:

STEP1: Enter Invoice (this process may or may not include matching each invoice with PO).

STEP2: Approve invoice payment.

STEP3: Select and pay approval invoices.

STEP4: Reconcile the payment with bank statement

Qns95:  AGING BUCKETS?

A.  Time periods you define to age your debit items. Aging buckets are used in the Aging reports to see both current and outstanding debit items. For example, you can define an aging bucket that includes all debit items that are 1 to 30 days past due.

Payables uses the aging buckets you define for its Invoice Aging Report

Q96. CREDIT INVOICE?

A.  An invoice you receive from a supplier representing a credit amount that the supplier owes to you. A credit invoice can represent a quantity credit or a price reduction.

Q97. CREDIT MEMO?

A document that partially or fully reverses an original invoice.

Q98.CUTOFF DAY?

The day of the month that determines when an invoice with proximate payment terms is due. For example, if it is January and the cutoff day is the 10th, invoices dated before or on January 10 are due in the next billing period; invoices dated after the 10th are due in the following period.

Q99. DEBIT INVOICE?

A.  An invoice you generate to send to a supplier representing a credit amount that the supplier owes to you. A debit invoice can represent a quantity credit or a price reduction.

Q100. JOURNAL ENTRY HEADERS?

A.   A method used to group journal entries by currency and journal entry category within a journal entry batch. When you initiate the transfer of invoices or payments to your general ledger for posting, Payables transfers the necessary information to create journal entry headers for the information you transfer. Journal Import in General Ledger uses the information to create a journal entry header for each currency and journal entry category in a journal entry batch. A journal entry batch can have multiple journal entry headers.

Apps Interview Ques and Ans

1) 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.

2) Tell me some thing about SQL-LOADER.

Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.

Sql * loader supports various load formats, selective loading, and multi-tables loads.

1) conventional

–The conventional path loader essentially loads the data by using standard ‘insert’ statement.

2) direct

— the direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.

EX:-

My data.csv file

1001, “scott tiger”,1000,40

1002,”gvreddy”,2345,50

Load data

Infile ‘c:\data\mydata.csv’

Into table emp

Fields terminated by “,” optionally enclosed by ‘”’

(empno, empname,sal,deptno)

>sqlldr scott/tiger@vis

control=loader.ctl  log= gvlog.log  bad=gvbad.bad  discard=gvdis.dsc .

3)  how do u dump data from pl/sql block to flat files?

Using utl_file package, we can dump data from pl/sql block to flat file.

PRE-REQUIREMENTS for UTL_FILE is specify the accessible directories for the UTL_FILE function in the initialization file (INIT.ORA) Using the UTL_FILE_DIR parameters.

Ex: UTL_FILE_DIR =

EX:-

–remember to update INITSID.ORA,

–utl_file_dir = ‘c:\oradata’

Declare

Fp utl_file.file_type;

Begin

Fp := utl_file.fopen(c:\oradata’,tab1.txt’,’w’);

Utl_file.putf(fp,’%s %s \n ‘text field’, 55);

Utl_file.fclose(fp);

End;

4) What is SET-OF-BOOKS?

Collection of Chat of Accounts and Currency and Calendars is called SOB

5)     What is the interface?

Interface Table is a table which is used as medium for transfer of data between two systems.

6)     What is invoice?

Send you a request for payment

7)     What is INBOUND and OUT BOUND? (Different types of interfaces)

Inbound Interface:

For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.

Outbound Interface:

For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs

8)      Tell me what r the Base tables in the AR?

hz_parties      (party_id)     (store info about org, groups and people)

HZ_PARTIES stores information about parties such as organizations, people, and groups, including the identifying address information for the party.

 hz_cust_accounts (cust_account_id)

HZ_CUST_ACCOUNTS stores information about customer relationships. If a party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.

 hz_cust_acct_sites_all  (cust_acct_site_id)

HZ_CUST_ACCT_SITES_ALL stores information about customer sites.One customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.

 hz_cust_site_uses_all   (site_use_id)

HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.

hz_party_sites (party_site_id)

HZ_PARTY_SITES stores information about the relationship between Parties and Locations. The same party can have multiple party sites.Physical addresses are stored in HZ_LOCATIONS.

hz_locations (location_id)

HZ_LOCATIONS stores information about physical locations.

hz_Person_Profiles (person_profile_id)

HZ_PERSON_PROFILES stores detail information about people.

hz_Organization_Profiles (organization_profile_id)

HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics, socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.

9)      What r the table’s interface tables in the customer interface tables?

1)     Ra_customers_interface_all

This table stores customer, address, and business purpose information. You do not have to enter values in this table if you do not want to import customers, addresses, or business purposes.

 ADDRESS1 through 4

Enter the address for your customer in these four columns. You can enter up to four lines of an address.

Validation:

If you enter a value in ORIG_SYSTEM_ADDRESS_REF, you must enter a value in ADDRESS1. For multiple rows with the same address reference, insert values in address 1–4.

Destination:                      HZ_LOCATIONS.ADDRESS1,

HZ_LOCATIONS.ADDRESS2,

HZ_LOCATIONS.ADDRESS3,

HZ_LOCATIONS.ADDRESS4

2) Ra_customer_profile_int_all

A customer level profile must exist in A_CUSTOMER_PROFILES_INTERFACE for new customers and each bill–to business purpose.

3) Ra_contact_phones_int_all

This table stores telephone numbers for customers, addresses and contacts as well as contacts for customers and addresses.

4) Ra_customer_banks_int_all

This table stores bank information for a customer or for a specific Bill–To address, you must enter a bank account for this customer,

5) Ra_cust_paymethod_int_all

To import payment methods for customers and bill–to business purposes,

10) What r the staging tables in the customer interface?

Ra_customers_stg

Ra_customers_address_stg

Ra_customers_point_stg

Ra_customers_contact points_stg

Ra_customers_relate_stg

Ra_customer_error.

11)Tell me some mandatory columns in the customer interface tables?

a) Ra_customers_interface_all

1) Orig_system_customer_ref

2) insert_update_flag

3) customer_number

4) customer_status

5)last_updated_by

6) last_updated_date

7)created_by

8) creation_date.

b) Ra_customer_profile_int_all

1) customer_profile_class_name

c) Ra_contact_phones_int_all

1)orig_system_telephone_ref

2)telephone

3) telephone_type

         d) Ra_customer_banks_int_all

1) bank_a/c_name

2)bank_a/c_no

3)bank_a/c_currency_code

e) Ra_cust_paymethod_int_all

1) payment_method_name

12)Tell me the Navigation for customer interface?

Receivables à Interface à Customers

13. What tables cannot be updated through customer interface?

No updates will be allowed to be made through customer interface for

Following tables

RA_SITE_USES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_CUST_RECEIPT_METHODS

AP_BANK_BRANCHES

AP_BANK_ACCOUNTS_ALL

AP_BANK_ACCOUNT_USES_ALL

14. How to send additional customer and address information through customer interafce 

Here is where attribute columns are used.

* Additional Customer data can be populated in

RA_CUSTOMERS_INTERFACE_ALL.customer_attribute1 to 15

This will go into RA_CUSTOMERS.attribute1 to 15

* Additional address information can be populated in

RA_CUSTOMERS_INTERFACE_ALL.address_attribute1 to 15

This will go into RA_ADDRESSES_ALL.attribute1 to 15

And so on.

15. What should be the batch size (number of customer records) general guidelines for optimal performance

About 10,000 records per bacth is ideal, it is suggested to keep the batch

size small.

   16. Why does customer interface error out if there is mismatch in address information?

Because the code validates against these mismatches.

arplscin.sql and arplbcin.sql 

It validates the address being inserted or updated with respect to the tax

location flexfield structure.  For each row being inserted or updated in

RA_CUSTOMERS_INTERFACE, and that has not already been marked in error,

the set_location_ccid function is called to return either an existing ccid

for the address entered or to create a new entry in AR_LOCATION_VALUES for

this new address.  The RA_CUSTOMERS_INTERFACE record is then updated with the

value of the ccid returned.

17. How do you send records at customer level profile and address/site level profile?

For every customer record in RA_CUSTOMERS_INTERFACE_ALL, insert two records  in table RA_CUSTOMER_PROFILES_INT_ALL.    (Refer Note: 1070800.6 )

18. Does Customer Interface import salesperson data?

No, Refer Enhancement Bug: 147495

19. Customer Interface process can be used for updating the customer information How does the UPDATE work?

Current functionality of Customer Interface is to update all the data.  You cannot run Customer Interface to update only changed data. (Refer Bug: 879121 for the intended functinality)

20 .   Tell me what is the procedure to develop an interface?           

  1. First we will get the Requirement document.
  2. We will create control file based on that plot file.
  3. Then the control files which loads the data into staging tables.
  4. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
  5. Through the standard programs we will push the data from interface tables to Base tables.

21.     What validations u did in the customer interface?

  1. customer name : the same customer reference can’t have different customer names with in this table

HZ_PARTIES.PARTY_NAME

  1. customer number : must be null if your r using automatic customer numbering, must exit if you are not using automatic customer numbering. This value much be unique with in HZ_PARTIES
  2. customer status : must be ‘A’ for active or ‘I’ for inactive

HZ_PARTIES_STATUS

  1. bank account num or bank account currency code :

if the bank a/c already exist do not enter a value

if the bank a/c does not exist  you must enter a value

  1. bank a/c name : it must exist in AP_BANK_ACCOUNTS or if it does not exist values must exist for BANK_A/C_CURRENCY_CODE

BANK_A/C_NUM

BANK_NAME

BANK_BRANCH_NAME

Note : every interface table has two error msg

1)      Error code.

2)      Error msg.

22.      How can u call a standard interface program from sql or pl/sql code?

FND_REQUEST.SUBMIT_REQUEST (‘PO’,’EXECUTABLE NAME’,,,,PARAMETERS)

23.      API’s FOR CUSTOMER INTERFACE?

HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C

HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C

FND_PROFILES

FND_APPLICATIONS

FND_GLOBAL

FND-FILE

FND_CONCSUB(can submit conc program in host invironment)

24.      Tell me some API?

FND_FILE.PUTLINE(FND_FILE.LOG)

FND_FILE.PUTLINE(FND_FILE.OUTPUT)

Is the Program exits, delete conc program and its executables.

IF      FND_PROGRAM.PROGRAM_EXITS(‘EMP’,APPLICATION_NAME_IN) THEN

FND_PROGRAM.DELETE_PROGRAM(‘EMP’,APPLICATION_NAME_IN)

FND_PROGRAM.DELETE_EXECUTABLE(‘EMP’,APPLICATION_NAME_IN)

END;

25.  What are profile options?

Is the Functional and Technical behavior of Oracle Applications Package.

EX: – I want to assign the user3 responsibility to p4 printer then

System Administrator àProfile àSystem

                                    (FND_PROFILE_OPTIONS)

26.  Oracle E-Business suite?

Oracle apps + analytical components software.

(Oracle discover)

(Oracle sales analyzer)

(Oracle financial analyzer)

(Oracle marketing analyzer)

27. What are some of the important fields that Customer Interface does not load?

Not all fields in RA_CUSTOMERS are being loaded by the interface.

SIC_CODE

GSA_INDICATOR

FOB_POINT

SALES_CHANNEL_CODE

FREIGHT_TERM

WAREHOUSE_ID

PRICE LIST

SHIP_PARTIAL

PAYMENT_TERM_ID   in RA_SITE_USES.PAYMENT_TERM_ID

(Refer Enhancement Request Bug: 245300)

28. New TCA/Customer Model, how to load customer as PERSON or ORGANIZATION through Customer Interface?

a) Populate ra_customers_interface_all . person_flag = ‘Y’

Run Customer Interface process will load this record as PERSON

b) Populate ra_customers_interface_all . person_flag = ‘N’ ( or NULL )

Run Customer Interface process will load this record as ORGANIZATION

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

Security à responsibility à operating unit.        

c) Operating unit — secures AR, OE, AP, PA and PO Information.

d) Organizations  — is a specialize unit of work at particular locations

30. What are the User PARAMETERS in the Reports?

              P_CONC_REQUEST_ID

              P_FLEX_VALUE

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

32. PL/SQL stored procedure parameters?

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

33) 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.

 34) What r the validation types?

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.

35) Form development process?

a) open template form

b) Save as .fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas   (subclass info)

h) Assign canvas property class to the canvas

I) assign the window to the canvas and canvas to the window

j) Create a data block

k) Modify the form level properties. (sub class item à Text item)

l)  Modify the app_cusom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties ((console window, First navigation

p) Save and compile the form.

Place the .fmx in the server directory.

Q) Register in the AOL

                                    APPLICATION à FORM

                                    APPLICATION à FUNCTION

                                    APPLICATION à MENU

36)How do u customize the Reports?

  1. Identify the Short name of the standard report in which module we have to customize

Ex: – if u want to customize in the AR module path is

              Appl top\ar\11.5.0\reports\US\ .rdf

  1. Open the .rdf file in Report builder and change the name of the module.
  2. Open the data module and modify the query (what is client requirements) assign the columns to the attributes.
  3. Go to report wizard and select, what r the newly created columns.
  4.  Then Compile it. Then u will get a .rep file in the specified module. If it is not in the specified directory then we have to put in the server directory.
  5. Then Register in the AOL   Concurrent àexecutable.

                                                  Concurrent à program.

  1. g.      go to system administrator  SecurityàResponsibilityàrequest

h)  Add and assign a concurrent program to a request group

37.    Difference b/w procedure and function?
A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block.  Procedure’s parameters can have IN or OUT or INOUT parameters. But function’s parameters can only have IN parameters.
38.    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.
39.    Give some examples of pseudo columns?

NEXTVAL, CURRVAL, LEVEL, SYSDATE
40.    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.
41.    How to create a table in a procedure or function?
See the below piece of code:  Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.

DECLARE
                    L_STMT VARCHAR2(100);
BEGIN
                    DBMS_OUTPUT.PUT_LINE('STARTING ');
                    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';
                    EXECUTE IMMEDIATE L_STMT;
                    DBMS_OUTPUT.PUT_LINE('end ');
END;

The above piece of code can be written In procedure and function DDL’s can be used in function provided that function should be invoked in Begin-End block not from Select statement.

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

43.    What is the purpose of FORUPDATE?
Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

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

DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      /* Issue your own error code (ORA-20101) with your own error message. 
         Note that you do not need to qualify raise_application_error with 
         DBMS_STANDARD */
      raise_application_error(-20101, 'Expecting at least 1000 tables');
   ELSE
      NULL; -- Do the rest of the processing (for the non-error case).
   END IF;
END;
/

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

45.    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.
46.    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.

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

48.    What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

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

50.    What is the difference between ref cursors and normal pl/sql cursors?

Declare

          type rc is ref cursor;
          cursor c is
          select * from dual;
          l_cursor rc;
       begin
          if ( to_char(sysdate,'dd') = 30 ) then
                   open l_cursor
                             for select * from emp;
          elsif ( to_char(sysdate,'dd') = 29 ) then
                   open l_cursor
                             for select * from dept;
          else
                   open l_cursor
                             for select * from dual;
          end if;
          open c;
      end;

Given that block of code you see perhaps the most “salient” difference, no matter how many times you run that block The cursor C will always be select * from dual.  The ref cursor can be anything.