PL/SQL and Informatica Interview Questions & Answers

  1. Question 1. What Do You Mean By Enterprise Data Warehousing?

    Answer :

    When the organization data is created at a single point of access it is called as enterprise data warehousing. Data can be provided with a global view to the server via a single source store. One can do periodic analysis on that same source. It gives better results but however the time required is high.

  2. Question 2. What The Difference Is Between A Database, A Data Warehouse And A Data Mart?

    Answer :

    Database includes a set of sensibly affiliated data which is normally small in size as compared to data warehouse. While in data warehouse there are assortments of all sorts of data and data is taken out only according to the customer’s needs. On the other hand datamart is also a set of data which is designed to cater the needs of different domains. For instance an organization having different chunk of data for its different departments i.e. sales, finance, marketing etc.

     


  3. Oracle 10g Interview Questions

  4. Question 3. What Is Meant By A Domain?

    Answer :

    When all related relationships and nodes are covered by a sole organizational point, its called domain. Through this data management can be improved.


  5. Question 4. What Is The Difference Between A Repository Server And A Powerhouse?

    Answer :

    Repository server controls the complete repository which includes tables, charts, and various procedures etc. Its main function is to assure the repository integrity and consistency. While a powerhouse server governs the implementation of various processes among the factors of server’s database repository.


  6. Oracle 10g Tutorial

  7. Question 5. How Many Repositories Can Be Created In Informatica?

    Answer :

    There can be any number of repositories in informatica but eventually it depends on number of ports.


  8. PL/SQL Interview Questions

  9. Question 6. What Is The Benefit Of Partitioning A Session?

    Answer :

    Partitioning a session means solo implementation sequences within the session. It’s main purpose is to improve server’s operation and efficiency. Other transformations including extractions and other outputs of single partitions are carried out in parallel.

     


  10. Question 7. How Are Indexes Created After Completing The Load Process?

    Answer :

    For the purpose of creating indexes after the load process, command tasks at session level can be used. Index creating scripts can be brought in line with the session’s workflow or the post session implementation sequence. Moreover this type of index creation cannot be controlled after the load process at transformation level.


  11. Oracle DBA Tutorial
    Oracle DBA Interview Questions

  12. Question 8. Explain Sessions. Explain How Batches Are Used To Combine Executions?

    Answer :

    A teaching set that needs to be implemented to convert data from a source to a target is called a session. Session can be carried out using the session’s manager or pmcmd command. Batch execution can be used to combine sessions executions either in serial manner or in a parallel. Batches can have different sessions carrying forward in a parallel or serial manner.


  13. Question 9. How Many Number Of Sessions Can One Group In Batches?

    Answer :

    One can group any number of sessions but it would be easier for migration if the number of sessions are lesser in a batch.


  14. Netezza Interview Questions

  15. Question 10. Explain The Difference Between Mapping Parameter And Mapping Variable?

    Answer :

    When values change during the session’s execution it’s called a mapping variable. Upon completion the Informatica server stores the end value of a variable and is reused when session restarts. Moreover those values that do not change during the sessions execution are called mapping parameters. Mapping procedure explains mapping parameters and their usage. Values are allocated to these parameters before starting the session.

  16. Question 11. What Is Complex Mapping?

    Answer :

    Following are the features of complex mapping.

    • Difficult requirements
    • Many numbers of transformations
    • Complex business logic

  17. Oracle Forms And Reports Interview Questions

  18. Question 12. How Can One Identify Whether Mapping Is Correct Or Not Without Connecting Session?

    Answer :

    One can find whether the session is correct or not without connecting the session is with the help of debugging option.

     



  19. Oracle 10g Interview Questions

  20. Question 13. Can One Use Mapping Parameter Or Variables Created In One Mapping Into Any Other Reusable Transformation?

    Answer :

    Yes, One can do because reusable transformation does not contain any mapplet or mapping.

  21. Question 14. Explain The Use Of Aggregator Cache File?

    Answer :

    Aggregator transformations are handled in chunks of instructions during each run. It stores transitional values which are found in local buffer memory. Aggregators provides extra cache files for storing the transformation values if extra memory is required.


  22. Question 15. Briefly Describe Lookup Transformation?

    Answer :

    Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the look up condition for all look up ports delivered during transformations.


  23. Oracle Application Framework Interview Questions

  24. Question 16. What Does Role Playing Dimension Mean?

    Answer :

    The dimensions that are utilized for playing diversified roles while remaining in the same database domain are called role playing dimensions.


  25. Question 17. How Can Repository Reports Be Accessed Without Sql Or Other Transformations?

    Answer :

    Repositoryreports are established by metadata reporter. There is no need of SQL or other transformation since it is a web app.

     


  26. Oracle Apex Interview Questions

  27. Question 18. What Are The Types Of Metadata That Stores In Repository?

    Answer :

    The types of metadata includes Source definition, Target definition, Mappings, Mapplet, Transformations.



  28. PL/SQL Interview Questions

  29. Question 19. Explain The Code Page Compatibility?

    Answer :

    When data moves from one code page to another provided that both code pages have the same character sets then data loss cannot occur. All the characteristics of source page must be available in the target page. Moreover if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due the fact the two code pages are not compatible.

  30. Question 20. How Can You Validate All Mappings In The Repository Simultaneously?

    Answer :

    All the mappings cannot be validated simultaneously because each time only one mapping can be validated.

     



  31. Powerbuilder Interview Questions

  32. Question 21. Briefly Explain The Aggregator Transformation?

    Answer :

    It allows one to do aggregate calculations such as sums, averages etc. It is unlike expression transformation in which one can do calculations in groups.

     

  33. Question 22. Describe Expression Transformation?

    Answer :

    Values can be calculated in single row before writing on the target in this form of transformation. It can be used to perform non aggregate calculations. Conditional statements can also be tested before output results go to target tables.


  34. Question 23. What Do You Mean By Filter Transformation?

    Answer :

    It is a medium of filtering rows in a mapping. Data needs to be transformed through filter transformation and then filter condition is applied. Filter transformation contains all ports of input/output, and the rows which meet the condition can only pass through that filter.


  35. Oracle D2K Interview Questions

  36. Question 24. What Is Joiner Transformation?

    Answer :

    Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.



  37. Oracle DBA Interview Questions

  38. Question 25. What Is Lookup Transformation?

    Answer :

    It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.

  39. Question 26. How Union Transformation Is Used?

    Answer :

    It is a diverse input group transformation which can be used to combine data from different sources. It works like UNION All statement in SQL that is used to combine result set of two SELECT statements.


  40. Question 27. What Do You Mean Incremental Aggregation?

    Answer :

    Option for incremental aggregation is enabled whenever a session is created for a mapping aggregate. Power center performs incremental aggregation through the mapping and historical cache data to perform new aggregation calculations incrementally.


  41. Netezza Interview Questions

  42. Question 28. What Is The Difference Between A Connected Look Up And Unconnected Look Up?

    Answer :

    When the inputs are taken directly from other transformations in the pipeline it is called connected lookup. While unconnected lookup doesn’t take inputs directly from other transformations, but it can be used in any transformations and can be raised as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping.

     

  43. Question 29. What Is A Mapplet?

    Answer :

    A recyclable object that is using mapplet designer is called a mapplet. It permits one to reuse the transformation logic in multitude mappings moreover it also contains set of transformations.

  44. Question 30. Briefly Define Reusable Transformation?

    Answer :

    Reusable transformation is used numerous times in mapping. It is different from other mappings which use the transformation since it is stored as a metadata. The transformations will be nullified in the mappings whenever any change in the reusable transformation is made.

  45. Question 31. What Does Update Strategy Mean, And What Are The Different Option Of It?

    Answer :

    Row by row processing is done by informatica. Every row is inserted in the target table because it is marked as default. Update strategy is used whenever the row has to be updated or inserted based on some sequence. Moreover the condition must be specified in update strategy for the processed row to be marked as updated or inserted.

  46. Question 32. What Is The Scenario Which Compels Informatica Server To Reject Files?

    Answer :

    This happens when it faces DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in the rows was condensed.

  47. Question 33. What Is Surrogate Key?

    Answer :

    Surrogate key is a replacement for the natural prime key. It is a unique identification for each row in the table. It is very beneficial because the natural primary key can change which eventually makes update more difficult. They are always used in form of a digit or integer.

     


  48. Oracle Forms And Reports Interview Questions

  49. Question 34. What Are The Prerequisite Tasks To Achieve The Session Partition?

    Answer :

    In order to perform session partition one need to configure the session to partition source data and then installing the Informatica server machine in multifold CPU’s.

     

  50. Question 35. Which Files Are Created During The Session Rums By Informatics Server?

    Answer :

    During session runs, the files created are namely Errors log, Bad file, Workflow low and session log.

  51. Question 36. What Is The Purpose Of The Pl/sql Language?

    Answer :

    PL/SQL is an extension of SQL. SQL is non-procedural. PL/SQL is a procedural language designed by oracle to overcome the limitations that exist in SQL.


  52. Oracle Application Framework Interview Questions

  53. Question 37. What Is The Difference Between Implicit And Explicit Cursors?

    Answer :

    Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created.

     

  54. Question 38. Which Command Is Used To Delete A Package?

    Answer :

    The DROP PACKAGE command.

     

  55. Question 39. How Do You Refer To The Types, Objects And Sub Programs Declared Within A Package?

    Answer :

    The types, objects, and subprograms declared within a package are referred to using the dot notation as −

    • package_name.type_name
    • package_name.object_name
    • package_name.subprogram_name
  56. Question 40. Which Command(s) Are Used For Creating Pl/sql Packages?

    Answer :

    CREATE PACKAGE command is used for creating the specification part. CREATE PACKAGE BODY command is used for creating the body part.


  57. Oracle Apex Interview Questions

  58. Question 41. Pl/sql Packages Usually Have Two Parts. What Are These Two Parts?

    Answer :

    PL/SQL packages have two parts −
    Specification part − where the interface to the application are defined.
    Body part − where the implementation of the specification are defined.

     

  59. Question 42. What Are The Steps That Need To Be Performed To Use An Explicit Cursor? Discuss Briefly.

    Answer :

    The steps that need to be performed on explicit cursor are −
    DECLARE − assigns a name to the cursor and defines the structure of query within it.
    OPEN − executes the query, whereby the rows returned by the query are available for fetching.
    FETCH − assigns values from the current row (cursor position) into specified variables.
    CLOSE − releases the memory space.

     


  60. Powerbuilder Interview Questions

  61. Question 43. What Do You Understand By Explicit Cursors?

    Answer :

    Explicit cursors are defined explicitly using the CURSOR statement, with a general syntax −
    CURSOR cursor_name [(parameters)] IS query_expression;
    It allows processing queries that return multiple rows.

  62. Question 44. How Do You Declare A User-defined Exception?

    Answer :

    User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax −
    EXCEPTION;

  63. Question 45. What Is The Difference Between A Function And A Stored Procedure?

    Answer :

    A function returns a value and a stored procedure doesn’t return a value.

  64. Question 46. Write A Statement To Disable A Trigger Named Update_marks.

    Answer :

    ALTER TRIGGER update_marks DISABLE;

     

  65. Question 47. Which Command Is Used To Delete A Trigger?

    Answer :

    DROP TRIGGER command.

  66. Question 48. What Is The Purpose Of The Optional Argument [or Replace] In A Create Trigger Command?

    Answer :

    The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.

  67. Question 49. What Is The Use Of A When Clause In A Trigger?

    Answer :

    A WHEN clause specifies the condition that must be true for the trigger to be triggered.

  68. Question 50. What Are The Uses Of Triggers?

    Answer :

    Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for −

    • Creating validation mechanisms involving searches in multiple tables
    • Creating logs to register the use of a table
    • Update other tables as a result of inclusion or changes in the current table.
  69. Question 51. What Is The Difference In Execution Of Triggers And Stored Procedures?

    Answer :

    A trigger is automatically executed without any action required by the user, whereas, a stored procedure needs to be explicitly invoked.

  70. Question 52. Which Command/commands Allow Iteration A Use Of Loops In A Pl/sql Block?

    Answer :

    LOOP command, FOR.. LOOP command, WHILE command.

  71. Question 53. What Is Returned By The Cursor Attribute Sql%notfound?

    Answer :

    It returns the Boolean value TRUE if no rows were processed.

  72. Question 54. What Is Returned By The Cursor Attribute Sql%found?

    Answer :

    It returns the Boolean value TRUE if at least one row was processed.

  73. Question 55. What Is Returned By The Cursor Attribute Sql%rowcount?

    Answer :

    It returns the number of rows that are processed by a SQL statement.

  74. Question 56. What Are The Pl/sql Cursors?

    Answer :

    Oracle uses workspaces to execute the SQL commands. In other words, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access it’s information.

  75. Question 57. What Is A Trigger?

    Answer :

    A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.

  76. Question 58. What Is A Pl/sql Package?

    Answer :

    A package is a file that groups functions, cursors, stored procedures, and variables in one place.

  77. Question 59. What Is The Purpose Of %rowtype Data Type? Explain With Example.

    Answer :

    It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable.
    For example
    emptype := emp%rowtype;
    name := emptype.empname;

  78. Question 60. What Is The Purpose Of %type Data Type? Explain With Example.

    Answer :

    It assigns a variable the same data type used by the column, for which the variable is created. For example,
    dcode := dept.detpno%type;
    The variable dcode is created with the same data type as that of the deptno column of the dept table.

  79. Question 61. What Does The Colon Sign (: ) Implies In The Following Statement?
    :deficit := Balance – Loan;

    Answer :

    The colon (: )sign implies that the variable :deficit is an external variable.

  80. Question 62. State Few Notable Characteristics Of Pl/sql.

    Answer :

    • Block-structured language.
    • Stored procedures help better sharing of application.
    • Portable to all environments that support Oracle.
    • Integration with the Oracle data dictionary.
  81. Question 63. Name Few Schema Objects That Can Be Created Using Pl/sql?

    Answer :

    • Stored procedures and functions
    • Packages
    • Triggers
    • Cursors
  82. Question 64. State Some Features Or Programming Constructs Supported By Pl/sql.

    Answer :

    • Variables and constants
    • Embedded SQL support
    • Flow control
    • Cursor management
    • Exception handling
    • Stored procedures and packages
    • Triggers
  83. Question 65. What Are The Three Basic Sections Of A Pl/sql Block?

    Answer :

    1. Claration section
    2. Execution section
    3. Exception section