Total Pageviews

Saturday, March 9, 2013


SQL PLSQL Q&A

1. What is Normalization?
Ans: Normalization is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalization rules.
1st Normal Form: A table is said to be in 1st normal form when the attributes are atomic and there is no repeating groups
2nd Normal Form: A table is said to be in 2nd Normal Form when it is in 1st normal form and all the non-key columns are functionally dependant on the primary key. .
3rd Normal Form: A table is said to be in 3rd Normal form when it is in 2nd normal form and all non key attributes not dependant transitively.
4th Normal Form: A table is said to be in 4th normal form when it is in 3rd normal form and has no multi -valued dependencies.
5th Normal Form: A table is said to be in 5th normal form when it is in 4th normal forma and every join dependency for the entity is a consequence of its candidate keys.
2. What is the Purpose of the Distinct Clause in SQL?
Distinct Clause allows you to display unique from the result set. This can be used with only select statements.
3. What are the DDL Commands and the Purpose of these commands?
DDL (Data Definition Language) command is used for defining the structure of the Data. DDL Statements are auto commit.
  • CREATE - to create objects in the database
  • ALTER - alters the structure of the database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object
4. What are the DML commands and Use Of these commands?
DML (Data Manipulation Language) statements are used for managing data within schema objects.
  • INSERT - insert data into a table
  • UPDATE - updates existing data within a table
  • DELETE - deletes all records from a table, the space for the records remain
v DML Statements can be roll backed.
v DML Statements can’t be roll backed When DDL Statement Executed immediately after the DML statement.
5. What are the DCL Commands and purpose of it?
 DCL is Data Control Language statements.
  • GRANT - gives user's access privileges to database
  • REVOKE - withdraw access privileges given with the GRANT command
6. What are the TCL Commands and Purpose of it?
(Transaction Control Language) Manages the changes made by DML statements. These commands allow statements to be grouped together into logical transactions.
  • COMMIT - save work done
  • SAVEPOINT - identify a point in a transaction to which you can later roll back
  • ROLLBACK - restore database to original since the last COMMIT
7. What is the Difference between TRUNCATE and DROP?
Truncate Delete the entire data from the table and keeps the structure.
TRUCATE TABLE table name
DROP drops the table structure also.
DROP TABLE Table name
8. What is the Difference between TRUNCATE and DELETE?
TRUNCATE
DELETE
It’s DDL Statement
It’s DML Statement
Auto commit, we can’t retrieve the data back
we can Retrieve the data back
We can delete entire rows (No condition)
we can delete rows conditional wise
9. What is NULL?
NULL in Oracle is an Absence of information. A NULL can be assigned but not evaluated by it self also.
v NULL not equal to null
v NULL Can not be Not equal to NULL (Neither Equal Not Not Equal)
v NULL Does not equal to empty String or doe not equal to ZERO.
10. How can we sort the rows in SQL?
We can Sort the rows Using ORDER By clause.
- ASC : Ascending Order is Default order
- DESC : Sorting in Descending
v Null Values Displayed At last in ascending Order
11. How can we convert NULL Value?
Using NVL Function we can convert Null Value to an Actual Value.
NVL(exp1, exp2).
If exp1 is NULL then it returns the exp2.
12. Purpose and Syntax of NVL2 ?
Convert the Null values in to Actual Value.
NVL2 (Exp1,exp2,exp3). If exp1 is NULL it returns the exp3 . if exp1 is not null then it returns the exp2.
13. When Cartesian product formed?
  • A join condition Omitted
  • A Join condition Invalid
