Showing posts with label interview. Show all posts
Showing posts with label interview. Show all posts

01 July, 2015

PLSQL Interview Questions | Third Set

1. What is PL/SQL ?PL/SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.


2. Differentiate between % ROWTYPE and TYPE RECORD.% ROWTYPE is used when a query returns an entire row of a table or view.TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)e_rec smp ROWTYPECursor c1 is select smpno,dept from smp;e_rec c1 %ROWTYPE


3. Explain uses of cursor.Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.


4. Show code of a cursor for loop.Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.Eg. FOR smp_rec IN C1 LOOPtotalsal=totalsal+smp_recsal;ENDLOOP;


5. Explain the uses of database trigger.A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :1)Audit data modifications.2)Log events transparently.3)Enforce complex business rules.4)Maintain replica tables5)Derive column values6)Implement Complex security authorizations


6. What are the two types of exceptions.Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.


7. Show some predefined exceptions.DUP_VAL_ON_INDEXZERO_DIVIDENO_DATA_FOUNDTOO_MANY_ROWSCURSOR_ALREADY_OPENINVALID_NUMBERINVALID_CURSORPROGRAM_ERRORTIMEOUT _ON_RESOURCESTORAGE_ERRORLOGON_DENIEDVALUE_ERRORetc.


8. Explain Raise_application_error.It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.


9.Show how functions and procedures are called in a PL/SQL block.Function is called as a part of an expression.total:=calculate_sal(‘b644’)Procedure is called as a statement in PL/SQL.calculate_bonus(‘b644’);


10. Explain two virtual tables available at the time of database trigger execution.Table columns are referred as THEN.column_name and NOW.column_name.For INSERT related triggers, NOW.column_name values are available only.For DELETE related triggers, THEN.column_name values are available only.For UPDATE related triggers, both Table columns are available.


11. What are the rules to be applied to NULLs whilst doing comparisons?1) NULL is never TRUE or FALSE2) NULL cannot be equal or unequal to other values3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)


12. How is a process of PL/SQL compiled?Compilation process includes syntax check, bind and p-code generation processes.Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.


13. Differentiate between Syntax and runtime errors.A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.


14. Explain Commit, Rollback and Savepoint.For a COMMIT statement, the following is true:

Other users can see the data changes made by the transaction.

The locks acquired by the transaction are released.

The work done by the transaction becomes permanent.

A ROLLBACK statement gets issued when the transaction ends, and the following is true.

The work done in a transition is undone as if it was never issued.

All locks acquired by transaction are released.

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.


15. Define Implicit and Explicit Cursors.A cursor is implicit by default. The user cannot control or process the information in this cursor.If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.


16. Explain mutating table error.It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.


17. When is a declare statement required?DECLARE statement is used by PL/SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.


18. How many triggers can be applied to a table?A maximum of 12 triggers can be applied to one table.


19. What is the importance of SQLCODE and SQLERRM?SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.


20. If a cursor is open, how can we find in a PL/SQL Block?the %ISOPEN cursor status variable can be used.


21. Show the two PL/SQL cursor exceptions.Cursor_Already_OpenInvaid_cursor


22. What operators deal with NULL?NVL converts NULL to another specified value.var:=NVL(var2,’Hi’);IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.


23. Does SQL*Plus also have a PL/SQL Engine?No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.


24. What packages are available to PL/SQL developers?DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.


25. Explain 3 basic parts of a trigger.

A triggering statement or event.

A restriction

An action


26. What are character functions?INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.


27. Explain TTITLE and BTITLE.TTITLE and BTITLE commands that control report headers and footers.


28. Show the cursor attributes of PL/SQL.%ISOPEN : Checks if the cursor is open or not%ROWCOUNT : The number of rows that are updated, deleted or fetched.%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.


29. What is an Intersect?Intersect is the product of two tables and it lists only matching rows.


30. What are sequences?Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.


31. How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.


32. What are the uses of SYSDATE and USER keywords?SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.


33. How does ROWID help in running a query faster?ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.


34. What are database links used for? Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.


35. What does fetching a cursor do?Fetching a cursor reads Result Set row by row.


36. What does closing a cursor do?Closing a cursor clears the private SQL area as well as de-allocates memory


37. Explain the uses of Control File.It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.


38. Explain ConsistencyConsistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.


39. Differ between Anonymous blocks and sub-programs.Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.


40. Differ between DECODE and CASE.DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);This statement returns an error.CASE is directly used in PL/SQL, but DECODE is used in PL/SQL through SQL only.


41. Explain autonomous transaction.An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.There are several situations to use autonomous transactions like event logging and auditing.


42. Differentiate between SGA and PGA.SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.


