oracle interview questions

Oracle Interview Questions and Answers

156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?

Ans:  Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};

               EXEC SQL PREPARE readytogo FROM :c_sqlstring;

               EXEC SQL EXECUTE readytogo;

157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?

Ans: Oracle8 allows tables and Indexes to be partitioned or broken up into smaller parts based on range of key values. Partitioning is a “divide and conquer” strategy that improves administration and performance in data warehouse and OLTP systems.

159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?

Ans: The basic oracle memory structure associated with Oracle includes:

Software Code Areas The System Global Area (SGA) ,The Database Buffer Cache

The shared Pool, The Program Global Areas (PGA), Stack Areas ,Data Areas, Sort Areas

160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?

Ans: Database, Tablespace , DB Object, Segment, Extents

161. WHAT IS SGA?

Ans: A System Global Area is a group of shared memory allocated by

Oracle that contains data and control information for one Oracle database instance. IF the multiple users are concurrently connected to the same instance, the data in the instance’s SGA is “shared” among the users.

Consequently, the SGA is often referred to as either the “system Global Area” or the “Shared

Global Area”.

162. WHAT IS PGA?

Ans: The Program Global Area is a memory buffer that contains data and

control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.

163. WHAT IS AN ORACLE INSTANCE?

Ans: Every time a database is started, an SGA is allocated and Oracle

background processes are started. The combination of these processes and memory buffers is called an Oracle instance.

164. WHAT ARE DIFFERENT ORACLE PROCESSES?

Ans:  A process is a “thread of control” or a mechanism in an operating

system that can be execute a series of steps. Some operating systems use terms jobs or task.

 A process normally has its own private memory area in which it runs. An Oracle database system has general types of process: User Processes and Oracle Processes.

165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?

Ans: SMON (System Monitor) performs instance recovery at instance of

startup. In a multiple instance system (one that uses the parallel server), SMON of one instance can also perform instance recovery other instance that have failed whereas The PMON (Process Monitor) performs process recovery when a user process fails.

166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?

Ans:  Database is a physical Component

         Tablespace is a Logical component

167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?

Ans: The Data Base Writer writes modified blocks from the database

buffer cache to the data files.

168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?

Ans: The Log Writer writes redo log files to disk. Redo log data is

generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo entries into an online redo log file.

169. WHAT IS RECOVERER?

Ans: The Recover (RECO) is used to resolve distributed transactions that are pending due to network or system failure in a distributed database. At timed intervals, the local RECO attempts to concept to remote database and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

170. WHAT IS ARCHIVER?

Ans:  The Archiver (ARCH) copies the online redo log files to archival storage when they are full. ,ARCH is active only when a database’s redo log is used ARCHILOG mode.

171. WHAT IS A STORED QUERY?

Ans: VIEW

172. WHAT IS USER PROCESS AND SERVER PROCESS?

Ans: A User process is created and maintained to execute the software

