1. Difference between implicit cursor and explicit cursor.

An explicit cursor is a named query that is defined as a cursor in PL/SQL by use of  the keyword CURSOR . UPDATE, DELETE and INSERT statements always use an implicit cursor.

The difference between explicit and implicit cursors in Oracle PLSQL is that explicit cursors give you complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched. Also, fetching from an explicit cursor will never raise a NO_DATA_FOUND or a TOO_MANY_ROWS exception.

When using implicit cursors in Oracle PL/SQL, we access the cursor attributes by prefixing the attribute name with "SQL%". To access the cursor attributes of explicit cursors in Oracle PL/SQL the SQL keyword is replaced by the cursor name.

Another advantage of using explicit named cursors in PL/SQL is that they can be parameterised just like any procedure or function

2. How to delete duplicate lines using SQL statement?

a. DELETE FROM t_table WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM t_table GROUP BY c1,c2... HAVING count(*) > 1)

b. DELETE FROM T t1 WHERE t1.rowid > 
 ( SELECT MIN(t2.rowID) FROM T t2 HERE t1.col1 = t2.col1 AND t1.col2 = t2.col2);

3. What's a PL/SQL package/procedure make up of?

Package Spec, Package body, Procedure, function, declaration, variables, cursor, exception etc…

4. How to handle PL/SQL exceptions?

Exception

when no data found

then …..

when too many rows

then…

when others

then…

5. How to write a PL/SQL block?

DECLARE … BEGIN … EXCEPTION ... END;

6. What's the difference among datatype record, datatype table and datatype varray?

A collection is an ordered group of elements, all of the same type. Both datatype table and varray are collections.But record is not.

A record is a group of related data items which are logically related but dissimilar in type.

Nested tables differ from varrays in the following ways:

a. Varrays have a maximum size, but nested tables do not.

b. Varrays are always dense(have consecutive subscripts), but nested tables can be sparse. So, you can delete individual elements from a nested table but not from a varray.

7. What's the difference between function, procedure and pl/sql block?

Function and procedure are  named PL/SQL blocks that can take parameters and be

invoked. A procedure to perform an action and a function to compute a value.

8. Can a function return two values? how?

Yes. Using OUT parameters. An OUT parameter lets you return values to the caller of a subprogram.

9. How to use SQL to sort A B C 1 2 3 in the order as of "A 1 B 2 C 3" ?

Order by a function.

select t.value as v from source t order by dummyAscii(v);

create or replace function dummyAscii(chr in char) return number is

  Result number;

begin

  Result := ascii(chr);

  if (Result >= ascii('A')) THEN

     Result := Result - ascii('A') - 0.5 + ascii('1');

  END IF;

  return(Result);

end dummyAscii;

10. How to use Function Decode()? How to use function Case?

DECODE(col|expression, search1, result1[, search2, result2,...,][, default])

SELECT CASE  (expression) WHEN (condition1) THEN <value1>

            WHEN (condition2) THEN <value2>

            ELSE <value3>

FROM <table_name>;

11. What's difference between UNION and UNION ALL? What's the usage of Minus?

UNION - Combine the unique rows returned by 2 SELECT statements

UNION ALL - Combine the rows returned by 2 SELECT statements (including all duplicates)

INTERSECT - Return only those rows that are in *both* SELECT statements

MINUS - Return the rows that are in the first SELECT but not the second

12. How to throw exception when using cursor?

An open cursor doesn't FETCH. The NO_DATA_FOUND exception will be only

set after a FETCH.  An explicit cursor like that with OPEN/CLOSE -- will NEVER throw NO_DATA_FOUND. The only thing that throws that exception is a SELECT INTO.  Only with a SELECT INTO is "zero rows returned" an error. You have to actually FETCH a

record -- test the cursor attribute "cursor_name%found" -- and then decide what

you want to do.

• Relative Posts
• Global Relative Posts