v To Avoid Cartesian Product, always include Valid Join condition
14. What type of joins using in SQL?
1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key relation ships.
2) NON-EQUI JOIN:
A join condition where any relation operator other than "=" equal to
operator is used.
3) OUTER JOIN:
In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example: consider EMO and DEPT table as Example
DEPTNO 40 is not displayed in the Equi example because there are no employees in it. If we want to diplay its detail also then we have to use OUTER JOIN.Otherwise OUTER JOIN is imilar to EQUI JOIN except for the difference it uses outer join (+) operator. (A plus within parenthesis) towards the side not having required data. Outer join operator will substitute null values when there are no values available.
SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D
2 WHERE E.DEPTNO (+) = D.DEPTNO;
4) SELF JOIN:
When we join a table to itself it is called self join.To join a table itself means that each row of the table is combined with itself and with every other row of the table. The self join can be seen as join of two copies of the same table.
SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO;
15. What are the Group Functions?
Group Functions Operate on Sets of rows to give one result per group. The types of group functions
AVG,COUNT,MAX, All columns in SELECT List that are not in group functions must be in the GROUP BY clause. MIN,SUM,STDDEV,VARIANCE
16. Can you Use Group functions in the Where Clause?
NO, We Can’t.
17. How can we restrict the Group Results?
Using HAVING Clause. We can’t use WHERE for these results.
18. What is difference Between SUBQUERY and CORRELATED SUBQUERY?
SUBQUERY :
A query within another quey. A select statement whose output is substituted in the condition of another select statement .(A query is a statement written for returning specific data). The subquery is executed only once. A subquery is enclosed in parenthesis.
EX: SQL> SELECT ENAME FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = 'SMITH');
CORRELATED QUERY:
In a correlated subquery the table used in outer query refers to the table used in the inner query. The correlated subquery is executed repeatedly once
for each row of the main query table.
Query to diplay name of highest salary taker.
SQL> SELECT EMPNO, ENAME FROM EMP A
WHERE 1 > ( SELECT COUNT(*) FROM EMP B
WHERE A.SAL < B.SAL)
19. What are the Multiple-Row comparisons Operators?
IN :EQUAL to any member in the list.
ANY : Compare value to each value returned by the sub query
ALL :Compare Value to Every value returned by the sub query.
20. You are updating the table, you ask some another user to logon to database to check your changes before you issue the commit command ? Can he see the changes done by you?
Another user can’t see the the changes done by you until you have given commit.
21. What is MERGE Statement do?
Provides the ability to conditionally update or insert data into a database table.
Performs update if the row exists and an insert if it is a new row.
22. What is the Use of SAVEPOINT?
A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINT to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.
Ex : Insert Statement
SAVEPOINT A
UPDATE Statement
SAVEPOINT B
DELETE Statement
SAVEPOINT C
Roll Back to SAVEPOINT B (means it roll backs to till UPDATE statement)
23. What is the Use of ALTER Statement?

To Add new column
To modify the datatype and size of the existing column
To Drop a column
24. What are the Difference between UNION and UNION ALL?
UNION Query displays the Unique rows ( No duplicate rows)
UNION ALL Query displays the Duplicate rows also.
25. If you a Table A, You want to create table B having the same fields in TABLE A ? That means you have to copy only structure not the data?
CREATE TABLE TABLEB AS (SELECT * FROM TABLE A where 1=2);
26. What is Synonym?
A synonym is an alternative permanent name for objects such as tables,views,sequences,stored Procedures
27. What is view?
A View is a virtual table ,it does not physically exist rather , it is created by a query joining one or more tables.
28. Can we Update the Data in View?
A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.
29. What is Sequence?
Sequence is for generating auto number field. This can be useful when you need to create a unique number to act as primary key.
Syntax:
 CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
