25+ Oracle Apps-Technical Interview Questions [ 95% SUCCESS ] | 2020
Oracle Applications-Technical Interview Questions and Answers

25+ Oracle Apps-Technical Interview Questions [ 95% SUCCESS ]

Last updated on 04th Jul 2020, Blog, Interview Questions

About author

Arul (Sr Technical Director )

He is a Expertise in Following Technical Domain with 9+ Years of Experience Also, His Informative Technical Writing Blogs Helps Freshers & JOB Seeker to Enhance their Career

(5.0) | 17212 Ratings 5097

These Oracle Applications-Technical Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of Oracle Applications-Technical.As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer.we are going to cover top 100 Oracle Applications-Technical Interview Questions along with their detailed answers. We will be covering Oracle Applications-Technical  scenario based interview questions,Oracle Applications-Technical Interview Questions for freshers as well as Oracle Applications-Technical interview questions and answers for experienced. 

Q1. Explain about different Flexfields in oracle financials?

Ans:

Assets: Location flexfield, Asset Key flexfield, Category flexfield

General Ledger: Accounts related flexfield

Service: Item flexfield

Inventory: System items flexfield, item categories flexfield, item catalogs flexfield, sales orders flexfield

Receivables: Sales tax and location flexfield, Territory flexfield.

Q2. What does dynamic insert signify?

Ans:

This is the basic Oracle Apps Technical Interview Question asked in an interview. It is a common feature of Oracle applications which allows the different kind of users to enter new account combinations by making use of any window/form. In a case where this feature is not available or disabled, the user is unable to create different account combinations with the help of any form or window. They have a fixed set of combination form where you can straight away enter different code combinations and this is the only option left in case dynamic insert is unavailable.

Q3. Explain in oracle applications, what do you mean by value set?

Ans:

Any flexfield segment can be used to display the approved values for entry. The list of all the actually approved values is also contained in a value set. There are some below characteristics related to the value sets:

One value set can be shared by different flexfields. For example, the names of all the regional offices which are contained in a value set can be a part of multiple flexfields.

The value ranges for different value sets are limited. Some have next to negligible restriction while others have permission to allow for a certain set of values.

There can be a different set of values present for different value sets.

Same value sets can be used by multiple other segments of the same flexfields. Even when there are different structures for a particular flexfield, a common value set can exist. SRS or the Standard Request Submission have many additional report parameters that share the same value sets.

Let us move to the next Oracle Apps Technical Interview Questions.

Q4. Explain Accounting period  statuses?

Ans:

NEVER OPENED: This means that the journals cannot be entered or posted in this status.

FUTURE ENTERABLE: This status means that the journal may be entered but not posted. The fixed number which is stated in the book set window tells about the period of Future Enterable.

OPEN: The journals can be entered and posted to any amount of timeline in the open period. Special care should be taken while opening unlimited periods that it may slow the entire process of posting and can also be a bit more cumbersome for the fresh users trying to enter the journals.

CLOSED: This status means that the journals cannot be posted during this period and that the periods have to be reopened if you want to post journals. Whenever you are done with your annual/month/quarter processing, these periods should be manually closed.

PERMANENTLY CLOSED: These are the periods that can never be opened again. The purge and archived data can be depicted with this status.

Q5. Describe the role of the field, “the future period”?

Ans:

The value tells how many periods are enterable when the users can enter their journal entries. The prerequisite, in this case, is that the future period should be in an opened state. Good care should also be given to ensure that the users are not unintentionally entering the entries in a wrong period.

Q6. Explain Retained earnings account?

Ans:

It is used to define the net earnings in percentage which is not a part of dividends but is saved by the company who plans to reinvest the same amount in their business or for any other company related financial transaction such as paying a debt, etc. Shareholder’s equity records this on balance sheet.

Q7. Explain the use of transaction calendar?

Ans:

It is defined as the one responsible for applying average balance transaction processing. The numbers of business days are also tagged along with the optional transaction calendar.

Let us move to the next Oracle Apps Technical Interview Questions.

Q8. Explain different segments of Key flexfield in General ledger?

Ans:

It consists of 15 columns where every field represents a segment. The different types are :

Balancing segment

Intercompany segment

Cost center segment

Account segment

Q9. Explain Custom Top?

Ans:

This is the most asked Oracle Apps Technical Interview Questions in an interview. It is another name for customer top where only the customers are the ones for which the accounts have been created for. Based on the type of client requirement, there can be more than one custom tops created. All the customized and the developed components are stored in it. In case of any patches which are applied by the Oracle Corporation, all other modules are overridden except for the custom top.

