45 Oracle interview questions with answer

Following is the Oracle interview questions with answer

Oracle is a secured database that is widely used in multinational companies. The frequently asked questions from oracle database are given below.

1) What are the components of physical database structure of Oracle database?

Components of physical database structure are given below.

    One or more data files.
    Two or more redo log files.
    One or more control files.

2) What are the components of logical database structure in Oracle database?

Components of logical database structure.

    Tablespaces
    Database's schema objects

3) What is a tablespace?

A database contains Logical Storage Unit called tablespaces. A tablespace is a set of related logical structures. Actually a tablespace groups related logical structures together.

4) What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically generate a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary tables for the entire database.

5) What is an Oracle table?

A table is basic unit of data storage in Oracle database. A table contains all the accessible information of a user in rows and columns.
6) In the Oracle version 9.3.0.5.0, what does each number shows?

Oracle version number refers:

    9 - Major database release number
    3 - Database maintenance release number
    0 - Application server release number
    5 - Component Specific release number
    0 - Platform Specific release number

7) What is bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.

8) What is the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.

9) What is a snapshot in Oracle database?

A snapshot is a replica of a target master table from a single point-in-time. In simple words you can say, snapshot is a copy of a table on a remote database.
10) What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also.

Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with a uncertain condition it should be restarted with RESTRICT mode and then shutdown with NORMAL option.

For a complete cold backup the following files must be backed up.

All datafiles, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).

11) How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:

    library cache
    data dictionary cache

12) What is save point in Oracle database?

Save points are used to divide a transaction into smaller parts. It allows rolling back of a transaction. Maximum five save points are allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

13) What is hash cluster in Oracle?

Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.

14) What are the various Oracle database objects?

Tables: This is a set of elements organized in vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is virtual table derived from one or more tables.

Indexes: This is a performance tuning method to process the records.

Synonyms: This is a name for tables.

15) What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

16) What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

    Form module
    Menu module
    Pl/SQL Library module
    Object Library module

17) What is the usage of ANALYZE command in Oracle?

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

    It is used to identify migrated and chained rows of the table or cluster.
    It is used to validate the structure of the object.
    It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
    It helps in deleting statistics used by object from the data dictionary.

18) Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

19) What types of joins are used in writing SUBQUERIES?

    Self join
    Outer Join
    Equi-join

20) What is the usage of control file in Oracle?

In Oracle, control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an ORACLE database begins.

21) What is a synonym?

A synonym is also known as alias for a table, view, sequence or program unit.

22) What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only accessed by the owner.

Public: It can be accessed by any database user.

23) What is the usage of synonyms?

    Synonym can be used to hide the real name and owner of an object.
    It provides public access to an object.
    It also provides location transparency for tables, views or program units of a remote database.
    It simplifies the SQL statements for database users.

24) How do you store pictures in a database?

Yes, you can store pictures in a database using Long Raw Data type. This data type is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type.

25) What is BLOB data type in Oracle?

BLOB data type is a data type with varying length binary string. It is used to store two gigabytes memory. For BLOB data type, the length needs to be specified in bytes.
26) What is the difference between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a character by character while Replace is used to substitute a single character with a word.

27) What are the different types of database objects?

A list of different types of database objects:

    Tables: This is a set of elements organized in vertical and horizontal fashion.
    Tablespaces: This is a logical storage unit in Oracle.
    Views: It is virtual table derived from one or more tables.
    Indexes: This is a performance tuning method to process the records.
    Synonyms: This is a name for tables.

28) What is the usage of Save Points in Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There are maximum 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

29) What is the difference between post-database commit and post-form commit?

The post-database commit trigger is executed after Oracle forms issue the commit to finalized transaction while, the post-form commit is fired during the post and commit transactions process, after the database commit occurs.

30) What is Logical backup in Oracle?

Logical backup is used to read a set of database records and writing them into a file. An Export utility is used to take the backup while an Import utility is used to recover from the backup.

31) What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group. If the group fails, the database automatically switches over to the next group. It diminishes the performance.

32) What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various processes is known as recursive hint. Recursive hint is occurred because of the small size of data dictionary cache.

33) What are the limitations of CHECK constraint?