30. How do we set the LASTVALUE value in an Oracle Sequence?
You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.
31. What is pseudo columns ? Name them?
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
32. How many columns can table have?
The number of columns in a table can range from 1 to 254.
33. How many rows and Columns in DUAL table?
One Row and One Column Only
34. What is the Use of CASE and DECODE?
CASE and DECODE statements Both perform procedural logic inside a SQL statement without having to resort to PL/SQL.
Syntax:
DECODE (F1,E2,E3,E4) { If F1=E2 Then E3 else E4}
Syntax:
CASE
WHEN E1 THEN E2 {If E1 True E2 Else E3 CASE evaluated the Expression only once with
ELSE E3 that result values be compared}
END
v It is Best to use CASE Statement when comparing ranges or more complex logic
35. What is Inline View?
SQL Statement in the FROM clause of SQL statement called Inline View. Oracle treats the data set that is returned from the inline view as if it were a table.
This is not a schema Object
SELECT * from (SELECT * from table);
36. What is the Purpose of Index?
SQL indexes are used because they can provide the following benefits / functions:
· Rapid access of information
· Efficient access of information
· Enforcement of uniqueness constraints
CREATE INDEX (Index_name) on TABLE_NAME(Field1,field2);
37. What are Constraints? And what are the constraint Types?
v Constraints Enforced rules at the table level.
v Constraints prevent the deletion of a table if there are decencies.
Following are the Constraint Types
NOTNULL:
Ensures the null values are not permitted for the column. Defined at column Level.
CREATE TABLE Tablename( Empname VARCHAR2(10) NOTNULL)
UNIQUE:
Not allow already existing value
Is defined either table level or column level
CREATE TABLE T1( X1 NUMBER,
X2 VARCHAR2(10),
CONSTRAINT x2_UK UNIQUE(X2))
PRIMARY KEY:
Doesn’t allow Nulls and already existing values.
CREATE TABLE T1( X1 NUMBER,
X2 VARCHAR2(10),
CONSTRAINT x2_UK PRIMARY KEY(X2))
FOREIGN KEY:
Foreign Key defines the column in the child table at table constraint level.
CREATE TABLE T1( X1 NUMBER,
X2 VARCHAR2(10),
CONSTRAINT x2_UK FOREIGN KEY(X2)
REFERENCES TABLE2(field2))
CHECK
Defines a condition that each row must satisfy
CONSTRAINT emp_salary CHECK(SAL>0)
38. What is the Purpose ON DELETE CASCADE?
Deletes the dependent rows in the child table, when a row in parent table is deleted.
39. How can you view the constraints?
User_constraints table
40. Can we perform the DML Operations On View?
v You can perform DML operations on Simple view (selecting view from one table and also all not null columns selected).
v If view is complex View and View contains the following then we can’t modify the view
--GROUP Functions
-- A Group By clause
-- DISTINCT Keyword
-- Not null columns in Base table that are not selected by View
41. How to deny the DML operation on simple View?
CREATE a view with ‘WITH READ ONLy’ option
42. When to create an Index?
You should create an index if :
  • A Column contains a wide range of values
  • A Column contains a large number of null values
  • One or more columns frequently used together in join condition
  • Table is large and most queries expected to retrieve less than 2 to 4% of the rows.
43. When Not create an Index?
You should not create an index if:
TABLE is Small
The columns are not often used as a condition in the query
The table is updated frequently
44. What is Functional Based Index?
A functional Based index is an index based on expression
CREATE INDEX ind_name table_name (UPPER(field_name))
45. What is the result of the following Command?
SELECT 1 FROM DUAL
UNION
SELECT ‘A’ FROM DUAL
Error : Expression Must have the same datatype as corresponding expression.
46. What is the difference between alias and Synonym?
Alias is temporary and used in one query. Synonym is Permanent aliasing
47. Can Dual table be deleted or dropped or altered or inserted?
YES,we can do
48. What Is the result for the following queries?
1) SELECT * from emp where rownum < 3
2) SELECT * from emp where rownum =3
1) 2 rows selected
2) No rows selected
49. Can we create index on View?
We can’t create index on view.
50. How to eliminate the duplicate rows?
Using ROWID we can delete duplicate rows.
 DELETE FROM EMPMASTER A WHERE A.ROWID> (SELECT MIN(B.ROWID) FROM EMPMASTER B WHERE A.EMPNO=B.EMPNO);
51. How can we find the nth salary in the table?
 SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL)
52. What is the ROWID?
ROWID is pseudo column. It uniquely identifies a row with in a table, but not with in the database.
53. What is the ROWNUM?
ROWNUM is pseudo column to limit the number of returned rows. This behaves like a table column but is not actually stored in tables.
54. What is the difference between Group by and Order by?
Group by is used to group set of values based on one or more values.
Order by is to sort values either in ascending or descending order.
55. How Many CODD rules Oracle satisfies?
Out of 12 , 11 rules Oracle satisfying .
56. What is the difference Between Primary Key and Unique Key?
Primary Key
Unique Key
You can have only one primary key in a table
You can have more than one Unique key in a table
Primary disallows the duplicates and Nulls also
Unique key disallows only duplicates, it accepts the Nulls.
PL/SQL:
1. What is the difference between %TYPE and %ROWTYPE?
%TYPE : It provides the datatype of a variable or database column
%ROWTYPE : It provides the record type that represents a row in a table.
2. What are the main Benefits using %TYPE and %ROWTYPE?
  • You need not know the exact datatype of the field in the table.
  • If you change the database definition (field size increased), datatype declared using %TYPE or %ROWTYPE at the time of runtime it will be take changed database field.
