Last Update: 10/09/2007 10:15 PM SG
Oracle Interview based script collections new
Oracle Question & Answer
Level: Fundamental
SQL
1. what is 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.
2. What is a View ?
A view is a virtual
table. Every view has a Query attached to it. (The Query is a SELECT statement
that identifies the columns and rows of the table(s) the view
uses.)
3. Do View contain Data
?
Views do not contain or store data.
4. Can a View based on another View
?
Yes.
5. What are the advantages of Views
?
i. Provide an additional level of table security, by
restricting access to a predetermined set of rows and columns of a table.
ii.
Hide data complexity.
iii. Simplify commands for the user.
iv. Present the
data in a different perpecetive from that of the base table.
v. Store complex
queries.
6
. Can a view
be updated/inserted/deleted? If Yes under what conditions ?
A View
can be updated/deleted/inserted if it has only one base table if the view is
based on columns from one or more tables then insert, update and delete is not
possible.
7
. If a View
on a single base table is manipulated will the changes be reflected on the base
table ?
If changes are made to the tables which are base tables of
a view will the changes be reference on the view.
8. What is a Sequence
?
A sequence generates a serial list of unique numbers for
numerical columns of a database's tables.
9.
How to see current user name
Sql> show user;
10. Change SQL prompt name
SQL> set sqlprompt ?Manimara > ?
Manimara
>
Manimara >
11. Switch to DOS
prompt
SQL> host
12.
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);
Example:
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
13. 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
14. 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
14. 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
16. 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.
17. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT,
cursor_name%ISOPEN
18. 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.
19. 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
20. To view installed Oracle version
information
SQL> select banner from v$version;
22. 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.
23. 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
24. Which date function returns
number value?
months_between
25. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
26. What are PL/SQL Cursor
Exceptions?
Cursor_Already_Open, Invalid_Cursor
38. Other way to replace query result
null value with a text
SQL> Set NULL ?N/A?
to reset
SQL> Set NULL ??
27. What are the more common
pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID,
ROWNUM
28. What is the output of SIGN
function?
1 for positive value,
0 for Zero,
-1 for
Negative value.
29. What is Cursor ?
A
Cursor is a handle ( a name or pointer) for the memory associated with a
specific statement
30. What is the maximum number of
triggers, can apply to a single table?
12 triggers
31. What is a Procedure
?
A Procedure consist of a set of SQL and PL/SQL statements
that are grouped together as a unit to solve a specific problem or perform a set
of related tasks.
32. What is difference between
Procedures and Functions ?
A Function returns a value to
the caller where as a Procedure does not.
33. What is a Package
?
A Package is a collection of related procedures,
functions, variables and other package constructs together as a unit in the
database.
34. What are the advantages of having
a Package ?
Increased functionality (for example,global
package variables can be declared and used by any proecdure in the package) and
performance (for example all objects of the package are parsed compiled, and
loaded into memory once)
35. What are the uses of Database
Trigger ?
Database triggers can be used to automatic data
generation, audit data modifications, enforce complex Integrity constraints, and
customize complex security authorizations.
36. What are the differences between
Database Trigger and Integrity constraints ?
A declarative
integrity constraint is a statement about the database that is always true.
A
constraint applies to existing data in the table and any statement that
manipulates the table.
A trigger does not apply to data loaded before the
definition of the trigger, therefore, it does not guarantee all data in a table
conforms to the rules established by an associated trigger.
A trigger can
be used to enforce transitional constraints where as a declarative integrity
constraint cannot be used.
37. What is an Integrity Constrains
?
An integrity constraint is a declarative way to define a
business rule for a column of a table.
38.
Describe the different type of Integrity Constraints supported by ORACLE
?
NOT NULL Constraint - Disallows NULLs in a table's
column.
UNIQUE Constraint - Disallows duplicate values in a column or set of
columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a
column or set of columns.
FOREIGN KEY Constrain - Require each value in a
column or set of columns match a value in a related table's UNIQUE or PRIMARY
KEY.
CHECK Constraint - Disallows values that do not satisfy the logical
expression of the
constraint.
39. What is difference between UNIQUE
constraint and PRIMARY KEY constraint ?
A column defined as
UNIQUE can contain NULLs while a column defined as
PERIMETER KEY can't
contain Nulls.
40. What is the maximum number of
CHECK constraints that can be defined on a column ?
No
Limit.
41. What is difference between
TRUNCATE & DELETE ?
TRUNCATE commits after deleting
entire table i.e., can not be rolled back.
Database triggers do not fire on
TRUNCATE
DELETE allows the filtered deletion. Deleted records can be
rolled back or committed.
Database triggers fire on DELETE.
42. What is a join ? Explain the
different types of joins ?
Join is a query which retrieves
related columns or rows from multiple tables.
Self Join - Joining the
table with itself.
Equi Join - Joining two tables by equating two common
columns.
Non-Equi Join - Joining two tables by equating two common
columns.
Outer Join - Joining two tables in such a way that query can also
retrive rows that do not have corresponding join value in the other
table.
43. Difference between SUBSTR and
INSTR ?
INSTR (String1,String2(n,(m)),
INSTR returns the
position of the mth occurrence of the string 2 in string1. The search begins
from nth position of string1.
SUBSTR (String1 n,m)
SUBSTR returns a
character string of size m in string1, starting from nth postion of
string1.
44. Explain UNION,MINUS,UNION ALL,
INTERSECT ?
INTERSECT returns all distinct rows selected by
both queries.
MINUS - returns all distinct rows selected by the first query
but not by the second.
UNION - returns all distinct rows selected by either
query
UNION ALL - returns all rows selected by either query,including all
duplicates.
45. What is ROWID
?
ROWID is a pseudo column attached to each row of a table.
It is 18 character long, blockno, rownumber are the components of
ROWID.
46. What is the fastest way of
accessing a row in a table ?
Using ROWID.
47. What is difference between CHAR
and VARCHAR2 ? What is the maximum SIZE allowed for each type
?
CHAR pads blank spaces to the maximum length. VARCHAR2
does not pad blank spaces. For CHAR it is 255 and 2000 for
VARCHAR2.
48. 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 columns is allowed. It is not possible to
use LONG column in WHERE or ORDER BY clause.
49. What are the pre requisites
?
i. to modify datatype of a column
?
ii. to add a column with NOT NULL
constraint ?
To Modify the datatype of a column the column
must be empty.
To add a column with NOT NULL constrain, the table must be
empty.
50. Where the integrity constrints
are stored in Data Dictionary ?
The integrity constraints
are stored in USER_CONSTRAINTS.
51. How will you a
activate/deactivate integrity constraints ?
The integrity
constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE
constraint.
52. If an unique key constraint on
DATE column is created, will it validate the rows that are inserted with SYSDATE
?
It won't, Because SYSDATE format contains time attached
with it.
53.
How to access the current value and next value from a sequence ? Is it possible
to access the current value in a session before accessing next value
?
Sequence name CURRVAL, Sequence name NEXTVAL.
It is not
possible. Only if you access next value in the session, current value can be
accessed.
54. What is CYCLE/NO CYCLE in a
Sequence ?
CYCLE specifies that the sequence continues to
generate values after reaching either maximum or minimum value. After pan
ascending sequence reaches its maximum value, it generates its minimum value.
After a descending sequence reaches its minimum, it generates its
maximum.
NO CYCLE specifies that the sequence cannot generate more values
after reaching its maximum or minimum value.
55. 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.
56. How many types of database
triggers can be specified on a table ? What are they
?
Insert
Update
Delete
Before Row
Yes
Yes
Yes
After Row
Yes
Yes
Yes
Before Statement Yes
Yes
Yes
After Statement Yes
Yes
Yes
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.
57. 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.
58. 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.
59.
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.
60.
You have just compiled a PL/SQL package but got errors, how would you view the
errors?
SHOW ERRORS
Level:
Intermediate
1. 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.
2. What are the components of Logical
database structure of ORACLE database?
Tablespaces and the
Database's Schema Objects.
3. What is a
Tablespace?
A database is divided into Logical Storage Unit
called tablespaces. A tablespace is used to grouped related logical structures
together.
3a. What is Tablespace Quota
?
The collective amount of disk space available to the
objects in a schema on a particular tablespace.
4. 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.
5. Explain the relationship among
Database, Tablespace and Data file.
Each databases
logically divided into one or more tablespaces One or more data files are
explicitly created for each tablespace.
6. What is
schema?
A schema is collection of database objects of a
User.
7. What are Schema Objects
?
Schema objects are the logical structures that directly
refer to the database's data. Schema objects include
tables,views,sequences,synonyms, indexes, clusters, database triggers,
procedures, functions packages and database links.
8. Can objects of the same Schema
reside in different tablespaces.?
Yes.
9. Can a Tablespace hold objects from
different Schemes ?
Yes.
16. What is a Synonym
?
A synonym is an alias for a table, view,sequence or
program unit.
17. What are the type of Synonyms
?
There are two types of Synonyms Private and
Public.
18. What is a Private Synonyms
?
A Private Synonyms can be accessed only by the
owner.
19. What is a Public Synonyms
?
A Public synonyms can be accessed by any user on the
database.
20. What are synonyms used for
?
Synonyms are used to : Mask the real name and owner of an
object.
Provide public access to an object
Provide location transparency
for tables,views or program units of a remote database.
Simplify the SQL
statements for database users.
21. 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.
22. How are Indexes Update
?
Indexes are automatically maintained and used by ORACLE.
Changes to table data are automatically incorporated into all relevant
indexes.
23. What is Database Link
?
A database link is a named object that describes a "path"
from one database to another.
24. What are the types of Database
Links ?
Private Database Link, Public Database Link &
Network Database Link.
25. What is Private Database Link
?
Private database link is created on behalf of a specific
user. A private database link can be used only when the owner of the link
specifies a global object name in a SQL statement or in the definition of the
owner's views or procedures.
26. What is Public Database Link
?
Public database link is created for the special user
group PUBLIC. A public database link can be used when any user in the associated
database specifies a global object name in a SQL statement or object
definition.
27. What is Row Chaining
?
In Circumstances, all of the data for a row in a table
may not be able to fit in the same data block. When this occurs , the data for
the row is stored in a chain of data block (one or more) reserved for that
segment.
28. What is a Data File
?
Every ORACLE database has one or more physical data
files. A database's data files contain all the database data. The data of
logical database structures such as tables and indexes is physically stored in
the data files allocated for a database.
29. What are the Characteristics of
Data Files ?
A data file can be associated with only one
database.
Once created a data file can't change size.
One or more data
files form a logical unit of database storage called a tablespace.
30. What is a Redo Log
?
The set of Redo Log files for a database is collectively
known as the database's redo log.
31. What is the function of Redo Log
?
The Primary function of the redo log is to record all
changes made to data.
32. What is the use of Redo Log
Information ?
The Information in a redo log file is used
only to recover the database from a system or media failure the prevents
database data from being written to a database's data files.
33. What does a Control file Contain
?
A Control file records the physical structure of the
database. It contains the following information.
i. Database Name
ii.
Names and locations of a database's files and redolog files.
iii. Time stamp
of database creation.
34. What is the use of Control File
?
When an instance of an ORACLE database is started, its
control file is used to identify the database and redo log files that must be
opened for database operation to proceed. It is also used in database
recovery.
35. What is a Data Dictionary
?
The data dictionary of an ORACLE database is a set of
tables and views that are used a read-only reference about the
database.
It stores information about both the logical and physical
structure of the database, the valid users of an ORACLE database, integrity
constraints defined for tables in the database and space allocated for a schema
object and how much of it is being used.
3 6. What is Two-Phase Commit
?
Two-phase commit is mechanism that guarantees a
distributed transaction either commits on all involved nodes or rolls back on
all involved nodes to maintain data consistency across the global distributed
database. It has two phase, a Prepare Phase and a Commit Phase.
37. Describe two phases of Two-phase
commit ?
Prepare phase - The global coordinator (initiating
node) ask a participants to prepare (to promise to commit or rollback the
transaction, even if there is a failure)
Commit - Phase - If all
participants respond to the coordinator that they are prepared, the coordinator
asks all nodes to commit the transaction, if all participants cannot prepare,
the coordinator asks all nodes to roll back the transaction.
38. What is the mechanism provided by
ORACLE for table replication ?
Snapshots and SNAPSHOT
LOGs
39. What is a SQL *
NET?
SQL *NET is ORACLE's mechanism for interfacing with
the communication protocols used by the networks that facilitate distributed
processing and distributed databases. It is used in Clint-Server and
Server-Server communications.
40. What is the use of ANALYZE
command ?
To perform one of these function on an
index,table, or cluster:
- to collect statisties about object used by the
optimizer and store them in the data
dictionary.
- to delete statistics
about the object used by object from the data dictionary.
- to validate the
structure of the object.
- to identify migrated and chained rows of the table
or cluster.
41. What are the benefits of
distributed options in databases ?
Database on other
servers can be updated and those transactions can be grouped together with
others in a logical unit.
Database uses a two phase commit.
42. What is the use of COMPRESS
option in EXP command ?
Flag to indicate whether export
should compress fragmented segments into single extents.
43. What is the use of GRANT option
in EXP command ?
A flag to indicate whether grants on
databse objects will be exported or not. Value is 'Y' or
'N'.
44. What is the use of INDEXES option
in EXP command ?
A flag to indicate whether indexes on
tables will be exported.
45. What is the use of ROWS option in
EXP command ?
Flag to indicate whether table rows should be
exported. If 'N' only DDL statements for the
databse objects will be
created.
46. What is the use of CONSTRAINTS
option in EXP command ?
A flag to indicate whether
constraints on table need to be exported.
47. What is the use of FULL option in
EXP command ?
A flag to indicate whether full databse
export should be performed.
48. What is the use of OWNER option
in EXP command ?
List of table accounts should be exported.
49. What is the use of TABLES option
in EXP command ?
List of tables should be
exported.
50. What is the use of RECORD LENGTH
option in EXP command ?
Record length in
bytes.
51. What is the use of INCTYPE option
in EXP command ?
Type export should be performed
COMPLETE,CUMULATIVE,INCREMENTAL.
52. What is the use of RECORD option
in EXP command ?
For Incremental exports, the flag
indirects whether a record will be stores data dictionary
tables recording
the export.
53. What is the use of PARFILE option
in EXP command ?
Name of the parameter file to be passed
for export.
54.What is the use of FILE option in
IMP command ?
The name of the file from which import should
be performed.
55. What is the use of IGNORE option
in IMP command ?
A flag to indicate whether the import
should ignore errors encounter when issuing CREATE
commands.
56. What is the use of GRANT option
in IMP command ?
A flag to indicate whether grants on
database objects will be imported.
57. What is the use of INDEXES option
in IMP command ?
A flag to indicate whether import should
import index on tables or not.
58. What is the use of ROWS option in
IMP command ?
A flag to indicate whether rows should be
imported. If this is set to 'N' then only DDL for database objects will be
exectued.
59.
What is the difference between a TEMPORARY tablespace and a PERMANENT
tablespace?
A temporary tablespace
is used for temporary objects such as sort structures while permanent
tablespaces are used to store those objects meant to be used as the true objects
of the database.
60.
How can you rebuild an index?
ALTER INDEX
<index_name> REBUILD;
61.
What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle
utilities are used for loading data into the database. The difference is that
the import utility relies on the data being produced by another Oracle utility
EXPORT while the SQL*Loader utility allows data to be loaded that has been
produced by other utilities from different data sources just so long as it
conforms to ASCII formattsed or delimited files.
61.
Name two files used for network connection to a database.
TNSNAMES.ORA and
SQLNET.ORA
PL/SQL
1. 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.
2. 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.
3. What are the components of a
PL/SQL block ?
A set of related declarations and procedural
statements is called block.
4. What are the components of a
PL/SQL Block ?
Declarative part, Executable part and
Execption part.
Datatypes PL/SQL
5. 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.
6. 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.
7. 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.
8. 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.
Cursors
9. 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.
10. Explain the two type 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.
11. What are the PL/SQL Statements
used in cursor processing ?
DECLARE CURSOR cursor name,
OPEN cursor name, FETCH cursor name INTO
<variable list> or Record
types, CLOSE cursor name.
12. 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.
13. 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;
14. 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.
15. 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)
16. 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.
17. 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.
18. Where the Pre_defined_exceptions
are stored ?
In the standard package.
Procedures,
Functions & Packages ;
19. What is difference between a
PROCEDURE & FUNCTION ?
A FUNCTION is always returns a
value using the return statement.
A PROCEDURE may return one or more values
through parameters or may not
Return at all.
20. What are advantages of Stored
Procedures?
Extensibility, Modularity, Reusability,
Maintainability and one time compilation.
21. What are the modes of parameters
that can be passed to a procedure ?
IN,OUT,IN-OUT
parameters.
22. What are the two parts of a
procedure ?
Procedure Specification and Procedure
Body.
23. Give the structure of the
procedure ?
PROCEDURE name (parameter
list.....)
is
local variable declarations
BEGIN
Executable
statements.
Exception.
exception handlers
end;
24. Give the structure of the
function ?
FUNCTION name (argument list .....) Return
datatype is
local variable declarations
Begin
executable
statements
Exception
execution handlers
End;
25. 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');
26. 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
27. 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, Information.
Hiding,. reusability and
Better Performance.
28.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.
29. 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.
30. Name the tables where
characteristics of Package, procedure and functions are stored
?
User_objects, User_Source and User_error.
31. 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<
BR>INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOU
RCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
