What is the difference between explicit and implicit cursors in Oracle?

An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from

• inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)
• vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)

Example

SELECT col INTO var FROM table WHERE something;

An explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others.

Example

DECLARE   
  CURSOR cur IS SELECT col FROM table WHERE something; 
BEGIN
  OPEN cur;
  FETCH cur INTO var;
  CLOSE cur;
END;

An explicit cursor is defined as such in a declaration block:

DECLARE 
CURSOR cur IS 
  SELECT columns FROM table WHERE condition;
BEGIN
...

an implicit cursor is implented directly in a code block:

...
BEGIN
   SELECT columns INTO variables FROM table where condition;
END;
...