Over Loading in PL/SQL - Function Over Loading

OverLoading in PL/SQL - Function OverLoading

OverLoading means creating objects with similar but varying in type of the parameters and
number of parameters

Overloading on standalone functions is not allowed in PL/SQL.

See below example :

SQL> create function xx_ovrload(p IN number)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is Number');
  6  return 1;
  7  end;
  8  /

Function created.

Now we try to overload the function by changing the IN parameter datatype and see 

SQL> create function xx_ovrload(p IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Still we have any doubts that it might be due to same no of parameters, we also test by adding an extra parameter to function and see

SQL> create function xx_ovrload(p IN varchar2,p1 IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2,p1 IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

So where can we acheive overloading? In a package :)

SQL> create or replace package xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number) return number;
  4  function xx_ovrload(p IN varchar2) return number;
  5  end;
  6  /

Package created.

SQL> create or replace package body xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number)
  4  return number
  5  as
  6  begin
  7  dbms_output.put_line('1');
  8  return 1;
  9  end;
 10
 11  function xx_ovrload(p IN varchar2)
 12  return number
 13  as
 14  begin
 15  dbms_output.put_line('2');
 16  return 2;
 17  end;
 18  end;
 19  /

Package body created.

Test by executing :

SQL> select xx_ovrload_pkg.xx_ovrload(10) from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD(10)
-----------------------------
                            1

1
SQL> select xx_ovrload_pkg.xx_ovrload('10') from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD('10')
-------------------------------
                              2

2


NOTE : If the type and no of parameters are the same and having different RETURN datatypes, function overloading fails in this case.

Thanks!!

Oracle Joins : ANSI Standard

Oracle Joins using ANSI Standard Syntax :

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Inner Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p INNER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

OR

select p.part_id,supplier_id,s.supplier_name
from part p INNER JOIN supplier s
USING(supplier_id);

Note : We need not qualify join column like p.supplier_id/s.supplier_id
            when we utilize USING keyword.

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Left Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------    ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P4
P3

Right Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
                            Supplier#3


Full Outer Join

select p.part_id,p.supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
USING(supplier_id)
;

See the output

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P3
P4
                            Supplier#3


Note : ANSI Standard is most effective than traditional way of joining the tables.

Oracle Joins : Traditional Syntax

Oracle Joins :

1. Traditional Syntax
2. ANSI standard since Oracle 9i

Types of Joins :
1. Self Join
2. Inner/Simple Join
3. Outer Join
     a. Left Outer
     b. Right Outer
     c. Full Outer

We use the below PART and SUPPLIER tables to understand the oracle joins in a lucid manner.

CREATE TABLE PART(
part_id         varchar2(5) constraint part_id_nn NOT NULL,
supplier_id     varchar2(5)
);

begin
insert into part values ('P1','S1');
insert into part values ('P2','S2');
insert into part values ('P3',' ');
insert into part values ('P4',' ');
commit;
end;
/

CREATE TABLE SUPPLIER(
supplier_id        varchar2(5) constraint supplierid_nn NOT NULL,
supplier_name    varchar2(20) constraint suppname_nn NOT NULL
);

begin
insert into supplier values ('S1','Supplier#1');
insert into supplier values ('S2','Supplier#2');
insert into supplier values ('S3','Supplier#3');
commit;
end;
/

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Joins using traditional syntax:

Here we use operators '=' and '+' for joining tables.

Simple Join :

When a simple join is made on 2 tables, common data between the 2 tables are fetched.

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id;

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Here we did not get the parts : P3 and P4 which doesn't have a supplier yet.
Also we did not get the Supplier#3 which doesn't have a part yet.

To fetch a full table data while joining we use 'Outer Join'

Left Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
;

See the result now, we can see the other 2 parts even though they have a supplier yet.
Here we have fetched entire rows from PART table.

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P4
P3

Right Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the results now, We will fetch all suppliers even though they donot supply any Parts.

part_id    SuppId         SuppName
---------- -----------    ----------------
P1            S1          Supplier#1
P2            S2          Supplier#2
                          Supplier#3