The main limitation of CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries.

34) What is the use of GRANT option in IMP command?

GRANT is used to import object grants.

35) What is the use of ROWS option in IMP command?

The ROWS option indicates whether the table rows should be imported.

36) What is the use of INDEXES option in IMP command?

The INDEXES option is used to determine whether indexes are imported.

37) What is the use of IGNORE option in IMP command?

The IGNORE option is used to specify how object creation errors should be handled.

38) What is the use of SHOW option in IMP command?

The SHOW option specifies when the value of show=y, the DDL within the export file is displayed.

39) What is the use of FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

40) How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert date to character. You can also specify the format in which you want output.

    SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;  

Or,

    SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;  

41) What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.

Let's see a procedure call which lists two actual parameters named empno and amt:

Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.

Following procedure declares two formal parameters named empid and amt:

    PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;  

42) What are the extensions used by Oracle reports?

Oracle reports are use to make business enable with the facility to provide information of all level within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

43) How to convert a string to a date in Oracle database?

Syntax: to_date (string , format)

Let us take an example :

    to_date ('2012-12-12', 'YYYY/MM/DD')  

It will return December 12, 2012.

44) How do you find current date and time in Oracle?

The SYSDATE() function is used in Oracle to find the current date and time of operating system on which the database is running.

    SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL;  

45) What will be the syntax to find current date and time in format "YYYY-MM-DD"?

    SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Date" FROM DUAL;  

benefits of factory pattern c# - understanding factory pattern with example

Factory pattern is a creational design pattern which facilitates:
  • Creating objects without exposing the instantiation logic to client
  • Referring to the newly created objects through a common interface
 whenever you come across words like kinds or types, watch out for application of factory pattern!! At the end of the day, it all depends on what the project demands. 

Using the Code

There are plenty of places where we can apply the Factory pattern. But at this moment, let’s consider a familiar example of a Lodging Inquiry system, wherein a customer may wish to get details of different types of available Rooms. Lodging system provides three different types of Rooms (DeluxeRoom, NonACRoom or an ACRoom). Based on the client choice, my system provides the details of specific room. All very well till this point. But just think about how my system might have been designed here?
With no knowledge of factory pattern, a system would have an Interface IRoomType which defines the common behavior for all types of room. All the product-classes (namely ACRoom, DeluxeRoom and NonACRoom) implement this interface. Fair enough till this end, now as per the user request, my client application will instantiate the desired class by directly using the new keyword.
factory pattern



Typically, the client-code would appear quite like this:

private void GetRoomDetails(string roomType)
{
    IRoomType room = null;

    switch (roomType)
      {
       case RoomTypes.AC:
                 room = new ACRoom();
                 break;

            case RoomTypes.Deluxe:
                 room = new DeluxeRoom();
                 break;

            case RoomTypes.NonAC:
                 room = new NonACRoom();
                 break;
      }
      room.GetDetails();
}
So, what is the problem with this type of implementation? Well, before describing the problem, I would like to define one common term “Client application”, which I will use quite frequently. Here, I am referring to any window application that can accept, display and store data is said to be client application. Let’s get back to the real problem.

  1. Firstly, we have lots of ‘new’ keyword scattered in the client application. In other words, the client application is loaded with a lot of condition based object creational activities which can make the client logic very complicated. What if tomorrow new types of Rooms are added in the lodge? Client application code needs to be changed!
  2. Client application has to be aware of all the concrete products (ACRoom, NonACRoom or DeluxeRoom) and their instantiation logic. This methodology often introduces a great deal of the inflexibility in the system, as the direct use of a language/run-time provided object instantiation function creates an explicit association between the creator and created classes.
  3. The coupling introduced between classes is extremely difficult to overcome if the requirements change (as they always do). Just imagine, there comes a season special offer wherein each Non-AC room would be available with a Deluxe Room free of cost! In this case, the constructor of NonACRoom class takes a reference to DeluxeRoom as an argument. Now again, the client application needs to be modified and I am sure after listening to such a drastic change, the developer will get a heart attack.
  4. The above mentioned issues might be easy to deal in the case of a small project. But,
    • What if the client code is huge and too complex to be modified and retested?
    • And what if this inquiry system is being used by multiple lodges in a city?
