If you are looking for job in DBA or related technology , you have to know PL/SQL and SQL to crack the interview. Here is A list of top frequently asked PL/SQL and SQL interview questions and answers are given below.
Ans:
Difference between SQL and PL/SQL can be categorized as follows
SQL | PL/SQL |
SQL is a natural language which is very useful for interactive processing. | PL/SQL is a procedural extension of Oracle – SQL. |
No procedural capabilities like condition testing, looping is offered by SQL. | PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc. |
All SQL statements are executed by the database server one at a time, thus it is a time-consuming process. | PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably. |
No error handling procedures are there in SQL. | PL/SQL supports customized error handling. |
Ans:
Characteristics of PL/SQL are as follows
PL/SQL allows access and sharing of the same sub programs by multiple applications.
PL/SQL is known for portability of code as code can be executed on any operating system provided Oracle is loaded on it.
With PL/SQL user can write their own customized error handling routines.
Improved transaction performance with integration to Oracle data dictionary.
Ans:
Data types define the ways to identify the type of data and their associated operations. There are 4 types of predefined data types explained as follows
Scalar Data Types: A scalar data type is an atomic data type that does not have any internal components.
For example
CHAR (fixed length character value between 1 and 32,767 characters)
VARCHAR2 (variable length character value between 1 and 32,767 characters)
NUMBER ( fixed-decimal, floating-decimal or integer values)
BOOLEAN ( logical data type for TRUE FALSE or NULL values)
DATE (stores date and time information)
LONG (character data of variable length)
Composite Data Types: A composite data type is made up of other data types and internal components that can be easily used and manipulated. For example RECORD, TABLE, and VARRAY.
Reference Data Types: A reference data types holds values, called pointers that designate to other program items or data items. For example REF CURSOR.
Large Object Data Types: A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.
For example
BFILE (Binary file)
BLOB (Binary large object)
CLOB ( Character large object)
NCLOB( NCHAR type large object)
Ans:
PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.
For example, the variable m_empno has the same data type and size as the column empno in table emp.
m_empno emp.empno%TYPE;
%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.
For example: dept_rec dept%ROWTYPE;
This declares a record that can store an entire row for DEPT table.
Ans:
PL/SQL packages are schema objects that groups functions, stored procedures, cursors and variables at one place. Packages have 2 mandatory parts
Package Specifications
Package body
Ans:
PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.
PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a PL/SQL program.
Ans:
There are two types of cursors. They are explained as follows
Declare the cursor
Syntax: CURSOR is
SELECT statement;
Where is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.
Open the cursor
Syntax: OPEN ;
Where, is the name of the previously defined cursor.
Fetch rows from the cursor
Syntax: FETCH INTO ;
Where refers to the name of the previously defined cursor from which rows are being fetched.
represents the list of variables that will receive the data being fetched.
Closing the cursor
Syntax: CLOSE ;
Where is the name of the cursor being closed.
For following statements, PL/SQL employs implicit cursors
INSERT
UPDATE
DELETE
SELECT ( queries that return exactly one row)
Ans:
The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when database related event is performed. Triggers are mainly required for the following purposes
To maintain complex integrity constraints
Auditing table information by recording the changes
Signaling other program actions when changes are made to table
Enforcing complex business rules
Preventing invalid transactions
Ans:
A stored procedure is executed explicitly by issuing procedure call statement from another block via a procedure call with arguments.
The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.
Ans:
Triggers are different from constraints in the following ways
Triggers | Constraints |
Only affect those rows added after the trigger is enabled. | Affect all rows of the table including that already exist when the constraint is enabled. |
Triggers are used to implement complex business rules which cannot be implemented using integrity constraints. | Constraints maintain the integrity of the database. |
Ans:
In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package.
Broadly, PL/SQL blocks are two types
DECLARE
num NUMBER(2);
sq NUMBER(3);
BEGIN
num:= &Number1;
sq := num*num;
DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);
END;
Example:
FUNCTION sqr (num IN NUMBER)
RETURN NUMBER is sq NUMBER(2);
BEGIN
sq:= num*num;
RETURN sq;
END;
Ans:
Syntax errors are the one which can be easily identified by a PL/SQL compiler. These errors can be the spelling mistake, etc.
Runtime errors are those errors in PL/SQL block for which exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.
Ans:
COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL.
COMMIT statement: When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens
Affected rows locks are released
Transaction marked as complete
Transaction detail is stored in the data dictionary.
Syntax: COMMIT;
ROLLBACK statement: When we want to undo or erase all the changes that have occurred in the current transaction so far, we require rolling back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.
Syntax to rollback a transaction fully
ROLLBACK;
SAVEPOINT statement: The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released.
Syntax:
SAVEPOINT ;
Ans:
A table which is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table.
A table that might need to be read from for a referential integrity constraint is known as constraining table.
Ans:
The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters.
For example: raise_sal(emp_num, merit+ amount);
Here in the above example, emp_num and amount are the two actual parameters.
The variables that are declared in the procedure header and are referenced in the procedure body are called as Formal parameters.
For example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
Here in the above example, emp_id acts as a formal parameter.
Ans:
The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks.
On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT.
Ans:
SET SERVER OUTPUT ON
DECLARE
BEGIN
FOR i IN REVERSE 1..99
LOOP
IF Mod(i,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
Ans:
3 modes of the parameter are IN, OUT, IN OUT. These can be explained as follows
IN parameters: IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.
OUT parameters: OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.
IN OUT parameters: IN OUT parameters passes initial values to a procedure and returns updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.
Ans:
An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of DML statement.
Ans:
The outcome of the statement is saved in 4 cursor attributes. These are
Ans:
Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. There are two ways to comment in PL/SQL
Ans:
When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in EXCEPTION section.
There are 3 types of EXCEPTION:
Ans:
Some of the predefined exceptions are
Ans:
The exceptions related to PL/SQL cursors are
Ans:
The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures.
The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.
Ans:
The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.
Ans:
DECLARE
n NUMBER;
avg NUMBER :=0 ;
sum NUMBER :=0 ;
count NUMBER :=0 ;
BEGIN
n := &enter_a_number;
WHILE(n<>0)
LOOP
count := count+1;
sum := sum+n;
n := &enter_a_number;
END LOOP;
avg := sum/count;
DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
END;
Ans:
Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.
Ans:
Expressions can be as mentioned below
Numeric or Arithmetic expressions : e.g. 20* 10+ 15
Boolean expressions: e.g. ‘spot’ LIKE ‘sp%t’
String expressions: e.g. LENGTH (‘NEW YORK’|| ‘NY’)
Date expressions: e.g. SYSDATE>TO_DATE(’15-NOV-16’, “dd-mm-yy”)
Ans:
A PL/SQL records can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.
There are three types of records supported in PL/SQL
Ans:
PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.
Ans:
% 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 %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
Ans:
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.
Ans:
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 LOOP
totalsal=totalsal+smp_recsal;
ENDLOOP;
Ans:
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
Ans:
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
Ans:
DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
etc.
Ans:
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
Ans:
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’);
Ans:
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.
Ans:
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
A maximum of 12 triggers can be applied to one table.
Ans:
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
Ans:
the %ISOPEN cursor status variable can be used.
Ans:
Cursor_Already_Open
Invaid_cursor
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
A triggering statement or event.
A restriction
An action
Ans:
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.
Ans:
TTITLE and BTITLE commands that control report headers and footers.
Ans:
Ans:
Intersect is the product of two tables and it lists only matching rows.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
Fetching a cursor reads Result Set row by row.
Ans:
Closing a cursor clears the private SQL area as well as de-allocates memory
Ans:
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.
Ans:
Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.
Ans:
Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
They are stored in the standard package called “Functions, Procedures and Packages”
Ans:
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.
Ans:
MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command
Ans:
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.
Ans:
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.
Ans:
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.
Ans:
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
Ans:
Spool command can print the output of sql statements in a file.
spool/tmp/sql_outtxt
select smp_name, smp_id from smp where dept=’accounts’;
spool off;
Ans:
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
Ans:
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.
Ans:
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
Ans:
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
Ans:
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
Ans:
Null substitution value
Set of assertion routines
Miscellaneous utilities
Set of constants used throughout PL vision
Pre-defined datatypes
Ans:
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.
Ans:
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”.
Ans:
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.
Ans:
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.
Ans:
To transfer a PL/SQL table log a database log table function “PROCEDURE ps2db” is used.
Ans:
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.
Ans:
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.
Ans:
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
Ans:
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
Ans:
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
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
Ans:
Criteria | SQL | PL/SQL |
What it is | Single query or command execution | Full programming language |
What it comprises | Data source for reports, web pages | Application language to build, format and display report, web pages |
Characteristic | Declarative in nature | Procedural in nature |
Used for | Manipulating data | Creating applications |
Ans:
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Ans:
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
Ans:
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
Audit data modifications.
Log events transparently.
Enforce complex business rules.
Maintain replica tables
Derive column values
Implement Complex security authorizations
Any of the constant, variable or parameter has a data type depending on which the storage constraints, format and range of values and operations are determined.
Ans:
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.
Ans:
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
Ans:
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.
Ans:
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
Ans:
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.
Ans:
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.
Ans:
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.
In this software era doesn't matter you are working on php,java or .net, everywhere there…
Here we have mentioned most frequently asked IT Fresher Interview Questions and Answers specially for…
Here we have mentioned most frequently asked Github Interview Questions and Answers specially for freshers…
In this software era doesn't matter you are working on php,java or .net, everywhere there is demand frontend software (such…
Here we have mentioned most frequently asked IT Fresher Interview Questions and Answers specially for freshers and experienced. 1. What…
Here we have mentioned most frequently asked Github Interview Questions and Answers specially for freshers and experienced. 1. What is…
Here we have mentioned most frequently asked HTML5 Interview Questions and Answers specially for freshers and experienced. 1. What is…
Here we have mentioned most frequently asked Geography Interview Questions and Answers specially for freshers and experienced. 1. What is…
Here we have mentioned most frequently asked JSP Interview Questions and Answers specially for freshers and experienced. 1. Explain JSP…
This website uses cookies.