Pre-requisites :
Step1: Data Definition and Template to be created
Step2: Concurrent program needs to be created

Steps To Create the PL/SQL package:

1. Initialize the  Session Specific variable using fnd_global.APPS_INITIALIZE
2. Set The BI publisher report layout Before submitting the concurrent program
3. Submit the Concurrent Program

Code: (Tested in R12.1.1 )

DECLARE
l_user_id              fnd_user.user_id%TYPE;
l_resp_id              fnd_responsibility.responsibility_id%TYPE;
l_resp_appl_id         fnd_application.application_id%TYPE;
l_set_layout           boolean;
l_request_id           NUMBER;
l_phase                VARCHAR2 (100);
l_status               VARCHAR2 (100);
l_dev_phase            VARCHAR2 (100);
l_dev_status           VARCHAR2 (100);
l_wait_for_request     boolean := FALSE;
l_get_request_status   boolean := FALSE;
Output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
BEGIN
l_request_id := NULL;


— Get the Apps Intilization Variables

SELECT   fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO   l_user_id, l_resp_id, l_resp_appl_id
FROM   fnd_user fnd, fnd_responsibility_tl fresp
WHERE   fnd.user_name = ‘OEAG’
AND fresp.responsibility_name = ‘Custom XML Reports’;


–Initialize the Apps Variables

fnd_global.APPS_INITIALIZE (user_id        => l_user_id,
resp_id        => l_resp_id,
resp_appl_id   => l_resp_appl_id);

COMMIT;


— Set the Layout  for BI Publisher Report

l_set_layout :=
fnd_request.add_layout (template_appl_name   => ‘XXERP’,
template_code        => ‘XXORACLEERPAPPSGUIDE’,
–Data Template Code
template_language    => ‘en’,
template_territory   => ‘US’,
output_format        => ‘PDF’);

IF l_set_layout
THEN
— Submit the Request

l_request_id :=
fnd_request.submit_request (application   => ‘XXERP’,
program       => ‘XXOEAG_PG’,
description   => ”,
start_time    => SYSDATE,
sub_request   => FALSE,
argument1     => l_person_id);

COMMIT;

IF l_request_id > 0
THEN

–waits for the request completion

l_wait_for_request :=
fnd_concurrent.wait_for_request (request_id   => l_request_id,
interval     => 60,
max_wait     => 0,
phase        => l_phase,
status       => l_status,
dev_phase    => l_dev_phase,
dev_status   => l_dev_status,
MESSAGE      => l_messase);

COMMIT;


— Get the Request Completion Status.

l_get_request_status :=
fnd_concurrent.get_request_status (
request_id       => l_request_id,
appl_shortname   => NULL,
program          => NULL,
phase            => l_phase,
status           => l_status,
dev_phase        => l_dev_phase,
dev_status       => l_dev_status,
MESSAGE          => l_messase
);


–Check the status if It IS completed Normal Or Not

IF UPPER (l_dev_phase) != ‘COMPLETED’
AND UPPER (l_dev_status) != ‘NORMAL’
THEN
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE Output_layout_failed;
END IF;

p_request_id := l_request_id;
EXCEPTION
WHEN Output_layout_failed
THEN
DBMS_OUTPUT.put_line (‘Out put Layout failed’);
WHEN request_submission_failed
THEN
DBMS_OUTPUT.put_line (‘Concurrent request submission failed’);
WHEN request_completion_abnormal
THEN
DBMS_OUTPUT.put_line (
‘Submitted request completed with error’ || l_request_id
);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR:’ || SUBSTR (SQLERRM, 0, 240));
END;

/

You can Create this as PL/SQL Procedure and register into Concurrent Program also.

For each Concurrent program we must use mandatory parameters : ERRBUF and RETCODE.

ERRBUF: It return the error message. 
For you program if you get any error in exception block you can assign the error message to this parameter. 
This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf.

RETCODE: This parameter returns the status of the concurrent program.
0- Success –Completed
1- Warning — Yellow color
2- Error — Red

These parameters we call as a first parameters for the program.

Ex:
Create procedure  CProgram(ERRBUF out varchar2, RETCODE  out varchar2, person_id in NUmber)
as
begin
<Declaration Goes  Here>
begin
<Code Block1 Here>

exception
when no_data_found then
retcode := 1;
errbuf:= ‘RetCode : Warning’;
end;

<Code Block2 Here>

retcode:= 0;
commit;
exception
when others then

retcode := 2;

errbuf:= ‘errbuf:= ‘RetCode : Error’;

end;
The main classification is Oracle Defined and User defined Exceptions

User Defined:

User defined exceptions are defined in many ways.

a.New user defined Exceptions can be created using existing Oracle defined Exceptions

