Nov 30, 2010

Oracle apps’s technical FAQs 5

(ERP) Oracle Applications 11i,Release 12 - Technical (eBussiness Suite):

52) what is REF Cursor?

To execute a multi-row query, oracle opens an unnamed work area that stores processing information, to access the information, an explicit, which names the work area or, a cursor variable, which points to the work area.

where as a cursor always refers to the same query work area, a cursor variable can refer to a different work areas, cursor variable area like ‘c’ or ‘pascal’ pointers, which hold the memory location(address) of some object instead of the object itself.

So, declaring a cursor variable creates a pointers, not an object.

32) Can u define exceptions twice in same block?


33) Can you have two functions with the same name in a pl/sql block?


34) Can you have two stored functions with in the same name?


35) Can function be overload?


36) What is the maximum number of statements that can be specified in a trigger statement?


32) Stored procedure?

Stored procedure is a sequence of statements that perform specific function.

53) What is procedure?

---- is a named pl/sql block to perform a specific task.

---- A procedure may have DML statements.

---- It may or may not return a value.

---- Procedure can return more than one value.

Example for procedure

1) To accept the year as a parameter and list emp belong to the year?

Create or replace

Procedure empy(y number) is

Cursor emp_cursor is

Select * from emp where to_char(hiredate,’yyyy’)=’y’;

Emp_record emp%rowtype;


For emp_record in emp_cursor loop

Print (emp_record.empno);

Print (emp_record.ename);

Print (emp_record.sal);

End loop;


Output :

var empx number;


:empx := ‘1234’;


Exec empy(:empx);

Print empy;

54) What is function?

---- is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.

---- A function is called as part of an exception.

---- Every function should return a value

Example for function

Create or replace

Function get_sal(p_id in emp.emp_no% type)

Return number


v_sal emp.sal%type :=0;


Select salary into v_salary

From emp

Where emp_no = p_id;

Return v_salary

End get_sal;


Output :

var g_sal number;

Exec :g_sal := get_sal(99);

Print g_salary;

9.Can functions be overloaded ?


10.Can 2 functions have same name & input parameters but differ only by return datatype


55) What is the package?

---- Group logically related pl/sql types, items and subprograms.

1) package specification

2) package body

Advantages of a package:

· Modularity

· Easier Application Design

· Information Hiding

· Overloading

You cannot overload:

•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).

•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)

•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)

•Two functions that differ only in return type, even if the types are in different families.

56) What is FORWARD DECLARATION in Packages?

PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:

• Define subprograms in logical or alphabetical order.

• Define mutually recursive subprograms.(both calling each other).

• Group subprograms in a package

Example of forward Declaration:



PROCEDURE calc_rating(. . .); -- forward declaration

PROCEDURE award_bonus(. . .)

IS -- subprograms defined

BEGIN -- in alphabetical order

calc_rating(. . .);

. . .


PROCEDURE calc_rating(. . .)



. . .


END forward_pack;

No comments:

OraApps Search

Custom Search

Search This Blog