Interview Questions

 1) What is the difference between an INLINE VIEW And SQL WITH CLAUSE ?

  The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

A subquery in the FROM clause of a SELECT statement is also called an inline view
Inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.


2) To view last executed statement in your session

       select * from table(dbms_xplan.display_cursor);



3)  What is the difference between execute and call statements ?

The main difference between EXECUTE and CALL command is that EXECUTE is a SQL*Plus command
whereas CALL is a SQL command AND understands SQL data types only.
In CALL STATEMENT using  PL/SQL datatypes (ie: PLSQL tables)  will result an error.

4) Difference between Varchar2(Bytes) & Varchar2(Char)  ?

 If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store , e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes  


5) Difference between Int & Number   ?
An integer is a "whole number". (Not just a display feature.) When you insert a number that has decimal places into an integer field, oracle is performing an implicition conversion from a number to an integer. (which removes the decimal places.) Any additional decimal places will be permanently lost