43. What is the location of Pre_defined_functions.They are stored in the standard package called “Functions, Procedures and Packages”


44. Explain polymorphism in PL/SQL.Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.


45. What are the uses of MERGE?MERGE is used to combine multiple DML statements into one.Syntax : merge into tablenameusing(query)on(join condition)when not matched then[insert/update/delete] commandwhen matched then[insert/update/delete] command


46. Can 2 queries be executed simultaneously in a Distributed Database System?Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.


47. Explain Raise_application_error.It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.


48. What is out parameter used for eventhough return statement can also be used in pl/sql?Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.


49. How would you convert date into Julian date format?We can use the J format string :SQL > select to_char(to_date(‘29-Mar-2013’,’dd-mon-yyyy’),’J’) as julian from dual;JULIAN


50. Explain SPOOLSpool command can print the output of sql statements in a file.spool/tmp/sql_outtxtselect smp_name, smp_id from smp where dept=’accounts’;spool off;


51. Mention what PL/SQL package consists of?A PL/SQL package consists of

PL/SQL table and record TYPE statements

Procedures and Functions

Cursors

Variables ( tables, scalars, records, etc.) and constants

Exception names and pragmas for relating an error number with an exception

Cursors


52. Mention what are the benefits of PL/SQL packages?It provides several benefits like

Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public

Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves

Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module

Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used

Guaranteeing transaction integrity: It provides a level of transaction integrity

Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.


53. Mention what are different methods to trace the PL/SQL code?Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes

DBMS_APPLICATION_INFO

DBMS_TRACE

DBMS_SESSION and DBMS_MONITOR

trcsess and tkproof utilities


54. Mention what does the hierarchical profiler does?The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes

Distinct reporting for SQL and PL/SQL time consumption

Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call

Multiple interactive analytics reports in HTML format by using the command line utility

More effective than conventional profiler and other tracing utilities


55. Mention what does PLV msg allows you to do?The PLV msg enables you to

Assign individual text message to specified row in the PL/SQL table

It retrieves the message text by number

It substitutes automatically your own messages for standard Oracle error messages with restrict toggle

Batch load message numbers and text from a database table directly PLV msg PL/SQL table


56. Mention what is the PLV (PL/Vision) package offers?

Null substitution value

Set of assertion routines

Miscellaneous utilities

Set of constants used throughout PL vision

Pre-defined datatypes

Mention what is the use of PLVprs and PLVprsps?

PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality

PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.


57. Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?With a single program call – “fcopy procedure”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program “file2pstab”.


58. Explain how exception handling is done in advance PL/SQL?For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.

Continue processing

Record and then continue

Halt processing

Record and then halt processing

For those exceptions that re-occurs you can use the RAISE statement.


59. Mention what problem one might face while writing log information to a data-base table in PL/SQL?While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.


60. Mention what is the function that is used to transfer a PL/SQL table log to a database table?To transfer a PL/SQL table log a database log table function “PROCEDURE ps2db” is used.


61. When you have to use a default “rollback to” savepoint of PLVlog?The default “rollback to” savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.


62. Why PLVtab is considered as the easiest way to access the PL/SQL table?The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.


63. Mention what does PLVtab enables you to do when you show the contents of PL/SQL tables?PLVtab enables you to do following things when you show the contents of PL/SQL tables

Display or suppress a header for the table

Display or suppress the row numbers for the table values

Show a prefix before each row of the table


64. Explain how can you save or place your msg in a table?To save msg in a table, you can do it in two ways

Load individual messages with calls to the add_text procedure

Load sets of messages from a database table with the load_from_dbms procedure


65. Mention what is the use of function “module procedure” in PL/SQL?The “module procedure” enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules

module_in

cor_in

Last_module_in


66. Mention what PLVcmt and PLVrb does in PL/SQL?PL/Vision offers two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.

PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing

PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL

PLSQL Interview Questions | Second Set

1. Is it possible to Auto-Refresh the Materialize View?

Yes it is possible. During declaration, you can specify the auto refresh interval of the mview.

Sol:The DBMS_MVIEW package contains three APIs for performing refresh operations: 
•DBMS_MVIEW.REFRESH 
Refresh one or more materialized views. 
•DBMS_MVIEW.REFRESH_ALL_MVIEWS 
Refresh all materialized views. 
•DBMS_MVIEW.REFRESH_DEPENDENT 
Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views. 
 CREATE materialized VIEW yarpno_mv 2 refresh fast 3 NEXT trunc(sysdate+1) 4 AS SELECT * FROM yarpno; 

2. Explain diff. types of Indexes? Which one is fasted?(IBM) 

3. How to send mails multiple customers in a single select query?(Bahwan Cyber Tech) sol : UTL_SMTP , UTL_MAIL 

