User:Snazzypragnesh/sandbox

Basic Skill Interview of Oracle Database dministrator.First think consider for oracle dba is Technical knowledge. No of Question are given below for Oracle Admin post.

1. Differentiate between TRUNCATE and DELETE. Ans => The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

2. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function? Ans => 1000000

3. Can you use a commit statement within a database trigger? Ans => Yes, if you are using autonomous transactions in the Database triggers.

4. What is an UTL_FILE? What are different procedures and functions associated with it? Ans => The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

Subprogram -Description FOPEN function-Opens a file for input or output with the default line size. IS_OPEN function -Determines if a file handle refers to an open file. FCLOSE procedure -Closes a file. FCLOSE_ALL procedure -Closes all open file handles. GET_LINE procedure -Reads a line of text from an open file. PUT procedure-Writes a line to a file. This does not append a line terminator. NEW_LINE procedure-Writes one or more OS-specific line terminators to a file. PUT_LINE procedure -Writes a line to a file. This appends an OS-specific line terminator. PUTF procedure -A PUT procedure with formatting. FFLUSH procedure-Physically writes all pending output to a file. FOPEN function -Opens a file with the maximum line size specified

5. What is a cursor ? ( Basic) - Name or handle to a private SQL area where Oracle parses and fetches query results.

6. How to control how many cursors are open ?(Intermediate) - Set OPEN_CURSORS parameter in initialization parameters.

7. What is shared SQL ? (Intermediate) - Oracle recognizes similar statements. The SQL area is used many times for similar statements.

8. What is Parsing ? (Intermediate) - Syntax checking, privileges checking, allocating Private SQL Area.

9.What is the difference between anonymous blocks and stored procedures ? ( Basic) - Anonymous block is compiled only when called. - Stored procedure is compiled and stored in database with the dependency information as well. - Former is PL/SQL code directly called from an application. Latter is stored in database. - Former has declare statement.Latter doesnt.

10.What are the advantages of procedures ? ( Basic) - Loaded once and used many times - Performance better coz all SQL stmts are sent in one go from the application to the database - Security ( no object privileges are given directly ) - Invoker's rights possible - Data integrity, productivity

11.What are standalone procedures ? (Basic) - Those that are not part of package

12.How is a PL/SQL program stored in database ? (Advanced) - Parsed code is stored. It's called P-code

13.How is a PL/SQL program executed ?(Advanced) - Prior to Oracle 9i, we have only bytecode and a virtual machine in the database runs it. Later versions have faster native code execution. - PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.

14.What are the advantages and disadvantages of DBMS_SQL ? (Intermediate) - It has all the advantages of dynamic sql .. like runtime construction of sql, DDL statements can be executed. - Its advantage over EXECUTE IMMEDIATE is it can Describe objects - It's kind of bulky and difficult compared to EXECUTE IMMEDIATE.

15.What is a package spec and package body ? Why the separation ? ( Basic) - Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs - Separation helps make development easier - Dependency is simplified. You can modify body without invalidating dependent objects.

16.What are the advantages of Packages ? ( Basic) - Encapsulation of code logic - Privileges to objects can be controlled - Loaded once into memory, used subsequently. - Dependency simplified - Public/private procs, functions, variables

17.How do you handle exceptions for bulk operations ? (Intermediate) - Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP ) - Use 'Exceptions When Others' to handle the exceptions - SQL%BULK_EXCEPTIONS(i).ERROR_CODE, - SQL%BULK_EXCEPTIONS(i).ERROR_INDEX - SQL%BULK_EXCEPTIONS.COUNT

18.Tell some tips to avoid performance problems in PL/SQL. (Intermediate to Advanced) - Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times - Tune SQL statements to avoid CPU overhead - Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided. - Reorder conditional tests to put least expensive ones first - Minimize datatype conversions => Assign data to exact same type variables - Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to  maintain precision and scale. - Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks - Use BINARY_FLOAT, BINARY_DOUBLE - EXECUTE IMMEDIATE is faster than DBMS_SQL

19.How to know PL/SQL compile parameters ?(Advanced) - SHOW PARAMETERS PLSQL - ALL_PLSQL_OBJECT_SETTINGS

