What is Pragma Restrict References ? when we use it ? what is WNDS, RNDS, WNPS, RNPS, TRUST ?

Pragma Restrict Reference:
----------------------------------

• The RESTRICT_REFERENCES  pragma asserts  that a user defined sub program does not read or write database tables or package variables.

• Sub programs that read or write database tables or package variables are difficult to optimize, because any call to the sub program might produce different results or encounter errors.
• To restrict these unexpected results we use pragma RESTRICT_REFERENCES.

PRAGMA:

--------------

                       signifies that the statement is pragma ( compiler directive). pragmas are processed at compile time, not at run time. they pass information to the compiler at compile time.

Syntax for PRAGMA RESTRICT_REFERENCES:

Sub program name:
------------------------
                                     The name of user defined sub program, usually a function. if sub program name is over loaded, the pragma applies only to the most recent sub program declaration.


Default:
----------

                   Specifies that the pragma applies to all sub programs in the package specification or object type specification. (including the system defined construct for object types).

 you can still declare the pragma for individual subprograms, overriding the default pragma.

RNDS:
---------      Asserts that the sub program reads no database state (doesn't query database tables).

WNDS:
----------
                 Asserts that the sub program writes no database state (doesn't modify database tables).

RNPS:
---------
            Asserts that the sub program reads no PACKAGE state (doesn't reference the value of the package variables).

you can not specify RNPS, if the sub program invokes the SQLCODE OR SQLERRM function.


WNPS:
----------
              Asserts that the sub program writes no PACKAGE state (doesn't change the value of the package variables).

you can not specify RNPS, if the sub program invokes the SQLCODE OR SQLERRM function.

TRUST:
-----------
                Asserts that the sub program can be trusted not to voilate one or more rules.
       when you specify TRUST, the sub program body is not checked for violations of the constraints listed in the pragma. The suprogram is trusted not to voilate them skipping these checks improves the performance.

TRUST is needed for functions written in c, java that are invoked from the plsql, since plsql can't verify them at run time.

How to create a employee by using hr employee API

CREATE OR REPLACE Procedure APPS.K_EMP11(errbuf   out varchar2,

                                    retcode  out varchar2) as

cursor c1 is select * from EMP_STAGE;

L_PID NUMBER(30);

l_AID NUMBER(30);

L_OVN NUMBER(9);

L_AOVN NUMBER(9);

L_ESD DATE;

L_EED DATE;

L_FULL_NAME VARCHAR2(100);

L_CID NUMBER(9);

L_AS NUMBER(9);

L_AN VARCHAR2(100);

L_CW  BOOLEAN;

L_PW BOOLEAN;

L_HW BOOLEAN;

L_EMPNO VARCHAR2(20);

l_bid number(9);

l_flag varchar2(1);

l_count number(9) default 0;

Begin

For x1 in c1 loop

l_count:=l_count+1;

l_flag :='A';

--Business Group ID Validation

Begin

select business_group_id

into   l_bid

from  HRFV_BUSINESS_GROUPS

where business_group_id = X1.BUSINESS_GROUP_ID;

Exception

When others then

l_flag :='E';

Fnd_File.put_line(Fnd_File.log,'Invalid Business Group ID'||'Record Number ='||l_count);

End;

If(l_flag !='E') then

HR_EMPLOYEE_API.CREATE_EMPLOYEE(p_validate                 => false

                                ,p_hire_date                => TRUNC(SYSDATE)

                                ,p_business_group_id       =>x1.BUSINESS_GROUP_ID

                                ,p_last_name                =>x1.last_name

                                ,p_sex                      =>x1.sex

                                ,p_person_type_id           =>x1.PERSON_TYPE_ID

                                ,p_date_of_birth            =>x1.DATE_OF_BIRTH

                                ,p_email_address            =>x1.email

                                ,p_employee_number          =>L_EMPNO

                                ,p_first_name               =>x1.first_name

                                ,p_marital_status          =>x1.MARITAL_STATUS

                                ,p_person_id               =>L_PID

                                ,p_assignment_id           =>L_AID

                                ,p_per_object_version_number    => L_OVN

                                ,p_asg_object_version_number    =>L_AOVN

                                ,p_per_effective_start_date    =>L_ESD

                                ,p_per_effective_end_date      =>L_EED

                                ,p_full_name                   =>L_FULL_NAME

                                ,p_per_comment_id              =>L_CID

                                ,p_assignment_sequence         =>L_AS

                                ,p_assignment_number           =>L_AN

                                ,p_name_combination_warning     =>L_CW

                                ,p_assign_payroll_warning       =>L_PW

                                ,p_orig_hire_warning            =>L_HW

                                ,p_national_identifier          => x1.SSID);

End If;

End Loop;

End;

/

How to use decode in where clause ?

select 
       emp.empno
      ,emp.ename
      ,emp.deptno
      ,dept.deptno
      ,dept.dname 
from  emp
     ,dept
where emp.deptno=dept.deptno
  and (decode(emp.deptno,10,'ACCOUNTING')=dept.dname
      or
       decode(emp.deptno,20,'RESEARCH')=dept.dname
      or
       decode(emp.deptno,30,'SALES','OPERATIONS')=dept.dname);

How to use case in where clause ?

select a.col1
      ,a.col2
      ,b.col1
      ,b.col2
from A a
    ,B b
where a.bookid=b.bookid
   and 1=
   (
   case
      when a.secid=0 and a.prodid=b.prodid then 1
      when a.secid=b.secid then 1
      else
         0
   end
   );

Bulk binds with error handling

1. declare
2. cursor curr_cur is select * from emp;
3. type emp_tab is table of curr_cur%rowtype;
4. emp_array emp_tab;
5. errors number;
6. dml_errors exception;
7. pragma exception_init(dml_errors,-24381);
8. begin
9. open curr_cur;
10. loop
11. fetch curr_cur  bulk collect into emp_array limit 35;
12. forall  i in emp_array.first..emp_array.last save exceptions
13. insert into emp_temp3 values emp_array(i);
14. exit when curr_cur%notfound;
15. end loop;
16. close curr_cur;
17. exception
18. when dml_errors then
19. errors:=sql%bulk_exceptions.count;
20. dbms_output.put_line('number of statements failed are '||errors);
21. for i in 1..errors loop
22. dbms_output.put_line('error #'||i|| ' is occured during '|| 'iterations #' ||sql%bulk_exceptions(i).error_index);
23. dbms_output.put_line('error  message is ' || SQLERRM(-sql%bulk_exceptions(i).error_code));
24. end loop;
25. end;
26. /

How to find the users attached to the particular responsibility?

1. select distinct fu.USER_ID
2.                ,fu.USER_NAME
3.                ,frt.RESPONSIBILITY_ID
4.                ,frt.RESPONSIBILITY_NAME
5.                ,fu.CREATION_DATE
6. from fnd_user fu
7.     ,fnd_responsibility_tl frt
8.     ,fnd_user_resp_groups_direct furgd
9. where 1=1
10.    and frt.RESPONSIBILITY_ID=20420
11.    and fu.USER_ID=furgd.USER_ID
12.    and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
13. order by fu.CREATION_DATE desc

      (OR)

1. select distinct fu.USER_ID
2.                ,fu.USER_NAME
3.                ,frt.RESPONSIBILITY_ID
4.                ,frt.RESPONSIBILITY_NAME
5.                ,fu.CREATION_DATE
6. from fnd_user fu
7.     ,fnd_responsibility_tl frt
8.     ,fnd_user_resp_groups_direct furgd
9. where 1=1
10.    and frt.RESPONSIBILITY_ID=:lvResp_id   
11.    and fu.USER_ID=furgd.USER_ID
12.    and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
13. order by fu.CREATION_DATE desc

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