In these cases, only God can save the whole application.
The failure of the above design can be overcome with the aid of Factory pattern. Factory pattern intends to overcome these drawbacks by delegating the task of object creation to a factory-class. The factory-class completely abstracts the creation and initialization of the product from the client-application. This indirection enables the client to focus on its discrete role in the application without concerning itself with the details of how the product (ACRoom, NonACRoom or DeluxeRoom) is created. Thus, as the product initialization changes over time or new types of product get added to the system, the client remains unchanged. The flow diagram for the same goes like this:

factory pattern
  

 The Factory pattern has evolved over time and majorly there are four different ways of implementing this pattern. Throughout the article, I will take Lodging Inquiry system as an example and will describe the pros and cons with each type of implementation. Before going forward, please synchronize with the attached source code.

  1. Implementation-1: Procedural Solution/Basic noob implementation also known as parameterized factory. In noob-implementation, we define a singleton-factory-class said to be RoomFactory class. RoomFactory class is responsible to implement a special-function (say GetRoomType). GetRoomType function accepts the user requests as a function-parameter and returns specific product-object, by directly using the ‘new’ keyword. Each product class (say ACRoom, DeluxeRoom or NonACRoom) must have to implement the common interface (IRoomType). IRoomType interface defines the basic properties for room.
     
    Noob-implementation removes the scattered ‘new’ from client code and gets the same in RoomFactory class. This way client-code remains less complicated and unchanged when new products get added to the system. The following code-snippet depicts the implementation of GetRoomType method in RoomFactory class. 
    internal IRoomType GetRoomType(RoomTypes roomType)
    {
        IRoomType room = null;
    
        switch (roomType)
        {
            case RoomTypes.AC:
                 room = new ACRoom();
                 break;
    
            case RoomTypes.Deluxe:
                 room = new DeluxeRoom();
                 break;
    
            case RoomTypes.NonAC:
                 room = new NonACRoom();
                 break;
         }
         return room;
     }
     
    In noob-implementation of factory pattern, the code snippets for client-code will be as follows:

    private void SubmitButton_Click(object sender, EventArgs e)
    {
        RoomTypes rmType = (RoomTypes) "ACRoom";
        IRoomType roomType = RoomFactory.Singleton.GetRoomType(rmType);
        roomType.GetDetails();
    }
    Advantages and disadvantages of this implementation are as follows:

    Advantages

    • Easy to implement
    • Client application code doesn’t have to change drastically
    • Moreover, the tight coupling between client and product classes is overcome and turned into coupling between factory and product classes. Hence client need not know the instantiation logic of products.

    Disadvantages

    • If we add any new product (room), we need a new case statement in GetRoomType method of Factory class. This violates open/closed design principle.
    • We can avoid modifying the Factory class by using sub classing. But sub classing means replacing all the factory class references everywhere through the code.
    • We have tight coupling between Factory class and products

Advantages of Factory Pattern

One of the eminent facets of a good software design is the principle of loose-coupling or de-coupling, i.e., reducing or removing class dependencies throughout the system. Factory class removes the dependency on the specific product classes generated by the Factory from the rest of the system. The system is only dependent on the single interface type that is generated by the Factory. Loosely coupled or decoupled code is easier to test or reuse since the code has fewer dependencies on other code.

C# - Types of Polymorphism in C#.Net with Examples

Polymorphism means many forms (ability to take more than one form). In Polymorphism poly means “multiple” and morph means “forms” so polymorphism means many forms.

In polymorphism we will declare methods with same name and different parameters in same class or methods with same name and same parameters in different classes. Polymorphism has ability to provide different implementation of methods that are implemented with same name.

In Polymorphism we have 2 different types those are

        -   Compile Time Polymorphism (Called as Early Binding or Overloading or static binding)

        -   Run Time Polymorphism (Called as Late Binding or Overriding or dynamic binding)

Compile Time Polymorphism

Compile time polymorphism means we will declare methods with same name but different signatures because of this we will perform different tasks with same method name. This compile time polymorphism also called as early binding or method overloading.

Method Overloading or compile time polymorphism means same method names with different signatures (different parameters)

