Interview Questions
[et_pb_section bb_built=”1″ _builder_version=”3.0.52″ background_image=”http://dev.oraclesolutions.pk/wp-content/uploads/2017/07/question-mark-2123967_1920.jpg” parallax=”on” parallax_method=”off” inner_shadow=”on”][et_pb_row _builder_version=”3.0.52″ parallax=”on” parallax_method=”off” background_position_1=”top_left” background_repeat_1=”no-repeat” make_fullwidth=”on” use_custom_width=”on” custom_width_percent=”100%” custom_width_px=”1702px” use_custom_gutter=”on” width_unit=”off” make_equal=”on”][et_pb_column type=”4_4″][et_pb_toggle _builder_version=”3.0.52″ title=”What is an ERP?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
- It is 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, which produce and access information in a real-time environment.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Tell me some thing about SQL-LOADER?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
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. There are commonly Two ways of loading format as given below:
- Conventional – The conventional path loader essentially loads the data by using standard ‘insert’ statement.
- Direct – Direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”How would you dump data from pl/SQL block to flat files?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
We can dump data from pl/SQL block to flat file using UTL_FILE Package.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”What is SET-OF-BOOKS or SOB?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
Collection of Chat of Accounts and Currency and Calendars is called Set of Books or SOB.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”What is the interface table?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
Interface Table is a table which is used as medium for transfer data between two systems.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”What is invoice?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
It is a document for request payment.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Tell me what are the Base tables in the AR?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
hz_parties |
HZ_PARTIES stores information about parties such as organizations, people, and groups, including address information for the party. |
hz_cust_accounts |
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 multiple-customer relationships with a single party, so each party can have multiple customer account records in this table. |
hz_cust_acct_sites_all |
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 |
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 |
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 |
HZ_LOCATIONS stores information about physical locations. |
hz_Person_Profiles |
HZ_PERSON_PROFILES stores detail information about people. |
hz_Organization_Profiles |
HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics, socio economic and corporate linkage information for business sites. |
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Tell me about some customer interface tables and there uses?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
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. |
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. |
Ra_contact_phones_int_all |
This table stores telephone numbers for customers, addresses and contacts. |
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. |
Ra_cust_paymethod_int_all |
To import payment methods for customers and bill–to business purposes |
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Write down the staging tables in the customer interface?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
· Ra_customers_stg
· Ra_customers_address_stg
· Ra_customers_point_stg
· Ra_customers_contact points_stg
· Ra_customers_relate_stg
· Ra_customer_error
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Tell me some mandatory columns in the customer interface tables?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
Ra_customers_interface_all |
· Orig_system_customer_ref· insert_update_flag· customer_number· customer_status· last_updated_by· last_updated_date· created_by· creation_date. |
Ra_customer_profile_int_all |
· customer_profile_class_name |
Ra_contact_phones_int_all |
· orig_system_telephone_ref· telephone· telephone_type |
Ra_customer_banks_int_all |
· bank_a/c_name· bank_a/c_no· bank_a/c_currency_code |
Ra_cust_paymethod_int_all |
· payment_method_name |
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Tell me the Navigation for customer interface?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
Receivables > Interface > Customers
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”Which tables we cannot be updated through customer interface?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
· RA_SITE_USES_ALL
· RA_CUSTOMER_RELATIONSHIPS_ALL
· RA_CUST_RECEIPT_METHODS
· AP_BANK_BRANCHES
· AP_BANK_ACCOUNTS_ALL
· AP_BANK_ACCOUNT_USES_ALL
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”How to record additional customer and address information through customer interface?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
We Can use attributes columns for saving additional information in following tables:
- Additional Customer data can be populated in RA_CUSTOMERS_INTERFACE_ALL from customer_attribute1 to customer_attribute15 which will be populated in RA_CUSTOMERS from attribute1 to attribute15.
- Additional address information can be populated in RA_CUSTOMERS_INTERFACE_ALL from address_attribute1 to address_attribute15, which will be populated in RA_ADDRESSES_ALL from attribute1 to attribute15
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”What should be the Ideal batch size (number of customer records) general guidelines for optimal performance?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
About 10,000 records per batch is ideal, it is suggested to keep the batch small.
[/et_pb_toggle][et_pb_toggle _builder_version=”3.0.52″ title=”How do you send records at customer level profile and address/site level profile?” open=”off” use_background_color_gradient=”on” border_style=”ridge” background_color=”#ffffff” title_font=”Montserrat|on||on|” title_text_color=”#ffffff” body_font=”Montserrat||||” body_font_size=”15″ background_color_gradient_end=”#000000″ background_color_gradient_direction=”181deg” use_border_color=”on” icon_color=”#0c71c3″ title_font_size=”18″ body_line_height=”1em” border_width=”3px” background_color_gradient_start=”#ffffff” open_toggle_text_color=”#ff0000″ body_text_color=”#ffffff”]
For every customer record in RA_CUSTOMERS_INTERFACE_ALL insert two records in table RA_CUSTOMER_PROFILES_INT_ALL.
(Refer Note: 1070800.6 )
[/et_pb_toggle][/et_pb_column][/et_pb_row][/et_pb_section]
1. What will you get by the cursor attribute SQL%FOUND?
It returns the Boolean value TRUE if at least one row was processed.
2. What will you get by the cursor attribute SQL%NOTFOUND?
It returns the Boolean value TRUE if no rows were processed.
3. What do you understand by PL/SQL packages?
- A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.
4. What are the two different parts of the PL/SQL packages?
PL/SQL packages have the following two parts:
- Specification part: It specifies the part where the interface to the application is defined.
- Body part: This part specifies where the implementation of the specification is defined.
5. Which command is used to delete a package?
- The DROP PACKAGE command is used to delete a package.
6. How to execute a stored procedure?
There are two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.
Simply use the procedure name
7. What are the advantages of stored procedure?
- Modularity, extensibility, reusability, Maintainability and one time compilation.
8. What are the cursor attributes used in PL/SQL?
- %ISOPEN: it checks whether the cursor is open or not.
- %ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
- %FOUND: it checks whether cursor has fetched any row. If yes – TRUE.
- %NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.
9. What is the difference between syntax error and runtime error?
- A syntax error can be easily detected by a PL/SQL compiler.
- For example: incorrect spelling etc.
- A runtime error is handled with the help of exception-handling section in a PL/SQL block.
- For example: SELECT INTO statement, which does not return any rows.
10. Explain the Commit statement?
Following conditions are true for the Commit statement:
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
11. Explain the Rollback statement?
The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement:
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
12. Explain the SAVEPOINT statement?
With SAVEPOINT, only part of transaction can be undone.
13. What is mutating table error?
- Mutating table error is occurred when a trigger tries to update a row that it is currently using.
- It is fixed by using views or temporary tables.
14. What is consistency?
Consistency simply means that each user sees the consistent view of the data.
15. What is cursor and why it is required?
- A cursor is a temporary work area created in a system memory when an SQL statement is executed.
- A cursor contains information on a select statement and the row of data accessed by it.
- This temporary work area stores the data retrieved from the database and manipulate this data.
- A cursor can hold more than one row, but can process only one row at a time.
- Cursor are required to process rows individually for queries.
16. How many types of cursors are available in PL/SQL?
There are two types of cursors in PL/SQL.
- Implicit cursor, and
- explicit cursor
1. What are some predefined exceptions in PL/SQL?
A list of predefined exceptions in PL/SQL:
- DUP_VAL_ON_INDEX
- ZERO_DIVIDE
- NO_DATA_FOUND
- TOO_MANY_ROWS
- CURSOR_ALREADY_OPEN
- INVALID_NUMBER
- INVALID_CURSOR
- PROGRAM_ERROR
- TIMEOUT _ON_RESOURCE
- STORAGE_ERROR
- LOGON_DENIED
- VALUE_ERROR
- etc.
2. What is a trigger in PL/SQL?
- A trigger is a PL/SQL program which is stored in the database.
- It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.
3. What is the maximum number of triggers, you can apply on a single table?
12 triggers.
4. How many types of triggers exist in PL/SQL?
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
- BEFORE ALL ROW INSERT
- AFTER ALL ROW INSERT
- BEFORE INSERT
- AFTER INSERT etc.
5. What is the difference between execution of triggers and stored procedures?
- A trigger is automatically executed without any action required by the user, while a stored procedure is explicitly invoked by the user.
6. What happens when a trigger is associated to a view?
- When a trigger is associated to a view, the base table triggers are normally enabled.
7. What is the usage of WHEN clause in trigger?
- A WHEN clause specifies the condition that must be true for the trigger to be triggered.
8. How to disable a trigger name update_salary?
- ALTER TRIGGER update_salary DISABLE;
9. Which command is used to delete a trigger?
- DROP TRIGGER command.
10. what are the two virtual tables available at the time of database trigger execution?
Table columns are referred as THEN.column_name and NOW.column_name.
- For INSERT related triggers, NOW.column_name values are available only.
- For DELETE related triggers, THEN.column_name values are available only.
- For UPDATE related triggers, both Table columns are available.
11. What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions.
- It is similar to a procedure in other programming languages.
- It is stored in the database and can be repeatedly executed.
- It is stored as schema object.
- It can be nested, invoked and parameterized.
12. What are the different schemas objects that can be created using PL/SQL?
- Stored procedures and functions
- Packages
- Triggers
- Cursors
13. What do you know by PL/SQL Cursors?
- Oracle uses workspaces to execute the SQL commands.
- When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area.
- This area is identified by the cursor.
- It allows programmers to name this area and access it’s information.
14. What is the difference between the implicit and explicit cursors?
- Implicit cursor is implicitly declared by Oracle.
- This is a cursor to all the DDL and DML commands that return only one row.
- Explicit cursor is created for queries returning multiple rows.
15. What will you get by the cursor attribute SQL%ROWCOUNT?
- The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
1. What is PL/SQL?
- PL/SQL stands for procedural language extension to SQL.
- It supports procedural features of programming language and SQL both.
- It was developed by Oracle Corporation in early of 90’s to enhance the capabilities of SQL.
2. What is the purpose of using PL/SQL?
- PL/SQL is an extension of SQL.
- While SQL is non-procedural, PL/SQL is a procedural language designed by Oracle.
- It is invented to overcome the limitations of SQL.
3. What are the most important characteristics of PL/SQL?
A list of some notable characteristics:
- PL/SQL is a block-structured language.
- It is portable to all environments that support Oracle.
- PL/SQL is integrated with the Oracle data dictionary.
- Stored procedures help better sharing of application.
4. What is PL/SQL table? Why it is used?
- Objects of type tables are called PL/SQL tables that are modeled as database table.
- We can also say that PL/SQL tables are a way to providing arrays.
- Arrays are like temporary tables in memory that are processed very quickly.
- PL/SQL tables are used to move bulk data.
- They simplifies moving collections of data.
5. What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
- Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
- Composite datatypes Example are RECORD, TABLE etc.
6. What is the basic structure of PL/SQL?
PL/SQL uses BLOCK structure as its basic structure.
Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.
- The Declaration Section (optional)
- The Execution Section (mandatory)
- The Exception handling Section (optional)
8. What is exception? What are the types of exceptions?
Exception is an error handling part of PL/SQL.
There are two type of exceptions: pre_defined exception and user_defined exception.
9. How to write a single statement that concatenates the words ?Hello? and ?World? and assign it in a variable named Greeting?
Greeting := ‘Hello’ || ‘World’;
10. Does PL/SQL support CREATE command?
No, PL/SQL doesn’t support the data definition commands like CREATE.
11. Write a unique difference between a function and a stored procedure.
A function returns a value while a stored procedure doesn’t return a value.
12. How exception is different from error?
- Whenever an Error occurs Exception arises.
- Error is a bug whereas exception is a warning or error condition.
13. What is the main reason behind using an index?
By using an Index we can access data blocks Faster in the table.
14. What are PL/SQL exceptions? Tell me any three.
- Too_many_rows
- No_Data_Found
- Value_error
- Zero_error etc.
15. How do you declare a user-defined exception?
You can declare the User defined exceptions under the DECLARE section, with the keyword EXCEPTION.
Syntax:
<exception_name> EXCEPTION;