code of an application program (such as PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process also manages the communication

with server processes. User processes communication with the server

Processes through the program interface.

Other processes call ORACLE processes. In a dedicated server

configuration, a server Process handles requests for a single user process. A multithread

server configuration allows many user processes to share a small number of server processes, minimizing the utilization of available system resources.

173. WHAT IS A SELF REFERENTIAL INTEGRITY?

Ans: Table related to itself .Foreign key of the table links to primary  key of the same table.

174. WHAT IS A "RAISE" STATEMENT?

Ans: It is used to Raise Exceptions.

175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?

Ans: Rowid is the address of the row at where it is stored in the

database. Rownum is count of records whereas Rowid is identification of the each row

Read More

oracle pl sql interview questions and answers free download

Oracle Interview Questions and Answers

126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?

Ans:  NO.

127. WHAT IS A PACKAGE AND PACKAGE BODY?

Ans: Package is declarative part of the functions and procedures stored

in that package and package body is

the definition part of the functions and procedures of that package.

128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?

Ans: Packages provides Functions or Procedures Overloading facility and

security to those Functions or

Procedures.

129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE SAME NAME?

Ans: NO if it is out side a Package, YES if it is within a Package.

130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?

Ans: YES.

131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?

Ans: Trigger:  A Trigger is a stored PL/SQL program unit associated

with a specific database table.

     Procedure: A Procedure is to be explicitly called by the user

whereas Triggers are automatically called implicitly

    by Oracle itself whenever event Occurs.

132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?

Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE

and Constraints has some limitations whereas Trigger has no limitations.

133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?

Ans: Insert, Update or Delete.

134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL TRIGGERS?

Ans: Table level Triggers execute once for each table based transaction

whereas Row level Triggers will execute once FOR EACH ROW.

135. WHAT ARE AUTONOMOUS TRIGGERS?

Ans: Supports to provide Commit statement in Triggers. Triggers a declared as independent

         Transactions.

136. WHAT IS AN "INSTEAD OF" TRIGGER?

Ans: These Triggers are used with the Complex Views only to make

possible of Insert, Update and Delete on those Views.

137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?

Ans: 18 Triggers

138. WHAT IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?

Ans: ORA-04091:         Table name is mutating, trigger/function may not see it

Cause : A trigger or a user-defined PL/SQL function that is referenced

in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.

Action : Rewrite the trigger or function so it does not read the table.

139. WHEN TO USE ":NEW" AND ":OLD" SPECIFIERS?

Ans:  The prefix :old is used to refer to values already present in the

table. The prefix :new is a correlation name that refers to the new value that  is inserted / updated.

141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?

Ans:  Define variable in declaration section

142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?

Ans: Using CREATE [OR REPLACE] TYPE <type name>

AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command; 

143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?

Ans: Declare the variable in a Package

144. HOW TO CREATE AN OBJECT IN ORACLE?

Ans: Using CREATE [OR REPLACE] TYPE <type name> AS OBJECT (ATTRIBUTE

NAME DATA TYPE,..) Command  

145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?

Ans: The Object created in a table is called Persistent Object.

     Object created on execution of PL/SQL block is called Transient Object.

146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?

Ans: A Column Object is only a Column of a table.

  

147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?

Ans: GRANT <permission> ON <object name> TO <user name>.

148. WHAT IS A COLLECTION OF ORACLE?

Ans: Varray, Nested Table is a collection of Oracle.

149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?

Ans:  Varray has a fixed size.

         Nested tables can carry any number of values.

150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?

Ans: To modify a stored VARRAY it has to selected into a

         PL/SQL variable and then inserted back into the table.

151. WHAT IS USE OF "THE" OPERATOR FOR NESTED TABLE?

Ans: THE operator allows nested tables to be manipulated using DML when

it is stored in a Table.

152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?

Ans: UTL_FILE Package is used for File input/output in Oracle.

153. NAME SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?

Ans: FOPEN, FCLOSE,  FFLUSH, IS_OPEN, GET_LINE, PUT_LINE, PUTF, NEW_LINE

154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?

Ans: SQLJ is basically a Java program containing embedded static SQL

statements that are compatible with Java design philosophy.

 155. WHAT IS AN ITERATOR? Name some TYPES OF ITERATORS?

Ans: SQLJ Iterators are basically record groups generated during

transaction, which requires manipulation of more than one records from one or more tables. There are two types Iterators namely Named Iterator and Positional Iterator. 

Read More

oracle sql interview questions and answers

Oracle Interview Questions and Answers

101. HOW PL/SQL IS DIFFERENT FROM SQL?

Ans: SQL is non-procedural language whereas PL/SQL is procedural

language that includes features and design of programming language.

102. WHAT IS ARCHITECTURE OF PL/SQL?

Ans:   Give picture & Explain

103. WHAT IS A PL/SQL BLOCK?

Ans:     DECLARE

               <declarations>

                BEGIN

               <Exececutable Statements>

            EXCEPTION

               <Exception Handler(s)>

            END;

104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?

Ans: DECLARE BLOCK: In this block all the declarations of the variable

used in the program is made. If no variables are used this block will become optional.

          BEGIN BLOCK: In this block all the executable statements are

          placed. This block is Mandatory.

          EXCEPTION BLOCK: In this block all the exceptions are handled.

                     This block is also very optional.

          END: Every begin must be ended with this END; statement.

105. WHAT ARE COMPOSITE DATA TYPES?

Ans: Records, Tables are two Composite data types.

106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?

Ans: The visuability and accessibility of a variable within the

block(s) is called scope of a variable.

107. WHAT IS A NESTED BLOCK?

Ans: A block within a block is called Nested Block.

  

108. WHAT IS A PL/SQL ENGINE?

Ans:  The PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural part of the statements and sends the SQL statements to the SQL statement executor in the Oracle server.

109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?

Ans: NULL

110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?

Ans: Simple requires declaration of variables used in it and exit

condition but For Loop doesn’t require this.

111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?

Ans: Cursor is Private SQL area in PL/SQL.

     Declare the Cursor,

     Open the Cursor,

     Fetch values from SQL into the local Variables,

     Close the Cursor.

112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?

Ans:  There are two types of cursors namely Implicit Cursor, Explicit Cursor.

113. WHAT IS A CURSOR FOR LOOP?

Ans: Cursor For Loop is shortcut process for Explicit Cursors because

the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

114. WHAT ARE CURSOR ATTRIBUTES?

Ans:   %Found

          %NotFound

          %IsOpen

          %RowCount are the cursor attributes.

115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?

Ans: This Clause stop accessing of other users on the particular

columns used by the cursor until the COMMIT is issued.

116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?

Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

117. NAME SOME BUILT-IN EXCEPTIONS?

Ans:  Too_Many_Rows,  No_Data_Found,   Zero_Divide,    Not_Logged_On

         Storage_Error,    Value_Error etc.

118. HOW TO CREATE A USER-DEFINED EXCEPTION?

Ans: User-Defined Exception is created as follows:

      DECLARE

            <exception name> EXCEPTION;

            - - - - - - - - - ;

            - - - - - - - - -;

            BEGIN

            - - - - - - - - -;

            - - - - - - - - -;

            RAISE <exception name>;

            EXCEPTION

            WHEN <exception name> THEN

            - - - - - - - - -;

            - - - - - - - - -;

            END;

119. WHAT IS "OTHERS" EXCEPTION?

Ans: It is used to along with one or more exception handlers.

        This will handle all the errors not already handled in the block.

120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?

Ans: Exception scope will be with in that block in which exception handler is written.

121. WHAT IS A SUB-PROGRAM?

Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING

PARAMATERS.

122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?

Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.

123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?

Ans: Function has return key word and returns a value whereas a

Procedure doesn’t return any value.

124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION OR PROCEDURE?

Ans: IN, IN OUT, OUT.

125. WHAT IS "IN OUT" PARAMETER?

Ans: A parameter, which gets value into the Procedure or Function and

takes the value out of the Procedure or

Function area, is called IN OUT parameter.

Read More

oracle sql plsql interview questions

Oracle Interview Questions and Answers

76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?

Ans: View is database object, which exists logically but contains no

physical data and manipulates the base table.

View is saved as a select statement in the database and contains no

physical data whereas Table exists physically.

77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?

Ans: Simple views can be modified whereas Complex views (created based

on more than one table) cannot be modified.

78. WHAT IS AN INLINE VIEW?

Ans: Inline view is basically a subquery with an alias that u can use

like a view inside a SQL statement. It is not a schema object like SQL-object.

79. HOW TO UPDATE A COMPLEX VIEW?

Ans: Using  'INSTEAD OF' TRIGGERS Complex views can be Updated.

80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?

Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows  which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.

81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX? 

Ans: An Index is a database object used n Oracle to provide quick

access to rows in a table. An Index increases the performance of the database.

82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?

Ans: Sequence is a Database Object used to generate unique integers  to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.

83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER

WHEN CLUSTERED TABLE EXISTS?

Ans: Cluster and Indexes are transparent to the user. Clustering is a

method of storing tables that are intimately related and are often joined together into the same area on the disk.

When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.

84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?

Ans: Materialized views can be used to replicate data. Earlier the data

was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.

85. WHAT IS A SYNONYM?

Ans:  A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or

package.

86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?

Ans: Only the user or table owner can reference Private synonym whereas

any user can reference the Public synonym.

87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?

Ans:  SQL commands are stored in the buffer whereas SQL*PLUS are not.

88. NAME SOME SQL*PLUS COMMANDS?

Ans:   DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.

89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?

Ans: SPOOL file-name, SPOOL OFF, TTITLE, BTITLE, BREAK ON, COMPUTE <any

aggregate function> OF <column name> [break] ON <column name> etc are

 SQL*PLUS REPORTING COMMANDS.

90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?

Ans: Connect and Resource etc are System Privileges.

Create <object>, Select, Insert, Alter etc are Object Privileges.

           

91. WHAT FOR DCL COMMANDS ARE?

Ans: Commit, Rollback are DCL commands.

92. WHAT FOR GRANT COMMAND WITH "WITH GRANT OPTION"?

Ans: “With Grant Option” with Grant Command gives privileges to the

user to grant privileges to other user(s)

among the privileges he/she has.

93. HOW TO CHANGE PASSWORD OF A USER?

Ans:  Using Password command or

         Using ALTER USER <user name> IDENTIFIED BY <new password> COMAND.

94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?

Ans: A schema is a collection of logical structures of data, or schema objects.

        A schema is owned by the database user and has the same name as that of user.

        Each user owns a single schema. Schema objects include following

        type of objects Clusters, Database Links, Functions, Indexes, Packages,Procedures,         Sequences, Synonyms, Tables, Database Triggers, Views.

95. HOW TO STARTUP AND SHUTDOWN ORACLE DATABASE?

Ans: Startup and Shutdown Oracle database can be done by only the

administator. Startup is done by using STARTUP command and Shutdown is done by SHUTDOWN command

96. WHAT IS A SESSION?

Ans: The period between Login and Logoff on schema.

97. WHAT IS A CLIENT PROCESS? WHAT IS A SERVER PROCESS?

Ans: ref: 172 Q & A.

98. HOW TO MAKE EVERY DML OPERATION AS AUTO COMMIT?

Ans: By using SET AUTOCOMMIT ON command.

99. HOW TO DISPLAY DATA PAGE WISE IN SQL?

Ans: By using SET PAUSE ON command.

100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?

Ans:  By using 

                       SET LINESIZE <value>,

                       SET PAGESIZE <value>,

                       SET SQLPROMPT <new prompt>.

Read More

oracle sql plsql interview questions and answers

Oracle Interview Questions and Answers

51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?

Ans: To arrange the query result in a specified

Order (ascending,descending) by default it takes ascending order.

52. WHAT IS "GROUP BY" QUERIES?

Ans: To group the query results based on condition.

53. NAME SOME AGGREGATE FUNCTIONS OF SQL?

Ans: AVG, MAX, SUM, MIN,COUNT.

54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?

Ans: Count () will count the specified column whereas count (*) will

count total no. of rows in a table.

55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?

Ans: To get subtotals and grand total of values of a column.

56. WHAT IS A SUB-QUERY?

Ans: A query within a query  is called a sub query where the result of

inner query will be used by the  outer query.

57. WHAT ARE SQL OPERATORS?

Ans: Value (), Ref () is SQL operator. ( Used with Objects )

58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?

Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest

value from the set and compares a value to each returned by a sub query.

All: ALL compares a value to every value returned by SQL.

Exists: This operator produces a BOOLWAN results. If a sub query

produces any result then it evaluates it to TRUE else it evaluates it to FALSE.

59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?

Ans: A correlated subquery is a nested subquery, which is executed once

for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column

value used in inner query refers to the column value present in the

outer query forming a correlated subquery.

60. WHAT IS A JOIN - TYPES OF JOINS?

Ans: A join is used to combine two or more tables logically to get

query results.

    There are four types of Joins namely

     EQUI Join

     NON-EQUI Join

     SELF Join

     OUTER Join.

61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?

Ans: There shold be atleast one common column between the joining tables.

62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?

Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement. 

63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?

Ans:  SELF JOIN is made within the table whereas

         EQUI JOIN is made between  different tables having common column.

64. WHAT ARE "SET" OPERATORS?

Ans: UNION ALL,UNION, INTERSECT ,MINUS are SET OPERATORS.

65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL" OPERATORS?

Ans: UNION will return the values distinctly whereas UNION ALL will

return even duplicate values.

66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER FUNCTIONS?

Ans: 

      Number Functions:

                Round (m, [n]),  Trunc (m, [n]),  Power (m, n),  Sqrt(n),

                Abs (m), Ceil (m),  Floor (m), Mod (m, n) ,sign(n)                                 

      Character Functions:

                Chr (x), Concat (string1, string2), Lower (string)

                      Upper (string), Substr (string, from_str, to_str), ASCII (string)

                      Length (string), Initcap (string). 

    Date Functions:

                 Sysdate, Months between (d1, d2), To_char (d, format)

                 Last day (d), Next_day (d, day).add_months(d,n), Extract

   Conversion Functions:     To_char,  To_date, To_number

67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?

Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.

68. WHAT FOR NVL () FUNCTION IS?

Ans: NVL Function helps in substituting a value in place of a NULL.

69. WHAT FOR DECODE () FUNCTION IS?

Ans: It is substitutes value basis and it actually does an

'if-then-else' test.

70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE () FUNCTIONS?

Ans: Translate()   is a superset of functionality provided by Replace().

71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?

Ans:   Substr() will return the specified part of a string whereas

          Instr() return the position of the specified part of the string.

72. WHAT IS A JULIAN DAY NUMBER?

Ans: It will return count of the no. Of days between January 1, 4712 BC

and the given date.

73. HOW TO DISPLAY TIME FROM A DATE DATA?

Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.

74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?

Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.

75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?

Ans:   To_date converts character date to date format whereas

          To_char function converts date or numerical values to characters.

Read More

oracle interview questions and answers free download

Oracle Interview Questions and Answers

26.  HOW MANY COLUMNS CAN A TABLE HAVE?

Ans: A Table can have 1000 columns.

27.  WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?

Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data

types for character values,

Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values,

Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.

28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?

Ans:    LOB     LONG

1) The maximum size is 4GB.