b.Entirely new Exceptions can be according to a users need. 
Eg: Raise an exception if employee salry should not be negative value.

There are mainly 3 ways to use User Defined Exceptions

A. RAISE EXCEPTION

declare
exc_user Exception; –declare exception
begin
–code–
exception when others then
raise exc_user;
–exception raised
exception
when exc_user then
–handler for exc_user. exception handled
when others then
–handler for others
end;

B. RAISE_APPLICATION_ERROR

declare
exc_user Exception; –declare exception
begin
if (<logic>) then
RAISE_APPLICATION_ERROR(-20001, exc_user);
–code greater than -20000
exception
when exc_user then
 –handled when error occurs with the specified Oracle Code
when others then
–handler for others

C. PRAGMA EXCEPTION_INIT

declare
exc_user Exception; –declare exception
PRAGMA EXCEPTION_INIT(exc_user, -<oracle_error_code>); 
— Oracle code with ‘-‘sign
begin
–code–
exception
when exc_user then
–handler for exc_user –handled when error occurs with the specified Oracle Code
when others then
–handler for others

Oracle defined:

There are many built in exceptions which we can use. Most commonly used ones are:

a.NO_DATA_FOUND
b.TOO_MANY_ROWS_FOUND
c.ZERO_DIVIDE
d.CURSOR_ALREADY_OPEN
e.INVALID_CURSOR
f.DUP_VALUE_ON_INDEX
g.VALUE_ERROR
h.INVALID_NUMBER

Difference between Value Error and Invalid Number
See below are the examples:

Eg1:
SQL> select to_number (‘a’)
  from dual

Error :ORA-01722: invalid number

Eg2:

SQL>   declare
       n number;
   begin
      n := ‘a’;
    exception
     when value_error
    then
     dbms_output.put_line (‘Value Error’);
   end;

 
Error: Value Error

Eg3:

SQL> declare
     n number;
    begin
         select ‘a’ into n
         from dual ;
     exception
     when value_error then
     dbms_output.put_line (‘Value Error’);
    end;


Error: Value Error

Eg4:
SQL> declare
     n number;
    begin
        select to_number(‘a’) into n
        from dual;
    exception
    when value_error  then
    dbms_output.put_line (‘Value Error’);
    when invalid_number then
    dbms_output.put_line (‘Invalid Number’);
 end;

Error:Invalid Number
Lot of time we got this type of senerio When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

Comparisons involving nulls always yield NULL

Applying the logical operator NOT to a null yields NULL

In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN — yields NULL, not TRUE
dbms_output.put_line(‘x != y’); — not executed
ELSIF x = y THEN — also yields NULL
dbms_output.put_line(‘x = y’);
ELSE
dbms_output.put_line(‘Can”t tell if x and y are equal or not…’);
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a = b’); — not executed
ELSIF a != b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a != b’); — not executed
ELSE
dbms_output.put_line(‘Can”t tell if two NULLs are equal’);
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.

NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR(”);
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != ”);
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN …

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

‘apple’ || NULL || NULL || ‘sauce’

returns the following value:

‘applesauce’

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to DECODE. In this case, manager_id is null
— and the DECODE function returns ‘nobody’.
SELECT DECODE(manager_id, NULL, ‘nobody’, ‘somebody’), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by ‘ || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to NVL. In this case, manager_id is null
— and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by employee #’ || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := ‘Apples and oranges’;
my_string string_type%TYPE := ‘more apples’;
— NULL is a valid argument to REPLACE, but does not match
— anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line(‘Old string = ‘ || old_string);
dbms_output.put_line(‘New string = ‘ || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := ‘Gold-i-locks’;
— When the substitution text for REPLACE is NULL,
— the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, ‘-‘, NULL);
BEGIN
dbms_output.put_line(‘Dashed name = ‘ || dashed);
dbms_output.put_line(‘Dashes removed = ‘ || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.
Posted by Sairamgoud at 3:05 PM 0 comments
PL/SQL -Advantages
Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.

Decode
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case
1.It’s an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null

Decode
The default operator for comparison is ‘=’,
for e.g select deptno,decode
(deptno,10,’dept10′,20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than ‘=’,
for e.g
select deptno,
case when deptno=10 and sal>1000 then ‘dept10’
else
‘dept20’

Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,’CLERK’,’executive’,job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job=’CLERK’ then ‘executive’ else ‘no’ end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.

There is one more Important difference between CASE and DECODE

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

Eg:-

Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(‘ The number = ‘||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := ‘ONE’;
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,’ONE’,1,0));
3 End;
4 /
pro_01(Decode(:a,’ONE’,1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column ‘DECODE’ may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

SQL> Begin
2 pro_01(case :a when ‘ONE’ then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.