PLSQL interview questions - part3

PL/SQL Interview Questions

oracle pl sql interview questions for 3+ years experience

41.What is Referential Integrity and Referential integrity constraint ?
Ans: Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
42.What is groups by and having clause? Explain with example
Ans: Group by clause tells oracle to group rows based on distinct values that exists for specified columns. The group by clause creates a data set , containing several sets of records grouped together based on condition.
Having Clause: Having clause can be used with GROUP BY clause. Having imposes a condition on the group by clause which further filters the group created by the GROUP BY clause. Select ename,empno From Empl Group by empno having empno > 10;
43.What are LOCKS? What are types of different types of Lock?
Ans: Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data. Locks are used to achieve two important database goals : Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. Integrity : Ensures that the database's data and structures reflect all changes made to them in the correct sequence.
Types of Locks :
1. Data Locks (DML)
2. Dictionary Locks (DDL)
3. Internal Locks and Latches
4. Distributed Locks
5. Parallel Cache Management Locks
Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level
1. Row Share Table Locks (RS)
2. Row Exclusive Table Locks (RX)
3. Share Table Locks (S)
4. Share Row Exclusive Table Locks (SRX)
5. Exclusive Table Locks (X)
Dictionary Locks :
1. Exclusive DDL Locks
2. Share DDL Locks
3. Breakable Parse Locks Restrictiveness of Locks : In general, two levels of locking can be used in a multi-user database: • Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released. • Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.
44.Difference between unique key,primary key and foreign key ?
Ans: Foreign key: A foreign key is one or more columns whose values are based on the primary or candidate key values from another table. Unique key can be null; Primary key cannot be null.
45.What are Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?
Ans: The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.
1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.
2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).
3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.
5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.
46.What are steps involved in Execution of SQL statements?
1. Create a cursor
2. Parse the statement
3. Describe Results
4. Defining outputs
5. Bind any variables
6. Execute the statement
7. Fetch rows of a query result
47.What do you mean by Parsing?
Ans: Parsing : Parsing is the process of: 1. Translating a SQL statement, verifying it to be a valid statement 2. Performing data dictionary lookups to check table and column definitions 3. Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing 4. Checking privileges to access referenced schema objects 5. Determining the execution plan to be used when executing the statement 6. Loading it into a shared SQL area 7. For distributed statements, routing all or part of the statement to remote nodes that contain referenced data
48.What is a HINT and what are types HINT?
Ans: Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.
 ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.
FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.
 FULL : The FULL hint explicitly chooses a full table scan for the specified table.
ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.
CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.
 HASH : The HASH hint explicitly chooses a hash scan to access the specified table.
INDEX : The INDEX hint explicitly chooses an index scan for the specified table.
AND_EQUAL: The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. (You can specify multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.
INDEX_DESC: The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.
 ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.
USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loops join using the specified table as the inner table.
USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.
49.What do u mean by EXCEPTION_INIT Pragma ?
Ans: EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called "pseudoinstructions") are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler. The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package
using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where "exception_name" is the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is "no data found," in which case SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
What do u mean by JSP query?
Ans: JSP Query : The JSP Query is a standard query for number to words conversion, used especially for converting amount in number into equivalent amount in words. The query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ), ‘JSP’ ) words from dual; For eg : Select to_char ( to_date ( '23949','j' ), 'JSP' ) "words" from dual; The value that can pass to &no cannot exceed 7 digits.
50.Describe Oracle database’s physical and logical structure ?
Ans: Physical: Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.

                 Part1                     Part2                     Part3                     Part4                     Part5