What
is PL/SQL? Describe the block structure of PL/SQL? Advantages of
pl/sql ?
PL/SQL is a
procedural language designed specifically to embrace SQL statements
within its syntax. PL/SQL program units are compiled by the Oracle
Database server and are stored inside the database. And at run-time,
both PL/SQL and SQL run within the same server process, bringing
optimal efficiency. PL/SQL automatically inherits the robustness,
security, and portability of the Oracle Database.
PL/SQL is a
block-structured language. A PL/SQL block is defined by the keywords
DECLARE, BEGIN, EXCEPTION, and END, which break up the block into
three sections.
PL/SQL is a
completely portable, high-performance transaction processing language
that offers the following advantages:
- Support for SQL
- Support for object-oriented programming
- Better performance
- Higher productivity
- Full portability
- Tight integration with Oracle
- Tight security
What
is an anonymous block ?
The PL/SQL anonymous
block statement is an executable statement that can contain PL/SQL
control statements and SQL statements. The anonymous block statement,
which does not persist in the database, can consist of up to three
sections: an optional declaration section, a mandatory executable
section, and an optional exception section.
Is
there a limit on the size of a PL/SQL block ?
Currently, the
maximum parsed/compiled size of a PL/SQL block is 64K and the maximum
code size is 100K.You can run the following select statement to query
the size of an existing package or procedure. SQL> select * from
dba_object_size where name = 'procedure_name'
Which
Datatypes are available in PL/SQL ?
There are mainly two
main Datatypes available in PL/SQL which are further sub-divided into
many datatypes and these are:
1. Composite
Datatypes: Record, Table, etc.
2. Scalar Datatypes:
Date,Number, Boolean, Varchar, Long, Varchar2, etc.
What
is the difference between CHAR and VARCHAR2 ?
CHAR is used for
storing fixed length string and unused bytes is padded with blank
space. While VARCHAR2 is used to store variable length string and
consume only used bytes and so it is memory efficient. During
comparison of string CHAR type variable need to be trimed using TRIM
to remove blank space.
VARCHAR is reserved
for future by Oracle.
What
are % TYPE and % ROWTYPE ? What are the advantages of using these
over datatypes ?
%TYPE provide
datatype of a column of a table to a variable while %ROWTYPE provide
record datatype that represent a row of a table or view or selected
columns of a cursor.
The advantages are:
first, no need to know the column datatype while declaring variable
and second if the database definition of a column in a table changes,
the data type of a variable changes accordingly.
Can
we declare a column having number data type and its scale is larger
than precision ex: column_name NUMBER(10,100), column_name
NUMBAER(10,-84) ?
Yes
What
is difference between single row functions and group functions ?
Single-row functions
return a single result row for every row of a queried table or view.
These functions can appear in select lists, WHERE clauses, START WITH
and CONNECT BY clauses, and HAVING clauses. Commonly used single-row
function are :
- Numeric function : ABS, CEIL, FLOOR, LN, LOG, MOD, POWER, REMAINDER, SQRT
- Characeter function : CHR, CONCAT, LOWER, UPPER, LPAD, RPAD, TRIM, LTRIM, RTRIM, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REPLACE, INSTR, SUBSTR, TRANSLATE, ASCII, LENGTH
- Datetime function : ADD_MONTHS, CURRENT_DATE, CURRENT_TIMESTAMP, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY
- Conversion function : TO_CHAR, TO_DATE, CAST
- Collection function : CARDINALITY, COLLECT, POWERMULTISET, POWERMULTISET_BY_CARDINALITY, SET
- Some miscellaneous function : NVL, DECODE, GREATEST, LEAST
Group
functions return a single result row based on groups of rows, rather
than on single row. Aggregate Functions like AVG,
MIN, MAX, COUNT, SUM, FIRST, LAST and Analytic Functions like LEAD,
LAG, RANK, DENSE_RANK
are group fucntions.
What
is an aggregate function ?
Aggregate functions
return a single result row based on groups of rows, rather than on
single rows. Aggregate functions can appear in select lists and in
ORDER BY and HAVING clauses. They are commonly used with the GROUP BY
clause in a SELECT statement, where Oracle Database divides the rows
of a queried table or view into groups.
How
do you convert a date to a string ?
using TO_CHAR
function.
How
to convert RAW datatype into TEXT ?
UTL_RAW.cast_to_varchar2
();
What
is difference between SUBSTR and INSTR ?
SUBSTR used to get
sub string from a given string while INSTR used to find index or
location of a string in a given string.
What
is the difference between TRANSLATE and REPLACE ?
REPLACE function
replace a string with other string in a given string.
E.g. :
REPLACE('IPHONE OR IPHONE','HONE','AD') will produce IPAD OR IPAD
TRANSLATE replaces a
sequence of characters in a string with another set of characters.
The replacement is done single character at a time.
E.g. :
TRANSLATE('IPHONE OR IPHONE','HONE','AD') will produce IPAD DR IPAD
What
is the dual table ?
The DUAL table is a
special one-row, one-column named DUMMY of type VARCHAR2(1) with
value 'X' table present by default. It is usefull for selecting
values from some system function like sysdate, TO_DATE etc.
Name
few pseduocolumns in oracle ?
rownum, currval,
nextval
What
is the difference between rowid and rownum ?
rowid is unique
hexadecimal code for each row of a table. rownum is pseduo column
that represent a number indicating the order in which Oracle selects
the row from a table or set of joined rows.
What
is difference between DECODE and TRANSLATE ?
TRANSLATE is a
character function which replaces a sequence of characters in a
string with another set of characters. The replacement is done single
character at a time.
DECODE function
compares expr to each search value one by one. If expr is equal to a
search, then Oracle Database returns the corresponding result. If no
match is found, then Oracle returns default. If default is omitted,
then Oracle returns null.
What
is difference between DECODE and CASE ?
CASE can work with
logical operators other than ‘=’
CASE can work with
predicates and searchable subqueries
CASE can work as a
PL/SQL construct
CASE handles NULL
differently
CASE expects
datatype consistency, DECODE does not
CASE is ANSI
SQL-compliant
Readability: DECODE
is shorter and easier to understand than CASE.
What
will you use in Query : IN or EXISTS and Why ?
Lets first
understand how they are processed.
Select * from T1
where x in ( select y from T2 )
is typically
processed as:
select *
from
t1, ( select distinct y from t2 ) t2
where
t1.x = t2.y;
The subquery is
evaluated, distinct'ed, indexed (or hashed or sorted) and then joined
to the original table -- typically.
As opposed to
select * from t1
where exists ( select null from t2 where y = x )
That is processed
more like:
for
x in ( select * from t1 )
loop
if
( exists ( select null from t2 where y = x.x )
then
OUTPUT
THE RECORD
end
if
end
loop
It always results in
a full scan of T1 whereas the first query can make use of an index on
T1(x).
So, when is where
exists appropriate and in appropriate?
Lets say the result
of the subquery
(
select y from T2 )
is "huge"
and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is very very fast
(nice index on t2(y)). Then the exists will be faster as the time to
full scan T1 and do the index probe into T2 could be less then the
time to simply full scan T2 to build the subquery we need to distinct
on.
Lets say the result
of the subquery is small -- then IN is typicaly more appropriate.
If both the subquery
and the outer table are huge -- either might work as well as the
other -- depends on the indexes and other factors.
What
is Sequence ?
Sequence is a
database object from which multiple users may generate unique
integers. It is used to automatically generate primary key values.
Write
the order of precedence for validation of a column in a table ? i.
done using Database triggers. ii. done using Integarity
Constraints.
If it is before
trigger then the trigger will run first before the constriants check
in the table. If it is after trigger, contraints are checked first
and then trigger will run.
What
rules are to be taken care of when doing comparisons using NULL ?
1. A NULL should
never be TRUE or FALSE.
2. If a value in an
expression is NULL, then the expression itself evaluates to NULL
except for Concatenation Operator.
3. A NULL is never
equal or unequal to other values.
4. NULL is not a
value, it represents absence of data. So NULL is something UNIQUE
What
operators deal with NULL ?
NVL converts NULL to
another specified value.
e.g.:
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.
What
is set serveroutput function used for in PL/SQL ?
To produce the
Output on the console using dbms_output.put_line() function, we need
to first set the server output to ON state. For this, the command is:
set serveroutput on;
What
is the difference between GRANT and REVOKE command ?
A Grant command
provide privileges to users & roles or a role to users &
roles whereas a Revoke command remove privileges from users &
roles or a role to users & roles.
What
are Global Temporary tables ?
Global Temporary
tables are database tables which store data temporarily as the data
is available only to the current session or current transaction. They
are called as Global because the table definition is available to all
sessions.
e.g.: Create global
temporary TempData(
id number(10),
name varchar2(20)
) on commit
delete rows /*OR on commit PRESERVE rows*/;
commit delete rows :
data should be deleted at the end of the transaction, or the end of
the session.
commit PRESERVE rows :
rows should persist beyond the end of the transaction and will only
be removed at the end of the session.
What
is dynamic SQL in oracle ?
Dynamic SQL is a
programming methodology for generating and running SQL statements at
run time. It is useful when writing general-purpose and flexible
programs like ad hoc query systems, when writing programs that must
run database definition language (DDL) statements, or when you do not
know at compilation time the full text of a SQL statement or the
number or data types of its input and output variables.
PL/SQL provides two
ways for writing it:
First, by using
EXECUTE IMMEDEATE and second, by using
DBMS_SQL package.
Which
of the following is not a schema object : Index, table, public
synonym, trigger and package ?
Public synonym
