Pl/Sql Interview Questions Level 2

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