Example


public class Class1
{
public void NumbersAdd(int a, int b)
{
Console.WriteLine(a + b);
}
public void NumbersAdd(int a, int b, int c)
{
Console.WriteLine(a + b + c);
}
}
In above class we have two methods with same name but having different input parameters this is called method overloading or compile time polymorphism or early binding.


Run Time Polymorphism

Run time polymorphism also called as late binding or method overriding or dynamic polymorphism. Run time polymorphism or method overriding means same method names with same signatures.

In this run time polymorphism or method overriding we can override a method in base class by creating similar function in derived class this can be achieved by using inheritance principle and using “virtual & override” keywords.

In base class if we declare methods with virtual keyword then only we can override those methods in derived class using override keyword

Example


//Base Class
public class Bclass
{
public virtual void Sample1()
{
Console.WriteLine("Base Class");
}
}
// Derived Class
public class DClass : Bclass
{
public override void Sample1()
{
Console.WriteLine("Derived Class");
}
}
// Using base and derived class
class Program
{
static void Main(string[] args)
{
// calling the overriden method
DClass objDc = new DClass();
objDc.Sample1();
// calling the base class method
Bclass objBc = new DClass();
objBc.Sample1();
}
}
If we run above code we will get output like as shown below

Output

----------------------------------
Derived Class
Derived Class

In this way we can implement polymorphism concept in our applications.

benefits of web api over mvc

ASP.NET MVC allows you to expose functionality through action methods. One can also expose the functionality as a Web API. Beginners often find it confusing to decide when to go for an MVC controller driven approach and when to go for a Web API. Here are five main considerations that can be helpful while taking a decision.

1. Expose functionality within a Web Application or create a full-fledged REST Service?


The basic consideration is whether you wish to expose a functionality within one specific application or you wish to expose it as a generic functionality independent of any specific application. In the former case ASP.NET MVC controllers may serve your needs. A controller is usually tied to a one particular web application. It can expose functionality that can be quickly consumed through Ajax. Instead of creating a whole new API this controller based approach can be quick and easy for exposing functionality for a given web application. The later approach is good if you wish to create a full-fledge REST service that is not tied to just a single application. Many client applications want to consume this service to get their job done. In such cases a Web API offers a more elegant and neat solution. Generally speaking if your functionality is data centric (for example, CRUD operations) then Web API serves well whereas if your functionality is UI/View centric (loading HTML fragments, Ajax driven pages) then MVC controllers are a natural choice.


2. What data formats you want to deal with?


A controller usually returns ActionResult or JsonResult. That means the output of a controller is typically HTML markup or JSON formatted data. If these data formats suffice your needs then action methods can be used to expose your functionality. However, if you need multiple data formats such as XML and JSON then Web API allows an easy way to configure them. Web API decides the data format automatically based on the Accept header. MVC controller, on the other hand, requires you to explicitly specify the data format (ActionResult or JsonResult) while writing the action methods.


3. Do you need content negotiation?


Content negotiation refers to returning content in a format as indicated by Accept header. Using Web API you can send content to the client in variety of formats such as images or files (not just XML or JSON). Although this is a nice features for an API framework such as Web API, not all applications need it. In most of the cases sending data as JSON or XML is what you need. So, this feature of Web API won't be of much use to you if you are sending data to and from a client.


4. Do you need self-hosting?

If you expose a functionality through a controller, you must host it in IIS. This is obvious because controller is part of your ASP.NET MVC application and requires IIS as the hosting environment. Web API being a service framework allow you to host an API in a custom host (self-hosting). In this case you can avoid overheads of IIS and host a Web API in a lightweight custom host. This is typically used where a service is to be consumed by wide array of clients such as desktop applications, web applications and/or even console applications. 


5. How important are the method signatures?


By default Web API uses HTTP verb based mapping for invoking methods. For example, if you make a request with POST verb then Web API will invoke its Post() method. Moreover, all the request data is wrapped in a single parameter and passed to the method under consideration. This makes the Web API action names and signatures bit rigid. You cannot, for example, have a method that has, say, five parameters. Similarly you can't have multiple methods for a same HTTP verb (except GET where you can have two methods). So, to develop a Web API you need to be aware of these design restrictions. In case of MVC controller, you don't have such restriction. MVC model binding takes care of mapping the request data with the appropriate parameters of an action method.
These are just some of the primary considerations that will help you evaluate a given scenario. You can also use both the techniques in a single web application.

