Can we define cursor inside package if so how to call cursors inside the package?

Yes, we can. call using packagename.cursorname.
Ex:  OPEN XX_INVOICE_VAL_PKG.c_eligible_invoice
       LOOP XX_INVOICE_VAL_PKG.c_eligible_invoice..

       END LOOP;

Can we set Index for primary key? If so what is the name of the Index?

Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:

create table foo (a int primary key,
                          b varchar(20) unique);

Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on Constraints).

To find out what indexes you have, use
    select index_name from user_indexes;

USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

Decode vs Case

Example for Decode and Case:

SQL> SELECT ename,
  2         DECODE(deptno, 10, 'ACCOUNTING',
  3                        20, 'RESEARCH',
  4                        30, 'SALES',
  5                        40, 'OPERATIONS',
  6                            'UNKNOWN') AS department
  7    FROM emp
  8   WHERE rownum < 4
  9  /

SQL> SELECT ename,
  2         CASE deptno
  3           WHEN 10 THEN 'ACCOUNTING'
  4           WHEN 20 THEN 'RESEARCH'
  5           WHEN 30 THEN 'SALES'
  6           WHEN 40 THEN 'OPERATIONS'
  7           ELSE
  8                        'UNKNOWN'
  9         END AS department
 10    FROM emp
 11   WHERE rownum < 4
 12  /

Difference:
1.
DECODE works with expressions which are scalar values.
CASE can work with predicates and subqueries in searchable form

SQL> select ename,
  2   case
  3     when ename in ('KING','SMITH','ALLEN') then
  4        'Managers'
  5     when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
  6        'Guy from 10th'
  7     else
  8        'Another person'
  9   end blah_blah
 10  from emp  
 11  /

2.
CASE executes faster in the optimizer than does DECODE.

3.
DECODE will return "true" if you compare NULL to NULL. CASE will not.

DECODE(NULL, NULL, 1, 0)
will return '1'.

CASE NULL
    WHEN NULL THEN 1
    ELSE 0
END
will return '0'.

You would have to write it as:
CASE
    WHEN NULL IS NULL THEN 1
    ELSE 0
END
will return '1'

4.
DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure

SQL> Declare
  1  a varchar2 := 'ONE';
  2  Begin
  3    procedure_01(case :a when 'ONE' then 1 else 0 end);
  4  End;
  5  /
The number  = 1

Pl/Sql FAQ-1

1. What is the difference b/w subquery and correlated subquery?
a) In a normal subquery, the inner query is executed first and then the result are passed off to the parent query.
b) In a correlated subquery, the outer query is executed first and process each row, the subquery is using. The info supplies by the result of the outer query.

2. How to delete duplicate rows from a table?
Delete test t1 where rowed< (Select max(rowed) from test t2 where t2.col1 = t1.col and t2.col2 = t1.col1.

3. what are the types of database triggers?
Ro. Total no of database triggers are 12. They are
a) Row level : once for every row affected by the triggering statement such as a trigger fired by an update statement that updates many rows.
b) Statement level : once for the triggering statement, no matters be many rows it affects.
c) Before : before triggering statement is executed.
d) After: After triggering statement is executed.
e) Instead of : triggers provider a transparent way of modifying that cant be modified through DML statement.

4. what is mutating error on a table?
It happens when a trigger on a table tries to insert, update or ever select the table of where trigger is being executed.

5. What is synonym? What is view?
A synonym is an alias for any table, sequence, procedure function or package. It requires no storage other than its definitions in the data dictionary.
A view is stored query, from one or more tables.

6. What is database link?
A database link is a pointer that defines a one_way communication path from an oracle database server to another database server

7. What is a dynamic SQL?
DDL statement can’t be used within PL/SQL using “Execute Immediate”

8. What are set operator?
Set operators combine the result of two component queries into a single result queries containing set operator are called components queries
UNION : all rows selected by either query
UNION ALL : All rows selected by either query, including all duplicate
INTERSECT : All distinct rows selected by both queries
MINUS : All distinct rows selected by the first query but not the second.

9, What is savepoint?
Savepoint are intermediate markers within the context of a transaction savepoint divide a long transaction into smaller parts we then have to option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.

10. What are the benefits of using package? Name few oracle supplied package
An 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 stand alone procedure. A package body can be replaced and recompiled without affecting the specification. Definition of procedure/variable can be private or public. For example I have 5 procedure out of 3 procedures can be used by a DBMS_SQL, DBMS_JOB, UTL_FILE.

11. What is deadlock?
A deadlock can occur when two or more user are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.

What is NOCOPY hint?

Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:
    * IN: parameters are passed by reference
    * OUT: parameters are implemented as copy-out
    * IN OUT: parameters are implemented as copy-in/copy-out

The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised so that changes could be rolled back. Because a copy of the parameter set was made rollback could be done. However this method imposed significant CPU and memory overhead when the parameters were large data collections for example PL/SQL Table or VARRAY types.

With the new NOCOPY option OUT and IN OUT parameters are passed by reference which avoids copy overhead. However parameter set copy is not created and in case of an exception rollback cannot be performed and the original values of parameters cannot be restored.

Here is an example of using the NOCOPY parameter option:

TYPE Note IS RECORD( Title VARCHAR2(15), Created_By VARCHAR2(20), Created_When DATE, Memo VARCHAR2(2000));
TYPE Notebook IS VARRAY(2000) OF Note;
CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) IS BEGIN ...END;

What is Function Overloading?

Two or more procedures or functions are called overloaded when
a) They have the same names
b) Different no of formal parameters defined
c) Formal parameters differ in their datatypes/Subtypes and not in the same family
d) They belong to same subprogram/package/PL SQL Block

They are not overloaded when
a) Any of the above points are not satisfied
b) RETURN datatype alone differs and not the formal parameters

The main purpose of overloading procedures is that when a PL SQL block is found to do a same operation with different inputs we names them same and feed different parameters

Explain Actual and Formal Parameters

Formal Parameter: A variable declared in the parameter list of a subprogram specification
Example:  create or replace procedure/function x (p_id number p_sal number)

Actual Parameter: A variable or expression refrenced in the parameter list of a subprogram call.
example:  execute x( 100 v_sal)

Diff between Function and Procedure?

1.Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2.Function can be called from SQL statements where as procedure can not be called from the sql statements
3.Functions are normally used for computations where as procedures are normally used for executing business logic.
4.You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.Function returns 1 value only. Procedure can return multiple values (max 1024).

What is Forward Declaration?

In PL/SQL (and other languates like Pascal) a program unit such as a procedure or function must be defined before it can be invoked by another program unit. In PL/SQL package bodies forward declarations provide an optional means to get around this. You declare all program units before they actually appear in the package. A forward declaration is merely the name of the program unit and any parameters required. With forward declarations in place you are now free to arrange the program units in the package body in any order so as to improve readability or group program units together logically. Note that forward declarations are not required as long as you code a program unit before it is called. Additionally forward declarations can appear anywhere not just at the top of the package body. Anytime you declare a program unit in advance of the actual coding that is a forward declaration.

Example:
procedure A is
begin
B;
end A;

procedure B is
begin
null;
end B;

Will not work because during call to B, B is still unknown (1 Step compiler) therefore we need a forward declaration,

procedure B;

procedure A is
begin
B;
end A;

procedure B is
begin
null;
end B;

Now we can compile

Source=> Net