2) LOBs (except NCLOB) can be attributes of an object type.

3) LOBs support random access to data.

4) Multiple LOB columns per table or LOB attributes in an object type.      

1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only

sequential access.

4) Only one LONG column was allowed in a table

29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?

Ans: Varchar2 is similar to Char but can store variable no. Of

characters and while querying the table varchar2  trims the extra spaces from the column and fetches the rows that exactly match the criteria.

30.  HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT

DATE  FORMAT IN ORACLE?

Ans: For Date data type oracle allocates 7 bytes Memory.

  Default Date Format is: DD-MON-YY.

31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?

Ans:

Datatype          Range

Char   Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects

(LOB’s) 2000 bytes  4000 bytes 

Precision 1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary

precision   2 GB  4GB

32.  HOW TO RENAME A COLUMN?

Ans: We can’t rename a Column of a table directly. So we follow the

following steps.

To Rename a Column:

a)         Alter the table specifying new column name to be given and data type.

b)         Then copy the values in the column to be renamed into new column.

c)         drop the old column.

33.  HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?

Ans: To Decrease the size of a Data type of a column

i.          Truncate the table first.

ii.         Alter the table column whose size is to be decreased using the same

name and data type but new size.

34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?

Ans: Constraint: Constraints are representators of the column to

enforce data entity and consistency.There r two levels