Now how about this situation if we need all rows from PART and SUPPLIER Tables??

We go for FULL JOIN:

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
UNION
select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the output

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P3
P4
                             Supplier#3


For ANSI Standard please go to



Oracle Joins : ANSI Standard

Thanks!

Commands to see APPS, DB, Form and Workflow Versions

Commands to see APPS, DB, Form and Workflow Versions

--Query to fetch Apps Version (11i/R12/12i)select release_name from apps.fnd_product_groups;

--Query to fetch Oracle Database versionselect * from v$version;

--to see whether RAC is implemented on DB show parameter cluster_database

--Workflow version with APPSselect TEXT "Version" from  WF_RESOURCES
where name='WF_VERSION'
and language='US';

#Web Server/Apache or Application Server in Apps 11i/R12#LOG into unix box using telnet/putty
#STEPS:
#1.Log in as Application user, set environment variable and run below query
#check if IAS_ORACLE_HOME env variable is set?
echo $IAS_ORACLE_HOME
#if exists set env variable as
set IAS_ORACLE_HOME
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version

Output for 11i instance looks like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jun 21 2012 23:59:43 (iAS 1.0.2.2.2 rollup 5)

#to get Oracle forms versionset ORACLE_HOME
$ORACLE_HOME/bin/f60run | grep Version | grep Forms

Output looks like

Forms 6.0 (Forms Runtime) Version 6.0.8.28.0 (Production)

#File Version on file systemadident Header <filename>
orstrings <file_name> | grep Header

Here adident is AD Utility (Oracle Apps) and strings is Unix utility



Send email using PL/SQL

SEND EMAIL USING PL/SQL

Below is the code used :

We are creating a procedure to achieve this,

CREATE OR REPLACE PROCEDURE xx_sendmail
AS

crlf VARCHAR2(2) := CHR(13)||CHR(10);

v_Mail_Conn utl_smtp.Connection;

V_MAIL_HOST VARCHAR2(1000):= NULL;

V_MAIL_HOST1 VARCHAR2(1000):= NULL;

V_RECIPIENT VARCHAR2(1000):= NULL;

V_SUBJECT   VARCHAR2(1800):= NULL;

v_sender varchar2(400);

v_tot_msg varchar2(300);

BEGIN

 SELECT UTL_INADDR.GET_HOST_ADDRESS into v_mail_host from dual;

 dbms_output.put_line('host_addre :'||v_mail_host);

 v_mail_host1:='abc@gmail.com';

 v_sender:='abc@gmail.com';

 V_RECIPIENT:='xyz@gmail.com';

 v_subject:='Hi....';

 v_tot_msg:='mail send through pl sql code';

        V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);

        UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);

        UTL_SMTP.MAIL(V_MAIL_CONN, v_sender);

        UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);

        UTL_SMTP.DATA(V_MAIL_CONN,

        'DATE: ' || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||

        'FROM: ' || V_MAIL_HOST1 || CRLF ||

        'SUBJECT: '|| V_SUBJECT || CRLF ||

        'TO: ' || V_RECIPIENT || CRLF ||

        CRLF ||

        CRLF ||

        CRLF ||

        V_TOT_MSG|| CRLF -- MESSAGE BODY

        );

        UTL_SMTP.QUIT(V_MAIL_CONN);

        EXCEPTION

        WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN

        RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL: '||SQLERRM);

        When others then

        dbms_output.put_line(sqlerrm);

END;
/

execute the procedure xx_sendmail

Thanks!!

Add responsibility through pl/sql

PL/SQL block to add responsibility

We have an API to do this : fnd_user_pkg.addresp

procedure AddResp(username       varchar2,
                  resp_app       varchar2,
                  resp_key       varchar2,
                  security_group varchar2,
                  description    varchar2,
                  start_date     date,
                  end_date       date)