4. Explain about Nonralization.(Polaris) 

6. What is passed by value & passed by reference? explain with example.(Bahwan Cyber Tech) 

7. What is Direct Path Loader?(IBM) 

8. Diff. between Direct Path Loader vs Conventional Path Loader?(IBM) 

9. Sql loader is for upload or download or both?(LNT) 

10. How to load other than csv file? (iGATE) 

11. How to skip a column in sql loader?(IBM) 

12. DML operations inside the exception handling path ( when others then) is possible? Explain.(Polaris) 

13. What i & g mean by in Oracle versions? (DNB-Dun & Broadstreet) 

14. what are diff. in 10g & 11g? (RAMCO) 

15. what is diff. between cursor & ref cursor? (LNT) 

16. what is diff. between nested table & varray?(iGATE) Greens Technology’s Contact :-9840496320 

17. what is diff. between ref cursor & bulk collect?(Bahwan Cyber Tech) 

18. What is global cursor? (CTS) 

19. what will happen when we use COMMIT in Trigger? (Polaris) 

20. How many number of triggers possible? (LNT) 

21. HOW TO SEE INVALID INDEXES?HOW TO REBUILD INVALID INDEXES USING PL/SQL BLOCK?(Polaris) 

22. TELL ME ABOUT BULK_COLLECT & BULK_BIND.(Bahwan Cyber Tech) 

23. WHAT IS DIFF B/W NOT EXITS & MINUS?(IBM) 

24. What is the physical and logical structure of oracle?(Polaris) 

25. how can u open multiple files in Unix is it possible? 

26. Created procedure has 5 parameters if the same procedure is called with 3 parameters will it work? What is the error will it give? (hexaware) 

27. What is pragma EXCEPTIO_INIT_?(Polaris) 

28. HOW TO COUNT NO OF RECORDS IN TABLE WITHOUT COUNT?(Bahwan Cyber Tech) 

29. HOW TO RETRIVE TOP 3 SALARIES FROM EACH DEPARTMENTS?(IBM) 

30. WHAT IS CONTEXT SWITHCING?(TCS) 

31. WHAT IS BULKCOLLECT? AND ANY RESTRICTIONS IN BULKCOLLECT?(iGATE) 

32. WHAT IS FUNCTIONAL BASED INDEX? WRITE SYNTAX? 

33. CAN YOU ALTER PROCEDURE WITH IN PACKAGE?(iGATE) 

34. IS IT POSSIBLE TO OPEN CURSOR WHICH IS IN PACKAGE IN ANOTHER PROCRDURE?(Polaris) 

35. DIFFERENCE BETWEEN CASE AND DECODE?(iGATE) 

36. CAN YOU USE SYSDATE IN CHECK CONSTRAINTS? IF NO, WHY?(hexaware) 

37. TELL ME SOME IMPORTANT ORACLE 11G FEATURES?(Bahwan Cyber Tech) 

38. IF TABLE DROPPED, THEN WHAT HAPPEN VIEW?(hexaware) 

39. WHICH FUNCTIONS ARE NOT SUPPORTED TO INDEX?(hexaware) 

40. WHAT ARE THE DATATYPES AVILABLE IN PL/SQL, NOT IN SQL? 

41. DIFF B/W EXPLICT CURSOR & FOR LOOP CURSOR?(Polaris) 

42. WHAT IS RAISE_APPLICATION_ERROR?EXPLAIN(IBM) 

43. HOW CAN YOU CALL PROCEDURE IN SELECT STATEMENT?(hexaware) 

44. HOW TO DROP PACKAGE BODY ONLY? 

45. What is the disadvantage of package?(Bahwan Cyber Tech) 

46. CAN USE PRAGMA_AUTONAMOUS_TRANCTIONS IN PACKAGES? 

47. HOW TO DEBUGG YOUR CODE? 

48. How will you move the cursor with out using arrow keys in vi editor in UNIX? 

49. What is grep in UNIX? (Polaris) 

50. How you get latest processed record in unix?(Polaris) 

51. Write a script for check a file if it is available or no If available rename it(unix). 

52. What is inode and explain it (unix)(Polaris) 

53. Write a correlated sub query and explain how it works.(hexaware) 

54. What are different type of triggers? 

55. Write a pl/sql block for trigger at the time of insertion for inserting old and new values in auditing table?(Bahwan Cyber Tech) 

56. If we have both user exception and system exception, which will be handled first? 

57. How to access table from another database? 

58. If I gave a select statement, it contains null data; I gave NO_DATA_FOUND AND OTHERS in exception block? What exception will handles and that procedure compiled or not? It will not compiled why because no choice appear with choice others in exception handler. 