3. What is Collection? What are the collection types using in PL/SQL?
A Collection is an ordered group of elements , all of the same type.
PL/SQL offers these Collections:
a. Nested Tables
b. VArrays
c. Index-By-tables (Associate arrays)
4. Give the Brief description on Nested Tables?
· PL/SQL Nested tables like one dimensional array. Nested tables size unbounded ,So the size of the Nested table can increase dynamically.
· We can delete elements from Nested table using DELETE ( it might leave gaps) and NEXT for iterate over the scripts.
Syntax:
TYPE type_name AS TABLE OF element_type
Ex:
CREATE TYPE Stulist AS TABLE OF VARCHAr2(10)
/
CREATE TYPE student AS OBJECT (
id NUMBER,
name VARCHAR2(10),
stuinfo stulist)
5. What is VARRY?
Varrays allow you to associate a single identifier with an entire collection.
Varray has the maximum size which you must specify in its type definition.
Syntax:
TYPE type_name AS VARRAY(size limit) OF element_type
Ex:
CREATE TYPE Stulist AS VARRAY(50) OF VARCHAr2(10)
/
CREATE TYPE student AS OBJECT (
id NUMBER,
name VARCHAR2(10),
stuinfo stulist)
6. What is the Index-By-Tables (Associated Arrays) ?
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
    
Syntax :
             TYPE type_name IS TABLE OF element type
               INDEX  BY  BINARY_INTEGER
 
Ex:          TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
         INDEX BY BINARY_INTEGER;
         emp_tab EmpTabTyp;
7.What is the difference between Nested tables and Varrays?
Varrays is good choice when the number of elements known in advance and all the elements are usually accessed in sequence.
Nested tables are dynamic. You can delete arbitrary elements rather than just removing an item from end.
8. What is the difference between Nested tables and Index-By-tables(Associated Arrays)?
Nested tables can be stored in database column, but associated arrays can not. Nested tables are appropriate for important data relationships that must be stored persistently.
Associated Arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or Package is initialized. These are good for collecting information whose value is unknown before hand, because there is no fixed limit on their size.
9. Can we Delete Individual Element from VARRAYS?
VARRAYS are dense. We can’t delete elements from VARRAYS.
10. What is bulk binding?
A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding.
For example If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.
Ø This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines
11. Where can you use the FORALL statement?
To do bulk binds with INSERT,UPDATE and DELETE statements you enclose the SQL statement with in a PL/SQL using FORALL
Syntax:
      FORALL index IN lower_bound..upper_bound
   sql_statement;