20.What is MERGE ?( Basic) - Combination of INSERT and UPDATE

21. Tell some new features in PL/SQL in 10g (Intermediate to Advanced) - Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR - Compile time warnings - Conditional compilation - Improvement to native compilation - BINARY_INTEGER made similar to PLS_INTEGER - INDICES OF, VALUES OF in FORALL lets you work on non-consecutive indices

22. Quoting mechanism. Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes. - Ex: q'!I'm a string, you're a string.!' - Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN - Implicit conversion between CLOB and NCLOB - Improved Overloading - New datatypes BINARY_FLOAT, BINARY_DOUBLE - Global optimization enabled - PLS_INTEGER range increased to 32bit - DYNAMIC WRAP using DBMS_DDL

23. What is a sequence ? (Basic) - A database object that offers high-speed access to an integer value - Guaranteed to be unique (within that sequence). - Used commonly to generate Primary key values

24. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables? - Data Definition Language (DDL)

25. What operator performs pattern matching? - LIKE operator

26. What operator tests column for the absence of data? - IS NULL operator

27. Which command executes the contents of a specified file? - START or @

28. What is the parameter substitution symbol used with INSERT INTO command? - &

29. Which command displays the SQL command in the SQL buffer, and then executes it? - RUN

30. What are the wildcards used for pattern matching? - for single character substitution and % for multi-character substitution

31 State true or false. EXISTS, SOME, ANY are operators in SQL. - True

32. State true or false. !=, <>, ^= all denote the same operation. - True

33. What are the privileges that can be granted on a table by a user to others? - Insert, update, delete, select, references, index, execute, alter, all

34. What command is used to get back the privileges offered by the GRANT command? - REVOKE

35. Which system tables contain information on privileges granted and privileges obtained? - USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

36. Which system table contains information on constraints on all the tables created? - USER_CONSTRAINTS

37. TRUNCATE TABLE EMP; - DELETE FROM EMP;

38.Will the outputs of the above two commands differ? - Both will result in deleting all the rows in the table EMP.

39. What is the difference between TRUNCATE and DELETE commands? - TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

40. What command is used to create a table by copying the structure of another table? Answer : CREATE TABLE .. AS SELECT command Explanation :To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following. CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2; If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

41(Q) Describe the level of data abstraction? (A) There are three levels for data abstraction Physical level: The lowest level of abstraction describes how data are stored Logical level: Next higher level in abstraction, describes which data is stored and relation between them View Level: This describes the part of entire database

42(Q) List the advantages of DBMS? (A) Here is the list of advantages of using DBMS 1. Redundancy is controlled. (Duplication is controlled) 2. Unauthorized access is restricted. (Restriction others to access data base information) 3. Providing multiple user interfaces. (Providing multi user access also limiting their permission) 4. Enforcing integrity constraints. 5. Providing backup and recovery. (Back memory provided when data base crash occurs)

43(Q) What is DBMS? (A) DBMS is software that provides the user process of defining, creating, altering the data for various applications.

44(Q) When we give SELECT * FROM EMP; How does oracle respond: (A) When we ask the query SELECT * FROM EMP; the database server check all the data in the EMP file and it displays the data of the EMP file on the screen.

45(Q) What is the difference between VARCHAR and VARCHAR2? (A) Varchar means fixed length character data (size) i.e., min size-1 and max-2000 Varchar2 means variable (Dynamic) length character data i.e., min-1 to max-4000

46(Q) Which command displays the SQL command in the SQL buffer, and then executes it? (A) LIST or L command is used to get the recent one from SQL Buffer.

47(Q) What is Difference between DBMS and File System?

(A) DBMS and File system are two ways of saving data, including managing data.

File System:It is a collection of raw data files stored in hard drive.

DBMS:It is a bundle of application that is dedicated for managing data stored in databases

In File System, files are used to store data while, collections of databases are utilized for the storage of data in DBMS. In file system storing altering data done manually. DBMS provide automated methods to complete managing data.

48(Q) What is difference between DELETE and TRUNCATE?

