Oracle Database Interview Questions & Answers Set-15

«« Previous
Next »»

1. How many types of triggers exist in PL/SQL?
Ans: 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.

2. What is stored Procedure?
Ans: 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.


3. How to execute a stored procedure?
Ans: There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.
EXECUTE or [EXEC] procedure_name;

Simply use the procedure name
procedure_name;


4. What are the advantages of stored procedure?
Ans: Modularity, extensibility, reusability, Maintainability and one time compilation.


5. What are the cursor attributes used in PL/SQL?
Ans:  %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.


6. What is consistency?
Ans: Consistency simply means that each user sees the consistent view of the data.

Consider an example: there are two users A and B. A transfers money to B's account. Here the changes are updated in A's account (debit) but until it will be updated to B's account (credit), till then other users can't see the debit of A's account. After the debit of A and credit of B, one can see the updates. That?s consistency.


7. What is cursor and why it is required?
Ans: 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.

8. How many types of cursors are available in PL/SQL?
Ans: There are two types of cursors in PL/SQL.

  • Implicit cursor, and
  • explicit cursor

9. What Is SQL*Plus?
Ans: SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
  • Format, perform calculations on, store, and print from query results
  • Examine table and object definitions
  • Develop and run batch scripts
  • Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.


10. How To Start the Command-Line SQL*Plus?
Ans: If you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:\oraclexe\app\oracle\product\10.2.0\server\
>.\bin\sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL>


11. What Information Is Needed to Connect SQL*Plus an Oracle Server?
Ans: If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:
  • The network hostname, or IP address, of the Oracle server.
  • The network port number where the Oracle server is listening for incoming connections.
  • The name of the target database instance managed by the Oracle server.
  • The name of your user account predefined on in the target database instance.
  • The password of your user account predefined on in the target database instance.

12. What Is a Connect Identifier?
Ans: A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.
Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

FYI_XE =
  (DESCRIPTION =
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = dba.fyicenter.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

The above "connect identifier" defines "TNS_XE" with the following connection information:
  • The network hostname: dba.fyicenter.com.
  • The network port number: 1521.
  • The name of the target database instance: XE.

13. How To Run PL/SQL Statements in SQL*Plus?
Ans: If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to FYIcenter!')
Welcome to FYIcenter!


14.What Is Input Buffer in SQL*Plus?
Ans: Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands:
  • LIST - Displays the SQL statement (the last executed SQL statement) in the buffer.
  • RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN.
  • CLEAR BUFFER - Removes the SQL statement in the buffer.
  • INPUT line - Adds a new line into the buffer.
  • APPEND text - Appends more text to the last line in the buffer.
  • DEL - Deletes one line from the buffer.
  • CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer.

15. What Is Commit?
Ans: Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

«« Previous
Next »»