--   Usage example in pl/sql
--   begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
--                              'STANDARD', 'DESCRIPTION', sysdate, null); end;
--   Input (Mandatory)
--   username:       User Name
--   resp_app:       Application Short Name
--   resp_key:       Responsibility Key
--   security_group: Security Group Key
--   description:    Description
--   start_date:     Start Date
--   end_date:       End Date

Take responsibility key value from

select responsibility_key from fnd_responsibility_vl
where responsibility_name = 'System Administrator' -- Query with your resp name to be added
;

Take resp_app from

select application_short_name from fnd_application
where application_short_name like 'SYS%';

CODE :

begin
fnd_user_pkg.addresp(
                        username       =>  v_user_name -- user_name from fnd_user table
                       ,resp_app       => 'SYSADMIN'
                        ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                        ,description    => 'Auto Assignment'
                         ,start_date     =>  SYSDATE
                        ,end_date       =>  SYSDATE + 365);
        COMMIT;       
        DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
        WHEN OTHERS
        THEN
        DBMS_OUTPUT.put_line (  ' Responsibility is not added due to '|| SQLCODE ||SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;
/


Thanks!!

PL/SQL Exception Handling - III

PL/SQL exception Handling-III

User defined exceptions

These are invoked using
-RAISE
-RAISE_APPLICATION_ERROR Procedure

--take a table and insert few rows
create table XX_TEST
(
ref_id number(4),
Name varchar2(20),
mobile number(10)
);


begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1002,'Jave',54321);
commit;
end;
/

create or replace procedure user_excp_proc
as
cnt number;
my_excp exception;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise my_excp;
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when my_excp then
dbms_output.put_line('No subject ');
when others then
dbms_output.put_line(sqlerrm);
end;
/

begin
user_excp_proc;
end;
/

user defined exceptions using
RAISE_APPLICATION_ERROR

Syntax :

raise_application_error(
error_number, message[, {TRUE | FALSE}]);
where error_number is a negative integer in the range -20000..-20999 and message
is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the
error is placed on the stack of previous errors. If the parameter is FALSE (the default),
the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package
DBMS_STANDARD, and as with package STANDARD, you need not qualify references to
it.

create or replace procedure user_excp_proc
as
cnt number;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise_application_error(-20001,'no subject');
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when others then
dbms_output.put_line('user exception..');
end;
/

begin
user_excp_proc;
end;
/

PL/SQL Exception Handling -II (Pragma Excpetion_init)

PL/SQL Exception Handling - II : Pragma Excpetion_init usage

Internally defined exceptions(ORA-n errors). These are raised implicitly.
However we have only few predefined error numbers associated with names as we discussed in
PL/SQL Exception Handling - I. The other predefined errors numbers are not associated with any predefined names.
In this we can assign names for those internal excpetions which have no names using
PRAGMA EXCEPTION_INIT (exception_name, error_code).


Let us take an example,


create table xx_test
(
id number(4),
name varchar2(20),
mobile number(10)
);

begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1002,'Jave',54321);
commit;
end;
/

Now we write a procedure which updates the name column of xx_test table with value higher than it's limit.
So obviously an excpetion will be raised. As we donot have such internal excpetion with a name.
catch it using OTHERS handler.

create or replace procedure xx_test_exception
as
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
run the procedure
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
-12899-ORA-12899: value too large for column "APPS"."XX_TEST"."NAME" (actual:
47, maximum: 20)
PL/SQL procedure successfully completed.


So now we use pragma exception_init and assign a name to the error no -12899
and now it will not go to others handler. See the modifed procedure
Note : to define the pragma, define in 2 steps

var_len_small excpetion; then
PRAGMA EXCEPTION_INIT(var_len_small, -12899);

create or replace procedure xx_test_exception
as
var_len_small EXCEPTION;
PRAGMA EXCEPTION_INIT(var_len_small, -12899);
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
when var_len_small then
dbms_output.put_line('trying to assign value which is higher than var limit');
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
see the output now,
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
trying to assign value which is higher than var limit
PL/SQL procedure successfully completed.