59. How u know whether a table is locked or not?(unix)(Polaris) 

60. How you know what are the preveliges you have? 

61.Is it possible for "two objects have the same name"? 

62. Can you see dba tables? 

63. What is cluster? 

64. What is dual ? can we truncate it ? 

65. What is the difference between truncate and delete? 

66. What is the difference between drop and delete?w 

67. what is explain plan? explain?(IBM) 

68. what is dbms_profiler? what are the uses.explin? 

69. explain the steps you worked with the performance tunning. 

70. what are the stepls for table partioning? explain? 

71. is it composite primary key possilbe? if yes how give example. 

72. what is the diff. between plsql table & record? 

73. Is it possilbe to create index automatically?if yes explain what and how? 

74. how you create views without base table? 

75. what is .bad file? 

76. what is the result for the query - SELECT 'A' FROM DUAL INTERSECT SELECT NULL FROM DUAL; 

77. HOW you pass parameters into package? 

78. To where you use execute immediate? what is the use of it? 

79. is it possible to use Continue statement is in PL/SQL loops? 

80. How many primary key you can use in a table? 

81. What are Difference between WHERE & HAVING? 

83. What is the difference between the cursor declared in the procedure and cursor declared in the package?

PL/SQL Interview Questions | First Set

PL/SQL is an advance version of SQL. There are given top list of PL/SQL interview questions with answer.
1) What is PL/SQL?
PL/SQL stands for procedural language extension to SQL. It supports procedural features of programming language and SQL both. It was developed by Oracle Corporation in early of 90's to enhance the capabilities of SQL.
2) What is PL/SQL table? Why it is used?
Objects of type tables are called PL/SQL tables that are modeled as database table. They are a type of COLLECTION. They were initially introduced in pl/sql. Nested tables are Varrays were introduced only after Oracle 8i.
We can also say that PL/SQL tables are a way to provide arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.
3) What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
1.    Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
2.    Composite datatypes Example are RECORD, TABLE etc.
4) What is the basic structure of PL/SQL?
PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.
1.    The Declaration Section (optional)
2.    The Execution Section (mandatory)
3.    The Exception handling Section (optional)
5) What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
6) What is exception? What are the types of exceptions?
Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.
7) How exception is different from error?
Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.
8) What is the main reason behind using an index?
Faster access of data blocks in the table.
9) What are PL/SQL exceptions? Tell me any three.
1.    Too_many_rows
2.    No_Data_Found
3.    Value_error
4.    Zero_error etc.
10) What is the maximum number of triggers, you can apply on a single table?
12 triggers.
11) How many types of triggers exist in PL/SQL?
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
·         BEFORE ALL ROW INSERT
·         AFTER ALL ROW INSERT
·         BEFORE INSERT
·         AFTER INSERT etc.
12) What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
13) How to execute a stored procedure?
There are two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.

1.    EXECUTE or [EXEC] procedure_name;

Simply use the procedure name

1.    procedure_name;

14) What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability and one time compilation.
15) What are the cursor attributes used in PL/SQL?
%ISOPEN: it checks whether the cursor is open or not.
%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether cursor has fetched any row. If yes - TRUE.
%NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.
16) What is consistency?
Consistency simply means that each user sees the consistent view of the data.
Consider an example: there are two users A and B. A transfers money to B's account. Here the changes are updated in A's account (debit) but until it will be updated to B's account (credit), till then other users can't see the debit of A's account. After the debit of A and credit of B, one can see the updates. That?s consistency.
17) What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed.
A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
18) How many types of cursors are available in PL/SQL?
There are two types of cursors in PL/SQL.
1.    Implicit cursor, and

2.    explicit cursor

27 June, 2015

PLSQL Interview Questions - Set 1

Set of PL/SQL Interview Questions


1. Difference between 
  • DROP and TRUNC[ATE] - truncate is faster as it is autocommit and does not fire any delete triggers. But delete does this, so it is slow.
  • NVL and NVL2 - nvl2 takes 3 arguments
  • UNION and UNIONALL - union removes the duplicates, but unionall shows duplicate records too. Performance of unionall is better as it does not have the overhead to remove duplicates.
2. PRAGMA
Compiler directive or call it a hint. Can be used with database triggers, top level anonymous block, local standalone procedures and functions.
Topic Explained

3. INSTEAD OF trigger
Create updatable views using instead of triggers.

 4. Dynamic SQL
This functionality allows us to build and execute dynamic sql at runtime.
That means when the code is compiled, the compiler does not know what the dynamic statement would be.
as compiler checks beforehand about the statis sql for valid privileges and db references, so its fast compared to dynamic.

Author's Message
Subscribe to: Posts (Atom)