(A) Both the commands are used to delete or remove the data from table, where as TRUNCATE is a DDL command and it can`t be rolled back. DELETE is a DML command and it can be roll back.

TRUNCATE deletes the data permanently while using DELETE we can again get back the data.(ROLL back).

49(Q) What is advantage of using trigger?

(A) Trigger used when we need to validate a DML statement that modifies a table. Also to update automatically when DML command is executed. Triggers can be used to enforce constraints.

50(Q) What is use of WITH GRANT OPTION and GRANT command?

(A) These commands give privilege to grant his/her privileges to other users.

51(Q) What is difference between SQL and SQL SERVER?

(A) SQL (sequel) is a language that provides an interface to RDBMS, developed by IBM. SQL SERVER is a RDBMS just like Oracle, DB2.

52(Q) Write a query to find second maximum value from a table?

(A) select max (sal) from tname where sal= (select max (sal) from tname where sal< (select max (sal) from tname);

53(Q) Write a query that returns only duplicate rows with number of times they are repeated, if table has duplicate values?

(A) SELECT COL1 FROM TAB1 WHERE COL1 IN (SELECT MAX (COL1) FROM TAB1 GROUP BY COL1 HAVING COUNT (COL1) > 1)

54. What is Oracle table? A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns. 55. What are Clusters? Clusters are groups of one or more tables physically stores together to share common columns and are often used together. 56. What is an Index? An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. 57. What are the advantages of views? Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table. Hide data complexity. Simplify commands for the user. Present the data in a different perspective from that of the base table Store complex queries. 58. What are the various types of queries? The types of queries are : Normal Queries Sub Queries co-related queries Nested queries Compound queries

59. What is the difference between clustered and a non-clustered index? A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

60. What is a Tablespace? A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.

61. Why use materialized view instead of a table? Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

62. What does ROLLBACK do? ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

63. Compare and contrast TRUNCATE and DELETE for a table? Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete

64. what is null value? Null Value is neither zero nor it is a blank space. It is some unknown value which occupies 4 bytes of space of memory in SQL.

65. Define transaction? A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.

66. what is the difference between sql&oracle? SQL is Stuctured Query Language.Oracle is a Database.SQL is used to write queries against Oracle DB.

67. What are different Oracle database objects? ■TABLES ■VIEWS ■INDEXES ■SYNONYMS ■SEQUENCES ■TABLESPACES 68. What is hash cluster? A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

69. What is a User_exit? Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.

70. What is schema? A schema is collection of database objects of a user.

71. What are Roles? Roles are named groups of related privileges that are granted to users or other roles.

72. What are the dictionary tables used to monitor a database spaces ? ■DBA_FREE_SPACE ■DBA_SEGMENTS ■DBA_DATA_FILES 73. What is a SNAPSHOT? Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

74. What is a database instance? -A database instance is a set of memory structure and background processes that access a set of database files. The processes can be   shared by all of the users.

75. What are parameters? Parameters provide a simple mechanism for defining and setting the valuesof inputs that are required by a form at startup.Form parameters are variables of type char,number,date that you define at design time.

76. What are the different file extensions that are created by oracle reports? Rep file and Rdf file.

77. What are clusters? Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

78. What is difference between SUBSTR and INSTR? INSTRfunction search string for sub-string and returns an integer indicating the position of the character in string that is the first character of this occurrence.SUBSTR function return a portion of string, beginning at character position, substring_length characters long.SUBSTR calculates lengths using characters as defined by the input character set

79. Define a view? A view is a virtual table which is based on the one or more physical tables and views.

80. What is the difference between a view and a synonym? Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

81. What is the usage of SAVEPOINTS? SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

82. What are ORACLE PRECOMPILERS? A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc.The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way.

83. When do you use WHERE clause and when do you use HAVING clause? The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions.Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE.

84. What are the differences between stored procedures and triggers? A stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object.

85. What must be installed with ODBC on the client in order for it to work with Oracle? SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

86. What are the different file extensions that are created by oracle reports? Rep file and Rdf file.

87. what is trigger? Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

88. Explain the difference between a data block, an extent and a segment? A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

89. What is bind reference and how can it be created? Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a before a column or a parameter name.

90. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY? Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

91. What are various types of joins? ■Equi joins ■Cartesian joins ■Self join ■Outer join 92. What is the maximum number of triggers, can apply to a single table? 12 triggers

93. What command would you use to create a backup control file? Alter database backup control file to trace.

94. What is an Oracle index? An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

95. What are the different index configurations a table can have? ■A table can have one of the following index configurations ■No indexes ■A clustered index ■A clustered index and many nonclustered indexes ■A nonclustered index ■Many nonclustered indexes. 96. What is difference between UNIQUE constraint and PRIMARY KEY constraint? A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls. A table can have only one primary keys.

97. What is BCP? When does it used? BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

98. How to know which index a table is using? SELECT table_name,index_name FROM user_constraints.

99. What is SYSTEM tablespace and when is it created? Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

100. Which date function returns number value? months_between

101. what is integrity constrains? Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

102. What is the difference between Explicit and Implicit Cursors? An Implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code An Explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others DBA_DATA_FILES.

103. what are primary keys? Primary key are used to uniquely identify each row of the table. A table can have only one primary key.

104. What are the components of physical database structure of Oracle database? Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

105. What is DECODE function used for? DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

106. What is the default return value of a function? The default return value from a function is int. In other words, unless explicitly specified the default return value by compiler would be integer value from function.

107. What is the difference between oracle,sql and sql server? ■Oracle is based on RDBMS. ■SQL is Structured Query Language. ■SQL Server is another tool for RDBMS provided by MicroSoft. 108. How you will avoid your query from using indexes? By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

109. What is a cluster key? The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

110. What is SGA? The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

111. What is a data segment? Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

112. What is ROWID? ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, row number are the components of ROWID.

113. What is the usage of SAVEPOINTS? SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

114. To see current user name Sql> show user;

115. Change SQL prompt name SQL> set sqlprompt “Manimara > “ Manimara > Manimara >

116. Switch to DOS prompt SQL> host

117. How do I eliminate the duplicate rows ? SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv); Example. Table Emp Empno Ename 101 Scott 102 Jiyo 103 Millor 104 Jiyo 105 Smith delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename); The output like, Empno Ename 101 Scott 102 Millor 103 Jiyo 104 Smith

118. How do I display row number with records? To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp; Output: 1 Scott 2 Millor 3 Jiyo 4 Smith 6. Display the records between two range select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start); Enter value for upto: 10 Enter value for Start: 7 ROWNUM EMPNO ENAME - - -- 1 7782 CLARK 2 7788 SCOTT 3 7839 KING 4 7844 TURNER

119. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query? SQL> select nvl(to_char(comm.),'NA') from emp; Output : NVL(TO_CHAR(COMM),'NA') --- NA 300 500 NA 1400 NA NA

120. Oracle cursor : Implicit & Explicit cursors Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

121. Explicit Cursor attributes There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

122. Implicit Cursor attributes Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
 * 2. All are Boolean attributes.

123. Find out nth highest salary from emp table SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); Enter value for n: 2 SAL - 3700

124. To view installed Oracle version information SQL> select banner from v$version;

125. Display the number value in Words SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp; the output like, SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP')) - - 800 eight hundred 1600 one thousand six hundred 1250 one thousand two hundred fifty If you want to add some text like, Rs. Three Thousand only. SQL> select sal "Salary ", (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.')) "Sal in Words" from emp / Salary Sal in Words --- -- 800 Rs. Eight Hundred only. 1600 Rs. One Thousand Six Hundred only. 1250 Rs. One Thousand Two Hundred Fifty only.

126. Display Odd/ Even number of records Odd number of records: select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); 1 3 5 Even number of records: select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp) 2 4 6

127. Which date function returns number value? months_between

128. Any three PL/SQL Exceptions? Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others 17. What are PL/SQL Cursor Exceptions? Cursor_Already_Open, Invalid_Cursor

129. Other way to replace query result null value with a text SQL> Set NULL ‘N/A’ to reset SQL> Set NULL ‘’

130. What are the more common pseudo-columns? SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM

131. What is the output of SIGN function? 1 for positive value, 0 for Zero, -1 for Negative value.

132. What is the maximum number of triggers, can apply to a single table? 12 triggers.

133. What is PL/SQL? PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

134. What is the basic structure of PL/SQL? PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

135. What are the components of a PL/SQL block? A set of related declarations and procedural statements is called block.

136. What are the components of a PL/SQL Block? Declarative part, Executable part and Execption part.

137. What are the datatypes a available in PL/SQL? Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

138.    What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes? % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are: I. need not know about variable's data type ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

139.    What is difference between % ROWTYPE and TYPE RECORD ? % ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type ); e_rec emp% ROWTYPE Cursor c1 is select empno,deptno from emp; e_rec c1 %ROWTYPE.

140.    What is PL/SQL table? Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

141.    What is a cursor? Why Cursor is required? Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

142.    Explain the two types of Cursors? There are two types of cursors, Implict Cursor and Explicit Cursor. PL/SQL uses Implict Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

143.   What are the PL/SQL Statements used in cursor processing? DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

144.    What are the cursor attributes used in PL/SQL? %ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows featched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are featched. % NOT FOUND - to check whether cursor has featched any row. True if no rows are featched. These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.

145.    What is a cursor for loop? Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP;

146.    What will happen after commit statement ? Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;- commit; end loop; end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

147.    Explain the usage of WHERE CURRENT OF clause in cursors ? WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.

148.    What is a database trigger ? Name some usages of database trigger ? Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

149.    How many types of database triggers can be specified on a table? What are they? Insert            Update             Delete

Before Row                o.k.                  o.k.                o.k.

After Row                  o.k.                  o.k.                o.k.

Before Statement       o.k.                  o.k.                o.k.

After Statement          o.k.                  o.k.                o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the retruned boolean value.

150.    Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why? It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

151.    What are two virtual tables available during database trigger execution? The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.

152.    What happens if a procedure that updates a column of table X is called in a database trigger of the same table? Mutation of table occurs.

153.    Write the order of precedence for validation of a column in a table ? I. done using Database triggers. ii. done using Integarity Constraints.

154.    What is an Exception? What are types of Exception? Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.

CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS.

155.    What is Pragma EXECPTION_INIT? Explain the usage? The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

156.    What is Raise_application_error? Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

157.    What are the return values of functions SQLCODE and SQLERRM? SQLCODE returns the latest code of the error that has occured. SQLERRM returns the relevant error message of the SQLCODE.

158.    Where the Pre_defined_exceptions are stored? In the standard package. Procedures, Functions & Packages;

159.    What is a stored procedure? A stored procedure is a sequence of statements that perform specific function.

160.    What is difference between a PROCEDURE & FUNCTION? A FUNCTION is alway returns a value using the return statement. A PROCEDURE may return one or more values through parameters or may not return at all.

161.    What are advantages of Stored Procedures? Extensibility,Modularity, Reusability, Maintainability and one time compilation.

162.    What are the modes of parameters that can be passed to a procedure? IN,OUT,IN-OUT parameters.

163.    What are the two parts of a procedure? Procedure Specification and Procedure Body.

164.    Give the structure of the procedure? PROCEDURE name (parameter list.....) is local variable declarations

BEGIN Executable statements. Exception. exception handlers

end;

165.    Give the structure of the function? FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End;

166.    Explain how procedures and functions are called in a PL/SQL block ? Function is called as part of an expression. sal := calculate_sal ('a822'); procedure is called as a PL/SQL statement calculate_bonus ('A822');

167.    What is Overloading of procedures? The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line

168.    What is a package? What are the advantages of packages? Package is a database object that groups logically related procedures. The advantages of packages are Modularity, Easier Applicaton Design, and Information. Hiding,. Reusability and Better Performance.

169.    What are two parts of package? The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

170.    What is difference between a Cursor declared in a procedure and Cursor declared in a package specification? A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

171.    How packaged procedures and functions are called from the following ? a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE b. BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCTION NAME (arguments); END; END EXEC; c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

172.    Name the tables where characteristics of Package, procedure and functions are stored? User_objects, User_Source and User_error.