Q10. Explain MRC?

Ans:

It is the abbreviation of Multiple Reporting Currencies. The default currency is $. In case, there is a need for booking by making use of some other currency, then MRC is put to use. Therefore, we can choose the currency as per our will by making use of MRC. We have brought to you a comprehensive set of interview question and answers for Oracle Applications. Keep following us for more articles.

Q11.Which are the kinds of report triggers?

Ans:

There are mainly five different kinds of report triggers available. They are

  • Before report
  • After report
  • Before parameter form
  • After parameter form
  • Between pages

Q12.What is the purpose of cursors in PL/SQL?

Ans:

The cursor can be made used for the purpose of handling various row – query associated with PL/SQL. Implicit cursors are available for the purpose of handling all the queries related with oracle. The memory spaces that are unnamed are used by oracle for storing the data that can be used with implicit cursors.

Q13.Define record group?

Ans:

Record group can be considered as a concept used for the purpose of holding sql query that is associated with list related with values. Record group consists of static data and also can access data inside tables of database through sql queries

Q14.What is a FlexField?

Ans:

This is a kind of field associated with oracle apps that are used for capturing information related with the organization.

Q15.What is the concurrent program?

Ans:

Concurrent programs are instances that need to be executed along with incompatibles and parameters.

Q16.Define application top?

Ans:

Application tops are found when we are connecting to server. There are two types of application tops available they are product top and custom top. Product top is the kind of top that is built in default by manufacturer. Custom top can be chosen by the client, and any number of custom tops can be created as per the requirement of the client.

Q17.Explain about the procedures that are compulsory in the case of procedures?

Ans:

There are number of parameters which are mandatory in the case of procedures and each of these parameters has a specific job associated with it.

Errorbuf: This is the parameter used for returning error messages and for sending that to log file.

Retcode: This is the parameter capable of showing the status associated with a procedure. 0, 1 and 2 are the status displayed by this parameter. 0 is used for indicating completed normal status, 1 defines completed warning status and 2 is the one denoting completed with error.

Q18.What is a token?

Ans:

Token is used for transferring values towards report builder. Tokens are usually not case – sensitive.

Q19.How to attach reports in Oracle Applications ?

Ans:

The steps are as follows :

  •    Design your report in D2K
  • Generate the executable file of the    report as .rdf.
  •   Move the executable as well as source file to the appropriate product’s folder.
  •  Register the report as concurrent executable.
  • Define the concurrent program for the executable registered.
  •  Add the concurrent program to the request group of the responsibility.

Q20.What is the use of triggers in Forms ?

Ans:

Triggers are used in forms for event handling. You can write PL/SQL code in triggers to respond to a particular event occurred in your forms like when user presses a button or when he commits the form.