Exception Handling - III 


 

PL/SQL Exception Handling - I

PL/SQL Exception Handling - I

What is an exception :
In PL/SQL, an error condition is called an exception.

What happens if an exception raises?
When an exception is raised, the pl/sql program leads to abnormal termination which leads to
improper result sets.

To handle situations when an exception raises, we use exception handlers in
a planned manner.By that we can terminate program smoothly and also displaying and handling
the errors raised.

We have 2 types of exceptions
1. Internally Defined
2. User Defined

Internally Defined Exceptions:

An internal exception is raised automatically if your PL/SQL program violates a
database rule or exceeds a system-dependent limit. PL/SQL predefined some common ORA-n errors as internal exceptions.

Some common internal exceptions have predefined names,
for ex: NO_DATA_FOUND etc.,
Other predefined exceptions have error numbers but not given any names.
These can be associated with names using pragma EXCEPTION_INIT.






Example to handle internally predefined exception associated with
predefined name.

--take a table and insert few rows

create table XX_TEST
(
ref_id number(4),
Name varchar2(20),
mobile number(10)
);


begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1001,'Jave',54321);
commit;
end;
/

select * from xx_test;

create or replace procedure exp_hand_proc
as
v_mobile xx_test.mobile%type;
v_mobile1 xx_test.mobile%type;
begin
select mobile into v_mobile
from xx_test
where name='Oracle';
dbms_output.put_line('Mobile no :'||v_mobile);
select mobile into v_mobile1
from xx_test
where name='dotnet';
dbms_output.put_line('Mobile no :'||v_mobile1);
dbms_output.put_line('End of procedure');
exception
when NO_DATA_FOUND then
dbms_output.put_line('Ora Error :'||sqlerrm);
dbms_output.put_line('No data exists exception raised');
When OTHERS then
dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;
/

-- Run procedure and see how output displayed.
begin
exp_hand_proc;
end;
/

If an internal excpetion is raised, and if doesnot know the name of predefined excpetion
then use OTHERS handler in the exception block. And dispaly
error message and error number associated with excpetion using
sqlcode and sqlerrm.

Few others predefined excpetions are
DUP_VAL_ON_INDEX,INVALID_CURSOR,INVALID_NUMBER
LOGIN_DENIED,NOT_LOGGED_ON,PROGRAM_ERROR,
ROWTYPE_MISMATCH,SELF_IS_NULL,STORAGE_ERROR,
SUBSCRIPT_BEYOND_COUNT,SUBSCRIPT_OUTSIDE_LIMIT,
SYS_INVALID_ROWID,TIMEOUT_ON_RESOURCE,TOO_MANY_ROWS,
VALUE_ERROR,ZERO_DIVIDE

Will post examples on user defined excpetions in my next article.




Exception Handling -II Pragma Exception_init

Thanks!

Oracle DATA DICTIONARY Views-I

DATA DICTIONARY views:

We create many objects like TABLE, VIEW, SYNONYM, INDEX, SEQUENCE etc.,
To get information about the objects oracle provided us data dictionary views. It is like metadata.

USER_OBJECTS and DBA_OBJECTS holds data of all objects created.

We shall try to see usage of other data dict views:

CREATE TABLE students
(
    id    NUMBER(4)     CONSTRAINT students_pk PRIMARY KEY,
    name    VARCHAR2(30)  CONSTRAINT students_nn NOT NULL,
    email    VARCHAR2(40)  CONSTRAINT students_U  UNIQUE,
    phone    NUMBER(10)
);

begin
insert into students values (1,'TOM','tom@live.com',123456);
insert into students values (2,'JAI','jai@gmail.com',654321);
commit;
end;
/

-- To check constraints on a table use USER_CONSTRAINTS
select * from user_constraints
where table_name='STUDENTS';

Note : For primary key and unique key constraints index are created upon table creation.

--To check info about indexes
select * from user_indexes
where index_name='STUDENTS_PK';