12. Where you use the BULK COLLECT?
To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.
13. What is a cursor? Why Use a cursor?
When a query executed in oracle, the result set is produced and stored in the memory .Oracle allows accessing this result set in the memory through Cursor.
Need of the cursor is Many times, when a query returns more than one row. We mightwant to go through each row and process the data in different way for them.
14. What are the CURSOR types?
PL/SQL uses 2 types of Cursors
Implicit Cursor:
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row.
Explicit Cursor:
Queries that return more than one row, you must declare an explicit cursor
15. How many ways CURSOR can open?
CURSOR can open in two ways
1) OPEN ---FETCH –CLOSE
Ex:
CURSOR c1 IS
SELECT ename,empno from EMP;
OPEN C1;
LOOP
FETCH ename,empno INTO var1,var2;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
2) FOR LOOP
Ex:
CURSOR c1 IS
SELECT ename,empno from EMP;
FOR c1 in C2 LOOP
Var1 =c2.ename;
Var2 = c2.empno;
END LOOP;
16. What is the difference between OPEN-FETCH-CLOSE and FOR LOOP in CURSORS?
FOR LOOP in CURSOR:
A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
OPEN-FETCH-CLOSE:
Explicitly we have to open the query and closing the query.
17. Can we pass the parameters in CURSOR?
Yes, we can
18. What are the explicit cursors attributes?
%FOUND,%ISOPEN,%NOTFOUND,%ROWCOUNT
19. What are the implicit cursors attributes?
%FOUND,%NOTFOUND and %ROWCOUNT
20. What is the purpose of %ROWCOUNT?
How many rows effected we can know from the %ROWCOUNT. %ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, DELETE or SQL statement.
If INSERT,UPDATE,DELETE statements effected no rows or SELECT statement returns no rows then the %ROWCOUNT value is ZERO.
If SELECT statement returns more than one row and PL/SQL raises the TOO_MANY_ROWS then %ROWCOUNT VALUE is 1. it doesn’t retrieve the actual value.
21. What is the REF CURSOR?
REF CURSOR is a cursor variable. Use of the cursor variables to pass the query result sets between PL/SQL stored subprograms, packages to client.
Syntax:
TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]
Ex:
DECLARE
TYPE DeptCur IS REF CURSOR RETURN dept%ROWTYPE;
Deptcut deptmain;
22. What are the PL/SQL Exceptions?
Pre defined Exceptions:
These Exceptions are raised implicitly by the run-time system.
Ex: NO_DATA_FOUND, ZERO_DIVIDE
User defined Exceptions:
User-defined exceptions must be raised explicitly by RAISE statements.
DECLARE
SAL_check EXCEPTION;
BEGIN
IF SAL <100 THEN
RAISE SAL_CHECK
END IF;
EXCEPTION
WHEN SAL_CHECK THEN
-----
END;
23. How to define our Own Error Messages?
We can define using RAISE_APPLICATION_ERROR.
Syntax:
RAISE_APPLICATION_ERROR ( Error number,error message);
Ø When this called, it ends the subprogram and returns the user defined error message to application.
Ø Error Number range is -20000 to-20999
24. What are SQLCODE and SQLERROR?
SQLCODE: returns the number of the Oracle Error.
SQLERRM: Associated error message for the SQLCODE
25. What are subprograms?
Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called Procedures and functions.
26. Where can we use procedure and Function?
Procedure used for to perform an action. Function to compute Value.
27. Advantages of subprograms?
Extensibility, modularity, reusability and maintainability.
28. Give the procedure Syntax?
 [CREATE [OR REPLACE]]