1)Column-level constraints 2)Table-level constraints.

35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL ?

Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.

36.  WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?

Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.

37. WHAT IS A COMPOSITE PRIMARY KEY?

Ans: A Primary key created on combination of columns is called Composite Primary Key.

38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?

Ans: It is a part of composite primary key.  Maximum 32 candidate key can be there in composite primary key.

39. HOW TO DEFINE A NULL VALUE?

Ans: A NULL value is something which is unavailable, it is neither zero

nor a space and any mathematical calculation with NULL is always NULL.

40. WHAT IS NULL?  A CONSTRAINT OR DEFAULT VALUE?

Ans: It is a default value.

41.  WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?

Ans: NULL.

42.  WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?

Ans: Index.

43.  WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?

Ans: In this we can't specify Pseudo Columns like sysdate etc.

44.  WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?

Ans: References is used as column level key word where as foreign key

is used as table level constraint.

45. WHAT IS "ON DELETE CASCADE"?

Ans: when this key word is included in the definition of a child table  then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.

46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?

Ans: A table which references a column of another table(using References)is called  as a child table(detail table) and a table  which is being referred  is called Parent (Master) Table .

47.  HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?

Ans: Using "on delete cascade".

48. IS ORACLE CASE SENSITIVE?

Ans: NO

49.  HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?

Ans: By Creating indexes and reference IDs.

50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?

Ans: Columns that are not created explicitly by the user and can be

used explicitly in queries  are called Pseudo-Columns.

Ex: currval,nextval,sysdate,new,old,sqlcode,sqlerrm,rownum,rowid,level

Read More