CREATE TABLE course
(
     id number(4) constraint course_fk references students(id),
    course_name  varchar2(20) constraint course_chk check
                                     (course_name in ('eee','cse','ece','mech'))
);

begin
insert into course values (1,'eee');
insert into course values (2,'ece');
commit;
end;
/

select * from user_constraints
where table_name='COURSE';

Create a view on tables students and course                            

create or replace view stud_details
as
select s.id,s.name,c.course_name from students s,course c
where s.id=c.id;

-- To see query used to create view we use USER_VIEWS
select TEXT from user_views
where view_name='STUD_DETAILS';

CREATE OR REPLACE SYNONYM stud
for studnets;

select * from user_synonyms
where table_name='STUDENTS';

create sequence stud_id_seq
start with 100
increment by 1;

insert into students values (stud_id_seq.nextval,'adi','adi@live.com',789);

select * from user_sequences
where sequence_name='STUD_ID_SEQ'

Few Other Data Dictionary Views are :

USER_TRIGGERS
USER_ERRORS 

Query to retrieve active responsibilities assigned to a user

Input : User Name

select distinct fu.user_name "User Name", fu.description "User Full Name", frv.responsibility_name "Responsibility Name", fur.start_date "Resp Assignment Start Date", fur.end_date "Resp Assignment End Date" from fnd_responsibility_vl frv, fnd_user_resp_groups_direct fur, fnd_user fu where fur.responsibility_id=frv.responsibility_id and fu.user_id=fur.user_id AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE) AND SYSDATE BETWEEN frv.start_date AND NVL(frv.end_date,SYSDATE) and fu.user_name=:User_name order by frv.responsibility_name ;

Query to get all responsibilities to which a concurrent program is attached

Query to get all responsibilities to which a concurrent program is attached
Input : Program Name

select distinct
frl.responsibility_name "Responsibility Name",
fcp.user_concurrent_program_name "Concurrent Program Name",
frg.request_group_name "Request Group Name"
from
fnd_responsibility_vl frl
,fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcp
where
frg.request_group_id=frl.request_group_id
and frg.request_group_id=frgu.request_group_id
and frgu.request_unit_id=fcp.concurrent_program_id
and fcp.user_concurrent_program_name=:Program --conc program
order by frl.responsibility_name
;

Workflow Query : To get Action History details

Workflow Query to get action history details:

Inputs Needed :

WF Item Type
Ex : 'POAPPR','PABUDWF' etc.,

WF Item Key

Query :

SELECT
  TO_CHAR(ias.begin_date,'DD-MON-RR HH24:MI:SS') begin_date,
  TO_CHAR(ias.end_date,'DD-MON-RR HH24:MI:SS') end_date,
  ap.display_name
  ||'/'
  ||pa.instance_label Activity   ,
  ias.activity_status Status     ,
  ias.activity_result_code Result,
  ias.assigned_user assigned_user,
  ias.notification_id NID        ,
  ntf.status "Status"            ,
  ias.action                     ,
  ias.performed_by               ,
  ias.due_date                   ,
  ias.ERROR_NAME                 ,
  ias.ERROR_MESSAGE
FROM apps.wf_item_activity_statuses ias,
  apps.wf_process_activities pa           ,
  apps.wf_activities ac                   ,
  apps.wf_activities_vl ap                ,
  apps.wf_items i                         ,
  apps.wf_notifications ntf
  WHERE ias.item_type      = 'PABUDWF' --  Enter WF Item TypeAND ias.item_key           = '21215'   --  Enter WF Item KeyAND ias.process_activity   = pa.instance_id
AND pa.activity_name       = ac.name
AND pa.activity_item_type  = ac.item_type
AND pa.process_name        = ap.name
AND pa.process_item_type   = ap.item_type
AND pa.process_version     = ap.version
AND i.item_type            = ias.item_type
AND i.item_key             = ias.item_key
AND i.begin_date          >= ac.begin_date
AND i.begin_date           < NVL(ac.end_date, i.begin_date+1)
AND ntf.notification_id(+) = ias.notification_id
ORDER BY ias.begin_date,
         ias.execution_time;