The different type of triggers available in forms are :

  • Key-triggers
  •  Navigational-triggers
  •  Transaction-triggers
  •  Message-triggers
  •  Error-triggers
  •  Query based-triggers

    Subscribe For Free Demo

    Q21.What is the use of Temp tables in Interface programs ?

    Ans:

    Temporary tables are used in Interface programs to hold the intermediate data. The data is loaded into temporary tables first and then, after validating through the PL/SQL programs, the data is loaded into the interface tables.

    Q22.What are the steps to register concurrent programs in Apps ?

    Ans:

    The steps to register concurrent programs in apps are as follows :

    • Register the program as concurrent executable.
    • Define the concurrent program for the executable registered.
    • Add the concurrent program to the request group of the responsibility

    Q23.How to pass parameters to a report ? do you have to register them with AOL ?

    Ans:

    You can define parameters in the define concurrent program form. There is no need to register the parameters with AOL. But you may have to register the value sets for those parameters.

    Q24.What Are The Benefits Of Flexfields?

    Ans:

    Configure applications to support your own accounting, product and other codes.

    Enable the construction of intelligent keys.

    Configure application to capture additional data.

    Use the application to validate values and value combinations entered by the user.

    Support multiple flexfield structures depending on data context.

    Q25.What Are The Types Of Flexfields?

    Ans:

    Key flexfield

    Descriptive flexfield

    Q26. Key And Dexcriptive Flexfield Comparison?

    Ans:

    KEY FLEXFIELD

    Owned by one application; used by many

    Required to set up; not always required to use

    Intelligent keys

    Identifies entities

    DESCRIPTIVE FLEXFIELD

    Associated with tables in a specific application

    Setup is optional

    No intelligence; only stores additional information

    Captures additional information only

    Oracle 10g Tutorial

    Q27. What Is A Key Flexfield Qualifier?

    Ans:

    A qualifier is a label attached to a particular key flexfield segment so it can be located by the application requiring its information. A key flexfield qualifier can be of 2 types:

    Flexfield qualifiers identify a segment in a flexfield.

    Segment qualifiers identify a value set in a segment.

    Q28. Types Of Flexfield Qualifier?

    Ans:

    NATURAL ACCOUNT: Each Accounting Flexfield structure must contain only one natural account segment. When setting up the values, you will indicate the type of account as Asset, Liability, Owner’s Equity, Revenue, or Expense.

    BALANCING ACCOUNT: Each Structure must contain only one balancing segment. Oracle GL ensures that all journals balance for each balancing segment.

    COST CENTER: This segment is required for Oracle Assets. The Cost center segment is used in many Oracle Assets reports and by Oracle Workflow to generate account numbers. In addition, Oracle Projects and Oracle Purchasing also utilize the cost center segment.

    INTERCOMPANY: GL automatically uses the intercompany segment in the account code combination to track intercompany transactions within a single set of books. This segment has the same value set and the same values as the balancing segment.

     Q29. Segment Qualifiers?

    Ans:

    ACCOUNT TYPE: Asset, Liability, Owner’s Equity, Revenue, Expense, Budgetary Dr, and Budgetary Cr.

    Budget entry allowed (Yes/No).

    Posting allowed (Yes/No).

    Q30. What Is The Implication Of Dynamic Insert?

    Ans:

    Dynamic Insertion is a feature which controls whether the user can enter new account code combinations from any form/window. If this feature is disabled, then the user cannot input new account code combinations from any window/form.

    Oracle applications use a particular form (called a Combination form) for directly entering the new code combinations. Users can enter new account code combinations only through this form if Dynamic Insertion is disabled.

    Course Curriculum

    Get On-Demand Oracle Applications Technical Training to Build Your Skills

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    Q31. Cross Validating Values?

    Ans:

    For key flexfields with multiple segments, we can define rules to cross check value combinations entered with in the key flexfield segments. This option is referred as Cross Validation rules.

    Q32.What is Oracle Apps (ERP)?

    Ans:

    ERP stands for Enterprise Resource Planning. A product of Oracle India Pvt. Ltd. As the name suggests Enterprise Resource Planning. Enterprise means a firm/organization. Resource means available source of wealth in firm and Planning means how you can maintain day to day resource. Hence in short to facilitate big businesses, companies Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications.

    Q33.What is the Difference between APPS Schema and other Schemas?

    Ans:

    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.

    Q34.What are cycles of GL, AP, and AR?

    Ans:

    In general, GL AP and AR consist of:

    Structure Phase – Defines the process flows and configuration for the system and Conduct business process and data mapping workshops.

    Construct Phase – Contains all of the configuration activities

    Transition Phase – Executes system test

    Deploy Phase – Migrates database to customer environment

    Q35.What are the businesses needs satisfy by the Oracle Applications flexfields?

    Ans:

    The following businesses needs are served by FlexField:

    • You can query the key fields.
    • Helps in customization of data fields without programming.
    • Helps in validating the values.
    • Customize your applications to capture data that is not been tracked by your application.
    • Consist of intelligent fields which comprised of one or more segments, where each segment has both a value and a meaning.

    Q36.What is the Significance of US Folder?

    Ans:

    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’

    Q37.What are different types of invoices and what is a recurring invoice?

    Ans:

    Types of Invoice: Standard Invoice, Credit Memo Invoice, Debit Memo Invoice, Expense Invoice, Recurring Invoice With-hold tax Quick Match and Pre-Payment Invoice.

    Recurring Invoice: As the name suggests the invoice that occurs at regular interval of time is known as the Recurring Invoice.

    Q38.Where did U find the Application short name and basepath names?

    Ans:

    selectbasepath,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

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

    Ans:

    SELECT release_name from FND_PRODUCT_GROUPS;             

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

    Ans:

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

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

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

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

    Q44. What Is Summary Columns?

    Ans:

    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.

    Q45. What Is Formula Column?

    Ans:

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

    Q46.What Is Place Holder Columns?

    Ans:

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

    Q47. Form Development Process?

    Ans:

    Change the form module name as form name.

    Delete the default blocks, window, and canvas

    Create a window.

    Assign the window property class to window

    Create a canvas (subclass info)

    Assign canvas property class to the canvas

    assign the window to the canvas and canvas to the window

    Create a data block

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

    Modify the app_custom package. In the program unit.

    Modify the pre-form trigger (form level)

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

    Save and compile the form.

    Q48. What Is Set-of-books?

    Ans:

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

    Q49.Tell Me Something About Sql-loader?

    Ans:

    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.

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

    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:datamydata.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 .

    Q50. What is Oracle and what are its different editions?

    Ans:

    Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.

    Course Curriculum

    Learn Practical Oriented Oracle Applications Technical Course By Real Time Experts

    Weekday / Weekend BatchesSee Batch Details

    Q51.How will you identify Oracle Database Software Release?

    Ans:

    Oracle follows a number of formats for every release.

    For Example,

    Release 10.1.0.1.1 can be referred to as:

    10: Major DB Release Number

    1: DB Maintenance Release Number

    0: Application Server Release Number

    1: Component Specific Release Number

    1: Platform Specific Release Number

    Q52.How will you differentiate between VARCHAR & VARCHAR2?

    Ans:

    Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:

    VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.

    VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.

    Q53. What is the difference between TRUNCATE & DELETE command?

    Ans:

    Both the commands are used to remove data from the database.

    The difference between the two include:

    TRUNCATE is a DDL operation while DELETE is a DML operation.

    TRUNCATE drops the structure of a database and hence cannot be rolled back while the DELETE command can be rolled back.

    The TRUNCATE command will free the object storage space while the DELETE command does not.

    Q54.What is meant by RAW datatype?

    Ans:

    RAW datatype is used to store variable-length binary data or byte strings.

    Q55. What is meant by Joins? List the types of Joins.

    Ans:

    Joins are used to extract data from multiple tables using some common columns or conditions.

    There are various types of Joins as listed below:

    • INNER JOIN
    • OUTER JOIN
    • CROSS JOINS or CARTESIAN PRODUCT
    • EQUI JOIN
    • ANTI JOIN
    • SEMI JOIN

    Q56.What is the difference between SUBSTR & INSTR functions?

    Ans:

    SUBSTR function returns the sub-part identified by numeric values from the provided string.

    For Example, [SELECT SUBSTR (‘India is my country’, 1, 4) from dual] will return “Indi”.

    INSTR will return the position number of the sub-string within the string.

    For Example, [SELECT INSTR (‘India is my country’, ‘a’) from dual] will return 5.

    Q57. How can we find out the duplicate values in an Oracle table?

    Ans:

    We can use the below example query to fetch the duplicate records.

    SELECT EMP_NAME, COUNT (EMP_NAME)

    FROM EMP

    GROUP BY EMP_NAME

    HAVING COUNT (EMP_NAME) > 1;

    Q58.How does theON-DELETE-CASCADE statement work?

    Ans:

    Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

    We can add ON DELETE CASCADE option on an existing table using the below set of commands.

    Syntax:

    ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES

    PARENT_T1 (COLUMN1) ON DELETE CASCADE;

    Q59. What is an NVL function? How can it be used?

    Ans:

    NVL is a function that helps the user to substitute value if null is encountered for an expression.

    HZ_PARTIES stores information

    about parties such as organizations,

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

    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.

    Q60.What are the validation types?

    Ans:

    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.

    4) Table – input is checked against values in an application table

    5) Special – values set uses a flex field itself.

    6) Pair – two flex fields together specify a range of valid values.

    7) Translatable independent – input must exist on previously defined listof values; translated values can be used.

    8) Translatable dependent – input is checked against a subset of valuesbased on a prior values; translated value can be used.

    Q61.How does u customize the Reports?

    Ans:

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

    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

    Q62. FLEX FIELDS?

    Ans:

    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

    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.

    Q63.What is Flex mode and Confine mode?

    Ans:

    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.

    Q64.What is the difference between a Primary Key & a Unique Key?

    Ans:

    Primary Key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

    Given below are a few differences:

    The primary key can be only one on the table while unique keys can be multiple.

    The primary key cannot hold null value at all while the unique key allows multiple null values.

    The primary key is a clustered index while a unique key is a non-clustered index.

    Q65.How TRANSLATE command is different from REPLACE?

    Ans:

    TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE command will replace a character or a set of characters with a complete substitution string.

    Q66. How can we find out the current date and time in Oracle?

    Ans:

    We can find the current date & time using SYSDATE command in Oracle.

    Syntax:

    • SELECT SYSDATE into CURRENT_DATE from dual;

    Q67.Why do we use COALESCE function in Oracle?

    Ans:

    COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. There must be a minimum of two arguments in an expression.

    Syntax:

    • COALESCE (expr 1, expr 2, expr 3…expr n)

    Q68. How will you write a query to get 5th RANK students from the table STUDENT_REPORT?

    Ans:

    The query will be as follows:

    SELECT TOP 1 RANK

    FROM (SELECT TOP 5 RANK

    FROM STUDENT_REPORT

    ORDER BY RANK DESC) AS STUDENT

    ORDER BY RANK ASC;

    Q69.When do we use the GROUP BY clause in SQL Query?

    Ans:

    GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG, etc.

    Syntax:

    • SELECT COLUMN_1, COLUMN_2
    • FROM TABLENAME
    • WHERE [condition]
    • GROUP BY COLUMN_1, COLUMN_2

    Q70.What is the quickest way to fetch the data from a table?

    Ans:

    The quickest way to fetch the data would be to use ROWID in the SQL query.

    Oracle Applications Technical Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    Q71. Where do we use DECODE and CASE Statements?

    Ans:

    Both DECODE & CASE statements will function like IF-THEN-ELSE statement and they are the alternatives for each other. These functions are used in Oracle to transform the data values.

    For Example:

    • DECODE Function
    • Select ORDERNUM,
    • DECODE (STATUS,’O’, ‘ORDERED’,’P’, ‘PACKED,’S’,’SHIPPED’,’A’,’ARRIVED’)
    • FROM ORDERS;
    • CASE Function
    • Select ORDERNUM
    • , CASE (WHEN STATUS =’O’ then ‘ORDERED’
    • WHEN STATUS =’P’ then PACKED
    • WHEN STATUS =’S’ then ’SHIPPED’
    • ELSE ’ARRIVED’) END
    • FROM ORDERS;

    Both the commands will display order numbers with their respective status as,

    If,

    Status O= Ordered

    Status P= Packed

    Status S= Shipped

    Status A= Arrived

    Q72.Why do we need integrity constraints in a database?

    Ans:

    Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

    Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

    Q73.What do you mean by MERGE in Oracle and how can we merge two tables?

    Ans:

    The MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

    Syntax:

    • MERGE INTO TARGET_TABLE_1
    • USING SOURCE_TABLE_1
    • ON SEARCH_CONDITION
    • WHEN MATCHED THEN
    • INSERT (COL_1, COL_2…)
    • VALUES (VAL_1, VAL_2…)
    • WHERE <CONDITION>
    • WHEN NOT MATCHED THEN
    • UPDATE SET COL_1=VAL_1, COL_2=VAL_2…
    • WHEN <CONDITION.

    Q74. What are the components of logical database structure in Oracle database?

    Ans:

    The components of the logical database structure in Oracle database are:

    Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.

    Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.

    Q75. Describe an Oracle table?

    Ans:

    A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

    To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.

    • CREATE TABLE table_name
    • (
    • column1 datatype [ NULL | NOT NULL ],
    • column2 datatype [ NULL | NOT NULL ],
    • column_n datatype [ NULL | NOT NULL ]
    • );

    Q76. Explain the relationship among database, tablespace and data file?

    Ans:

    An Oracle database possesses one or more logical storage units called tablespaces. Each tablespace in Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. Talking about the datafiles, these are the physical structure that confirms with the operating system as to which Oracle program is running.

    Q77. Explain about the ANALYZE command in Oracle?

    Ans:

    This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

    Analyze command is used to identify migrated and chained rows of the table or a cluster.

    It is used to validate the structure of an object.

    This helps in collecting the statistics about the object used by the user and are then stored on to the data dictionary.

    It also helps in deleting statistics that are used by an object from the data dictionary.

    Q78. What is the use of Aggregate functions in Oracle?

    Ans:

    An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations.

    Q79. Explain Temporal data types in Oracle.

    Ans:

    Oracle mainly provides these following temporal data types:

    Date Data Type: Different formats of Dates.

    TimeStamp Data Type: Has different formats of Time Stamp.

    Interval Data Type: Interval between dates and time.

    Q80. What is the Difference Between Interfaces and Conversions in Oracle apps.

    Ans:

    Interface Process is the Subset of the Conversion Process. It Means 

    When we migrating the Data from External source to the Oracle Apps, but the data is not in the Form or in the shape like Oracle apps required Data format , so we need to massage this data or to put extra efforts to prepare this data in the form of Oracle Required format data that process is called conversion. Once the data prepared in the format of Oracle apps required format then the Interface process starts to import or migrate the data in Oracle apps.Conversion most of the Time is the one-time process when we are doing implementation or we are switching the ERP application from other application to Oracle application. But Interface is the continuous on-going task which helps to sync the external applications with the Oracle application.

    Q81. What is the Max Parameters can be created in the concurrent Program?

    Ans:

    There is the limitation in the Concurrent Program. We can only create the max 100 parameters in the Concurrent Program.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free