Oracle Interview Questions

Posted by Admin on 10:00 AM in , | 0 comments

  1. What are the various types of queries ?
Answer: The types of queries are:
    • Normal Queries
    • Sub Queries
    • Co-related queries
    • Nested queries
    • Compound queries
  2. What is a transaction ?
Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
  1. What is implicit cursor and how is it used by Oracle ?
Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
  1. Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Answer: Public synonyms
  1. What is PL/SQL?
Answer: PL/SQL is Oracle’s Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to theOracle database server and a variety of Oracle tools.
  1. Is there a PL/SQL Engine in SQL*Plus?
Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient asSQL statements are not stripped off and send to the database individually.
  1. Is there a limit on the size of a PL/SQL block?
Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = ‘procedure_name’
  1. Can one read/write files from PL/SQL?
Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=…parameter).BeforeOracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(’/home/oracle/tmp’, ‘myoutput’,'W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
  1. How can I protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap iname=myscript.sql.yyy
  1. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’, DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
  • What are the various types of queries ?
Answer: The types of queries are:
  • Normal Queries
  • Sub Queries
  • Co-related queries
  • Nested queries
  • Compound queries
  • What is a transaction ?
Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
  • What is implicit cursor and how is it used by Oracle ?
Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
  • Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Answer: Public synonyms
  • What is PL/SQL?
Answer: PL/SQL is Oracle’s Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to theOracle database server and a variety of Oracle tools.
  • Is there a PL/SQL Engine in SQL*Plus?
Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient asSQL statements are not stripped off and send to the database individually.
  • Is there a limit on the size of a PL/SQL block?
Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = ‘procedure_name’
  • Can one read/write files from PL/SQL?
Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=…parameter).BeforeOracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(’/home/oracle/tmp’, ‘myoutput’,'W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
  • How can I protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap iname=myscript.sql.yyy
  • Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’, DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
  • What are the various types of Exceptions ?
Answer: User defined and Predefined Exceptions.
  • Can we define exceptions twice in same block ?
Answer: No.
  • What is the difference between a procedure and a function ?
Answer: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.
  • Can you have two functions with the same name in a PL/SQL block ?
Answer: Yes.
  • Can you have two stored functions with the same name ?
Answer: Yes.
  • Can you call a stored function in the constraint of a table ?
Answer: No.
  • What are the various types of parameter modes in a procedure ?
Answer: IN, OUT AND INOUT.
  • What is Over Loading and what are its restrictions ?
Answer: OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.
  • Can functions be overloaded ?
Answer: Yes.
  • Can 2 functions have same name & input parameters but differ only by return datatype
Answer: No.
  • What are the constructs of a procedure, function or a package ?
Answer: The constructs of a procedure, function or a package are :
  • variables and constants
  • cursors
  • exceptions
  • Why Create or Replace and not Drop and recreate procedures ?
Answer: So that Grants are not dropped.
  • Can you pass parameters in packages ? How ?
Answer: Yes.You can pass parameters to procedures or functions in a package.
  • What are the parts of a database trigger ?
Answer: The parts of a trigger are:
  • A triggering event or statement
  • A trigger restriction
  • A trigger action
  • What are the various types of database triggers ?
Answer: There are 12 types of triggers, they are combination of :
  • Insert, Delete and Update Triggers.
  • Before and After Triggers.
  • Row and Statement Triggers.
  • What is the advantage of a stored procedure over a database trigger ?
Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
  • What is the maximum no.of statements that can be specified in a trigger statement ?
Answer: One.
  • Can views be specified in a trigger statement ?
Answer: No
  • What are the values of :new and :o ld in Insert/Delete/Update Triggers ?
Answer: INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
  • What are cascading triggers? What is the maximum no of cascading triggers at a time?
Answer: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.
  • What are mutating triggers ?
Answer: A trigger giving a SELECT on the table on which the trigger is written.
  • What are constraining triggers ?
Answer: A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
  • Describe Oracle database’s physical and logical structure ?
Answer:
  • Physical : Data files, Redo Log files, Control file.
  • Logical : Tables, Views, Tablespaces, etc.
  • Can you increase the size of a tablespace ? How ?
Answer: Yes, by adding datafiles to it.
  • Can you increase the size of datafiles ? How ?
Answer: No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause )
  • What is the use of Control files ?
Answer: Contains pointers to locations of various data files, redo log files, etc.
  • What is the use of Data Dictionary ?
Answer: It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc
  • What are the advantages of clusters ?
Answer: Access time reduced for joins.
  • What are the disadvantages of clusters ?
Answer: The time for Insert increases.
  • Can Long/Long RAW be clustered ?
Answer: No.
Tags: ,

About author

Freshersplanet is exclusively for listing fresher jobs, and attracts nearly a million fresh job seekers from different streams. Recruiters can publish any fresher openings, Internships and walk-ins via Freshersplanet to reach to the top quality freshers talent available in the country.

0 comments

Leave a Reply

Newer Post Older Post Home