Creating Public and Private subprograms in a PL/SQL Package

Package :
Is a schema object that logically groups related pl/sql types,variables and subprograms.

Subprograms defined in package spec becomes public.
Subprograms defined in package body becomes private, are accessible to package members only.

See below package - XX_EMP_DETAILS

It has a procedure define in spec - DISPLAY_EMP_DETAILS
It is a public object. It can be referenced from outside the package like
XX_EMP_DETAILS.DISPLAY_EMP_DETAILS

If we see package body, a function get_yearly_salary is defined.
It becomes private.
It can't be accessed from outside the package.


See the code details below :

CREATE OR REPLACE PACKAGE XX_EMP_DETAILS
AS
--Public Methods
PROCEDURE DISPLAY_EMP_DETAILS(p_id employees.employee_id%type);
END XX_EMP_DETAILS;
/

CREATE OR REPLACE PACKAGE BODY XX_EMP_DETAILS
AS

--Defining a private method
FUNCTION GET_YEARLY_SALARY(p_id employees.employee_id%type    )
RETURN NUMBER
AS
mon_sal number;
BEGIN
select salary*12
into mon_sal
from employees
where employee_id=p_id;
return mon_sal;
end get_yearly_salary;

PROCEDURE DISPLAY_EMP_DETAILS(p_id employees.employee_id%type)
IS
v_first_name varchar2(100);
v_last_name varchar2(100);
v_sal number;
v_dept employees.department_id%type;
BEGIN
SELECT
      first_name,
      last_name,
      department_id
into
      v_first_name,
      v_last_name,
      v_dept
from  employees
where employee_id = p_id;
--get yearly salary by calling private function get_yearly_salary
v_sal:=get_yearly_salary(p_id);
DBMS_OUTPUT.PUT_LINE('Employee Details : ');
DBMS_OUTPUT.PUT_LINE(rpad('FIRST NAME',20)||' '||rpad('LAST NAME',20)||' '
                     ||Lpad('YEARLY SALARY',10)||' '||Lpad('DEPARTMENT',5));
DBMS_OUTPUT.PUT_LINE(rpad(v_first_name,20)||' '||rpad(v_last_name,20)||' '
                     ||Lpad(v_sal,10)||' '||Lpad(v_dept,5));
DBMS_OUTPUT.PUT_LINE('END of Employee Details');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Does not exist');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END DISPLAY_EMP_DETAILS;
end XX_EMP_DETAILS;
/

Now call public procedure and see. Here private function is
accessed in the procedure.

begin
XX_EMP_DETAILS.DISPLAY_EMP_DETAILS(100);
end;
/

Now call private function and check.
It throws an error 

If we see procedure call, as it is a package member, it can access private subprograms.

Limitations in calling a Stored Function

To invoke/call a function from SQL statements/subprograms it should obey :

1. When called from a SELECT statement,function cannot modify any tables.

2. When invoked from a SELECT,INSERT,UPDATE or DELETE constructs,
function cannot query/modify any tables modified by that statement.

3. When invoked from SELECT,INSERT,UPDATE or DELETE statements, the function cannot
execute transaction control statements/DDL's.

To check point # 1, lets try do what is mentioned :

1. Create a table 'xx_func_dml'

create table xx_func_dml
(
id number(4),
name varchar2(20)
)

begin
insert into xx_func_dml values (1,'NAG');
insert into xx_func_dml values (2,'SRI');
commit;
end;
/

2.create a function xx_func_call, which updates a table xx_func_dml.
create or replace function xx_func_call
return number
is
begin
dbms_output.put_line('In func to execute a dml statement on
                      table. Also func is called in select query');
update xx_func_dml
set name='TOM'
where id=2;
commit;
return 1;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/

Call function normally and see : 

declare
i number;
begin
i:=xx_func_call;
dbms_output.put_line(i);
commit;
end;
/

Now call function from a select query and check :

Select xx_func_call from dual;