PROCEDURE procedure_name[(parameter[, parameter]...)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
29. What is AUTHID? What is the Purpose of AUTHID in procedure?
The AUTHID clause determines whether a stored procedure executes with the Privileges of its owner (the default) or current user.
30. What is AUTONOMOUS_TRANSACTION?
Ø The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a
procedure as autonomous (independent).
Ø Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
31. Can we call a function from SELECT Statement?
YES
32. What is forward declaration?
Forward Declaration advises PL/SQL that the body of procedure can be found later in the block, but the specification should be declared first.
33. What are actual and Formal Parameters?
Variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Ex: update_sal(empnum,sal);
Variables declared in subprogram specification and referenced in the subprogram body are formal parameters.
Ex: PRCODURE update_sal(empnum number,sal number)
34. What are the types of notations?
Positional, Named and Mixed notations
Ex: PROCEDURE acc_update (acct_no NUMBER, amount NUMBER);
Positional Notation :
Acc_update(acct,amt);
Named Notation : acc_update(amount =>amt, acct_no => acct);
Or
Acc_update(acct_no=>acct,amount=>amt);
Mixed Notation:
Positional Notation must precede named notation. And the reverse notation is not allowed.
Acc_update(acct,amount => amt);
35. What are the Parameter Modes and what is the default parameter mode?
Parameter Modes are IN,OUT,INOUT
IN parameter is the default parameter mode.
36. In Parameter modes which are pass by Reference and Pass by Value?
Pass By Reference : IN
Pointer to the actual parameter is passed to the corresponding formal parameters. Both Parameters use the same memory location.
Pass By Value :OUT,IN OUT
The Values of OUT Actual parameters copied into the corresponding formal parameters.
37. What is NOCOPY? When we use it?
NOCOPY is Compiler Hint. When the Parameters hold large data structures such as collections and records , all this time copying slows down the execution. To prevent this we ca specify NOCPY. This allows the PL/SQL Compiler to pass OUT and INOUT parameters by reference.
38. What is Table Functions?
Table functions are functions that produce a collection of rows (either a nested table or a Varray) that can be queried like a physical database table or assigned to PL/SQL collection variable. We can use the table function like the name of the database table.
39. What is PL/SQL Package? And what are the parts of the package?
Package groups logically related PL/SQL types, procedures, functions.
Package having the two parts: Package specification and Package Body.
· Package Body is optional in some cases
40. What are the advantages of the Packages?
Modularity, Easier application design, Information hiding, better performance.
· When you call the Packaged Procedure for the first time ,the whole package is loaded in to memory. So later calls to related subprograms in the package require no disk I/O.
· Packages stop cascading dependencies and thereby avoid unnecessary recompiling.
41. What are Private and Public variables in Package?
  • Variables declared in Package Body and restricted to use with in the package those variables are called Private Variables.
  • Variables declared in Package specification and this variable is Visible outside the package ,those variables are called public variables.
42. Which Package can we use to display the output from the PL/SQL blocks or subprogram?
DBMS_OUTPUT
43. Which statement we have to set to display output on SQL*PLUS?
SET SERVEROUTPUT ON
44. How to read and write the text files?
Using UTL_FILE
45. What is Dynamic SQL?
Some Statements can, probably will change from execution to execution means change at runtime, and they are called dynamic SQL statements.
46. What is the need for Dynamic SQL?
  • You want to execute the DDL statements from the PL/SQL block
  • You want to Execute the Control statements from the PL/SQL block
47. How can we execute DDL statements from PL/SQL Block?
Using EXECUTE_IMMEDIATE statement
Ex: EXECUTE_IMMEDIATE(‘TRUNCATE TABLE T1’);
48. What is Trigger? And Define the Parts of the trigger?
Trigger is stored procedure, that run implicitly when an INSERT, UPDATE Or DELETE statement issued against the table or against the view or database system action Occurs.
Parts of the Trigger :
  • Triggering Event or statement
  • Trigger restriction
  • Triggering action
49. What are the types of triggers?
  • ROW Level Triggers
  • Statement Level Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD of Triggers
  • System Event and User event Triggers
50. What is the difference Between Row Level Trigger and Statement Level Trigger?
  • Row level trigger executes once for each row after (or before) the event.This is defined By using FOR EACH ROW
  • Statement Level trigger executes once after (or before) the event, independent how many rows are affected by the event.

51. How can we access the attribute values in triggers?
Using :OLD and :NEW only with Row level trigger
52. Where can we use instead of triggers?
INSTEAD-OF triggers Provide a transparent way of modifying views, that can’t be modified directly through SQL DML statements.
53. What are the System Event Triggers?
System events that can fire triggers are related to instances startup and shutdown and error messages
      • STARTUP
      • SHUTDOWN
      • SERVERERROR
54. What are the User event Triggers?
User events that can fire triggers are related to user logon and logoff, DDL statements
LOG ON
LOG OFF
BEFORE CREATE and AFTER CREATE
BEFORE ALTER and AFTER ALTER
BEFORE DROP and AFTER DROP
55. Can we give the Commit and Roll back in side the Trigger?
NO
56. What is mutating table Error?
A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
57. What is Optimization? What are the types of optimization?
Optimization is the process of choosing the most efficient way to execute SQL statement.
Optimization is in two approaches:
RULE Based
COST Based
58. What is Execution Plan?
Combination of steps Oracle Uses to execute a statement is called an execution plan. Execution plan includes an access method for each table that the statement accesses and ordering of the tables
We can check the execution plan by EXPLAIN PLAN Command.
59. What are the LOCKS providing by ORACLE?
Oracle provides two different levels of locking
      • ROW LEVEL
Each row in the table locked individually
      • TABLE LEVEL
Entire Table Locked
60. What are the Modes of the LOCKING?
    • Exclusive LOCK Mode
    • Share Lock Mode
61. What is exclusive Lock mode?
This Lock prevents the associated resource from being shared. This Lock Mode obtained to modify data.
Ex : LOCK TABLE table_name IN EXCLUSIVE MODE
62. What is Share Lock Mode?
This Lock allows the associated resource to be shared, depending on the operations involved.
Ex: LOCK TABLE table_name IN SHARE MODE
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)