benefits of entity framework over ado.net

Entity Framework is highly recommended technology to build any complex system. Entity Framework is more powerful then ADO.NET and LINQ to SQL. Object Relational Mapping (ORM) is main technology that Entity Framework is used. ORM will generate business objects according to database structure. ORM reduce work code and very simple to use. Developer don't need to write SQL statements to access data. Five reasons for using an ORM tools to get Advantage of ORM.

Here we have discuss several Advantages which give more awareness about Entity Framework.

Advantage of Entity Framework

1. EF reduce code by creating Model instead of create class to access data.

2. Easy and fast Functionality for select, Insert, update ,delete and other CRUD operation.

3. Data access code is under source control. If any Database Modification required, no need to change
    Data access logic. You have to just change  model or business object.

4. Easy to manage relationship between tables. 

5. Faster Development approach then ADO.NET.

6. Code is also usually much neater and more maintainable

7. Conceptual model can be represented in a better way.

Benefits of singleton pattern in c#

The Singleton pattern is probably the most famous and at the same time the most controversial pattern known to us. It must be also be the simplest pattern to learn and implement. Like any other pattern, Singleton exists to solve a common business problem that is ‘managing the state of a resource’. But does it solve the real problem or introduce additional problems?

Following are the benefits or good things about the singleton pattern.


One of the toughest issues to debug is the one created by the multiple instances of a class which manages the state of a single resource. It is highly desirable if we can use some Design Pattern to control the access to that shared resource. The Singleton pattern fits the bill perfectly to solve this scenario; by wrapping a singleton class around this problem ensures that there will be only one instance of the class at any given time. A most common and clichéd example for a singleton class is the one used for logging purposes where the whole application needs only one logger instance at anytime.
The anatomy of a singleton class is very simple to understand. The class typically has a private constructor which will prohibit you to make any instance of the singleton class; instead you will access astatic property or static function of the singleton class to get the reference of a preconfigured instance. These properties/methods ensure that there will be only one instance of the singleton class throughout the lifetime of the application.
The one and only instance of a singleton class is created within the singleton class and its reference is consumed by the callers. The creation process of the instance can be done using any of the following methods:

1. Lazy Instantiation

If you opt for the lazy instantiation paradigm, then the singleton variable will not get memory until the property or function designated to return the reference is first called. This type of instantiation is very helpful if your singleton class is resource intense.
However, the above implementation is not taking any precautions to be thread safe. That is, there may be situations like two or more threads accessing the Instanceproperty at the same time which will create more than one instance of the singleton class.
We can use various thread synchronization techniques to combat the circumstances said above. One way is the use of double-checked locking. In double-checked locking, synchronization is only effective when the singleton variable isnull, i.e., only for the first time call toInstance. This helps us to limit the performance penalty that comes along with the synchronization object to only happen once.

2. Static Initialization

In static initialization, memory is allocated to the variable at the time it is declared. The instance creation takes place behind the scenes when any of the member singleton classes is accessed for the first time. The main advantage of this type of implementation is that the CLR automatically takes care of race conditions I explained in lazy instantiation. We don't have to use any special synchronization constructs here. There are no significant code changes in the singleton implementation when you switch from lazy instantiation to static initialization. The only change is that the object creation part is moved to the place where we are declaring the variable.

Inheritance of Singleton Class

Inheriting a singleton class should be prohibited. Making a singleton class inheritable means any number of child classes can inherit from it creating multiple instances of the singleton class which will obviously violate the principle of singletons.

Singleton Class vs. Static Methods

Singleton takes over static classes on the following shortcomings:
  1. Static classes don’t promote inheritance. If your class has some interface to derive from, staticclasses makes it impossible.
  2. You cannot specify any creation logic with static methods.
  3. Static methods are procedural code.

45 Oracle interview questions with answer

Following is the Oracle interview questions with answer Oracle is a secured database that is widely used in multinational companies. Th...