PL/SQL FAQ'S IN INTERVIEWS

1)How to execute DOS Commands from SQL Prompt?

ans) By using HOST we can execute the dos commands

2)What are CBO and RBO? What is the diff between these two?

ans)   Cost Based optimization
       for details you can go to this link  http://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1656
  
       Role Based optimization
       for details you can go to this link  http://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#721


3) What is the RND and WND?

ans) RND – Read No Database
     WND – Write No Database  


4)  What is CURSOR? What are the Cursor types? What are cursor declaration steps?

ans) Cursor is nothing but a private SQL work area which is used to store process information.

Types: 1.Implicit, 2.Explicit   

cursor declaration steps:
--------------------------    1. Declare
                              2. Open
                              3. Close
Cursor Attributes:
------------------
1. %not found
2. %found
3. %is open
4. %row count

5) What is the diff between Implicit and Explicit and Ref Cursor?

ans)

1.Implicit – It is defined by the Oracle Server for queries that return only one row.
2.Explicit – Which is defined by the Users, for queries that return more than one row
3.Ref – With this we can change the select statement dynamically.
        for this first we have to declare ref cursor. and we have to associate a select
        statement at run time. before going to associate another select statement
        we have to close the cursor and again we have to open cursor.


6) What is Procedure and what is Function?
    
ans) Procedure: Is used perform an action
     Function: Is used to compute a value

procedure:
---------- generally we will use procedures when we have to perform dml operations and
procedure no need to return a value.

function:
---------   generally we will use functions to perform computation's and function
must return a value.

           Procedure                                                      function

1.may or may not return a value.                            1.Function must return a value.
2.can't call from select statement.                           2.can call from select statement.
3. It can return more than one value through          3. It can return only one value.
   OUT Parameter.


7). If we drop the table which we have used in the procedure do we need to recompile
 the procedure then how?

ans)  No need to Recompile the procedure.
      error: <SCHEMA>.<PROCEDURE NAME> INVALID.

 to recompile the procedure, use the below command.
 - ALTER PROCEDURE proc1 compile;

8). How to get the Procedure Source code from database?

ans) To get the procedure source code from the database u need to query the below the query

     select TEXT from user_source where object_type='PROCEDURE' and object_name='PROC1';


9). What are the other objects we can group inside of Package?
   
ans) Packages provide a method of encapsulating related procedures, functions, and
     associated cursors and variables together as a unit in the database.

Applications for Packages:

Packages are used to define related procedures, variables, and cursors and are
often implemented to provide advantages in the following areas:

    1.encapsulation of related procedures and variables

    2.declaration of public and private procedures, variables, constants, and cursors

    3.separation of the package specification and package body

    4.better performance:
     --------------------
                          Using packages rather than stand-alone stored procedures results
                          in the following improvements:

    1.The entire package is loaded into memory when a procedure within the
      package is called for the first time. This load is completed in one operation,
      as opposed to the separate loads required for standalone procedures.
      Therefore, when calls to related packaged procedures occur,
      no disk I/O is necessary to execute the compiled code already in memory.

    2.A package body can be replaced and recompiled without affecting the specification.
      As a result, objects that reference a package's constructs
      (always via the specification) never need to be recompiled unless
      the package specification is also replaced. By using packages,
      unnecessary recompilations can be minimized,
      resulting in less impact on overall database performance.

10). Can we declare Procedure directly in the package body without declaring
in the package specification?

ans) No, We can not declare procedure directly in package body.

11).Can we commit inside of trigger? How to delete the Trigger?
How many triggers we can use maximum?

ans:
===    yes, we can issue commit inside of the trigger body by using the
autonomus transactions.
 drop trigger j_trg;
There is no limit on triggers.

12). What are SRW Packages we have?

ans:
----
SRW.DO_SQL (DDL &DML STMT).
SRW.RUN_REPORT.


13).How to recompile the invalid objects?

ans).  You can invoke the utl_recomp package To recompile the invalid objects.

                    Schema Level
                  -------------------
 EXEC UTL_RECOMP.RECOMP_SERIAL('SCOTT');
 EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'SCOTT');

                    Database Level
                  --------------------
 EXEC UTL_RECOMP.RECOMP_SERIAL( );
 EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

           using job_queue_processes_ value.
      ------------------------------------------

 EXEC UTL_RECOMP.RECOMP_PARALLEL( );
 EXEC UTL_RECOMP.RECOMP_PARALLEL(NULL, 'SCOTT');


14) How to find the invalid objects?

ans). to find the invalid objects we have to execute the following query.

       select owner
             ,object_type
             ,object_name
       from dba_objects
       where
            status != 'VALID'
       ORDER BY
            owner,
            object_type;

     Here is a script to recompile the  invalid pl/sql packages, procedures and functions.
     You may need to run it more than once for dependencies, if you get errors from the script.


        INVALID.SQL

      
 Set Heading off;
 Set feedback off;
 Set lines 999;

   Spool  run_invalid.sql

       select 'ALTER '|| OBJECT_TYPE || ' ' ||OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM  DBA_OBJECTS
WHERE STATUS= 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE');

SPOOL OFF;

  set heading on;
  set feedback on;
  set echo on;

@run_invalid.sql

How to compile the form in server ?

to compile form in server first you have to change the directory to where you have to place the .fmx file in server.
frmcmp_batch module=xxx.fmb  userid=apps/apps  compile_all=special batch= yes

What Is Cluster in Oracle ?

A Cluster is a schema object used to store related information from multiple tables at stable physical location in a single data block.

•  we need to create a cluster with cluster column then, we need to create a index on cluster column. this index is called cluster index.
• Create the related tables and store them in a cluster by using a cluster clause.
• use the cluster clause at theend of all column definitions in cluster tables.

EX:

===

create cluster personel1 (dept number(4))

size 512

storage (initial 100k next 50k);

cluster index:

==========

create index indx_personel1 on cluster personel1;

adding tables to a cluster:

====================

create table dept_10

cluster personel1(dept)

as select * from emp where deptno=10;

create table dept_20

cluster personel1(dept)

as select * from emp where deptno=20;

WHAT IS DIFFERENCE BETWEEN WHERE CLAUSE AND HAVING CLAUSE ?

WHERE                                                                                                                 HAVING
=================================               ================================
1. WHERE CLAUSE RESTRICT THE ROWS        1.HAVING CLAUSE RESTRICT THE ROWS
RETURNED BY THE SINGLE ROW FUNCT           RETURNED BY THE GROUP FUNCTIONS.
TIONS.

2.WHERE CLAUSE SHOULD APPEAR                 2.HAVING CLAUSE SHOULD APPEAR
BEFORE GROUP BY CLAUSE.                                  AFTER GROUP BY CLAUSE.

How to display the ename from emp, which contains '%' and '_' in ename ?

'%'  and  '_' are special characters in oracle.   To compare these characters in the ename, we need to work with the ESCAPE clause.

EX:
==

select ename from emp where ename like '% /%'  escape '/';

can we apply null values and duplicate values to foreign key?

yes, we can insert null values into foreign key column. and we can also insert duplicate values into foreign key column which are there in unique key column or primary key column.

How to delete the duplicate values from table ?

To Delete duplicate values from the table we have to execute  the  query given below.

delete from emp where rowid not in (select min(rowid) from emp group by empno);