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.

x NUMBER := 5;
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’);
dbms_output.put_line(‘Can”t tell if x and y are equal or not…’);
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.

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
dbms_output.put_line(‘Can”t tell if two NULLs are equal’);
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;
high := y; | high := x;

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:

null_string VARCHAR2(80) := TO_CHAR(”);
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != ”);
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:


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:

the_manager VARCHAR2(40);
name employees.last_name%TYPE;
— 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);
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:

the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
— 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);
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:

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);
dbms_output.put_line(‘Old string = ‘ || old_string);
dbms_output.put_line(‘New string = ‘ || new_string);
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:

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);
dbms_output.put_line(‘Dashed name = ‘ || dashed);
dbms_output.put_line(‘Dashes removed = ‘ || name);
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.

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

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

The default operator for comparison is ‘=’,
for e.g select deptno,decode

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’

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


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 /
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.

Components of Oracle Workflow :

Workflow Builder
Workflow Engine
Workflow Definitions Loader
Notification Systems
Workflow Monitor
Notification systems
Business Event System’s
Workflow XML Loader

Directory Service’s.

Workflow Builder :Work Flow builder is a Graphical interface to create and modify a business process with simple drag and drop operations.
It has two components –

* Navigator Window
* Process Window
A workflow builder would be used by a person to design and modify a workflow. It is more of a designer’s tool rather than an end-user’s tool.

Workflow Engine : The Workflow Engine embedded in the Oracle8 server monitors workflow states and coordinates the routing of activities for a process.Changes in workflow state, such as the completion of workflow activities, are signaled to the engine via a PL/SQL API or a Java API. Based on flexibly–defined workflow rules, the engine determines which activities are eligible to run, and then runs them. The Workflow Engine supports sophisticated workflow rules, including looping, branching,parallel flows, and sub-flows.

Workflow Definitions Loader :The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations.It allows opening and saving workflow definitions in both a database and file.

Notification System : Each notification includes a message that contains all the information a user needs to make a decision.

Workflow Monitor : The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process.Users can get a graphical depiction of their work item status. It also displays a separate status summary for the work item, the process, and each activity in the process.

Setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File – > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by
a.owner, a.name;