What is the difference between explicit and implicit cursors in Oracle

Question

I am a bit rusty on my cursor lingo in PL SQL  Anyone know this

User · Answer

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

User · Answer

From a performance point of view  Implicit cursors are faster  Let s compare the performance between an explicit and implicit cursor  SQL gt  DECLARE   2    l loops  NUMBER    100000    3    l dummy  dual dummy TYPE    4    l start  NUMBER    5    -- explicit cursor declaration   6    CURSOR c dual IS   7      SELECT dummy   8      FROM   dual    9  BEGIN  10    l start    DBMS UTILITY get time   11    -- explicitly open  fetch and close the cursor  12    FOR i IN 1    l loops LOOP  13      OPEN  c dual   14      FETCH c dual  15      INTO  l dummy   16      CLOSE c dual   17    END LOOP   18  19    DBMS OUTPUT put line  Explicit        20                          DBMS UTILITY get time - l start       hsecs     21  22    l start    DBMS UTILITY get time   23    -- implicit cursor for loop  24    FOR i IN 1    l loops LOOP  25      SELECT dummy  26      INTO   l dummy  27      FROM   dual   28    END LOOP   29  30    DBMS OUTPUT put line  Implicit        31                          DBMS UTILITY get time - l start       hsecs     32  END   33    Explicit  332 hsecs Implicit  176 hsecs  PL SQL procedure successfully completed   So  a significant difference is clearly visible  Implicit cursor is much faster than an explicit cursor  More examples here

User · Answer

These days implicit cursors are more efficient than explicit cursors   http   www oracle com technology oramag oracle 04-sep o54plsql html  http   asktom oracle com pls asktom f p 100 11 0    P11 QUESTION ID 1205168148688

User · Answer

Every SQL statement executed by the Oracle database has a cursor associated with it  which is a private work area to store processing information  Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements   You can declare and use Explicit cursors to name the private work area  and access its stored information in your program block

User · Answer

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

User · Answer

In PL SQL  A cursor is a pointer to this context area  It contains all the information needed for processing the statement    Implicit Cursors  Implicit cursors are automatically created by Oracle whenever an SQL statement is executed  when there is no explicit cursor for the statement  Programmers cannot control the implicit cursors and the information in it   Explicit Cursors  Explicit cursors are programmer-defined cursors for gaining more control over the context area  An explicit cursor should be defined in the declaration section of the PL SQL Block  It is created on a SELECT Statement which returns more than one row   The syntax for creating an explicit cursor is   CURSOR cursor name IS select statement

User · Answer

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

User · Answer

With explicit cursors  you have complete control over how to access information in the database  You decide when to OPEN the cursor  when to FETCH records from the cursor  and therefore from the table or tables in the SELECT statement of the cursor  how many records to fetch  and when to CLOSE the cursor  Information about the current state of your cursor is available through examination of the cursor attributes   See http   www unix com ua orelly oracle prog2 ch06 03 htm for details

User · Answer

Implicit cursor returns only one record and are called automatically  However  explicit cursors are called manually and can return more than one record

User · Answer

Explicit     cursor foo is select   from blah  begin    open fetch exit when close cursor yada yada yada  don t use them  use implicit  cursor foo is select   from blah   for n in foo loop    x   n some column end loop  I think you can even do this  for n in  select   from blah  loop     Stick to implicit  they close themselves  they are more readable  they make life easy

User · Answer

As stated in other answers  implicit cursors are easier to use and less error-prone    And Implicit vs  Explicit Cursors in Oracle PL SQL shows that implicit cursors are up to two times faster than explicit ones too   It s strange that no one had yet mentioned Implicit FOR LOOP Cursor   begin   for cur in       select t id from parent trx pt inner join trx t on pt nested id   t id     where t started at  gt  sysdate - 31 and t finished at is null and t extended code is null       loop     update trx set finished at sysdate  extended code   -1 where id   cur id      update parent trx set result code   -1 where nested id   cur id    end loop cur  end    Another example on SO  PL SQL FOR LOOP IMPLICIT CURSOR   It s way more shorter than explicit form   This also provides a nice workaround for updating multiple tables from CTE

User · Answer

Implicit cursors require anonymous buffer memory    Explicit cursors can be executed again and again by using their name They are stored in user defined memory space rather than being stored in an anonymous buffer memory and hence can be easily accessed afterwards

User · Answer

An explicit cursor is one you declare  like   CURSOR my cursor IS   SELECT table name FROM USER TABLES   An implicit cursor is one created to support any in-line SQL you write  either static or dynamic

User · Answer

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

User · Answer

With explicit cursors  you have complete control over how to access information in the database  You decide when to OPEN the cursor  when to FETCH records from the cursor  and therefore from the table or tables in the SELECT statement of the cursor  how many records to fetch  and when to CLOSE the cursor  Information about the current state of your cursor is available through examination of the cursor attributes   See http   www unix com ua orelly oracle prog2 ch06 03 htm for details

User · Answer

With explicit cursors  you have complete control over how to access information in the database  You decide when to OPEN the cursor  when to FETCH records from the cursor  and therefore from the table or tables in the SELECT statement of the cursor  how many records to fetch  and when to CLOSE the cursor  Information about the current state of your cursor is available through examination of the cursor attributes   See http   www unix com ua orelly oracle prog2 ch06 03 htm for details

User · Answer

Explicit     cursor foo is select   from blah  begin    open fetch exit when close cursor yada yada yada  don t use them  use implicit  cursor foo is select   from blah   for n in foo loop    x   n some column end loop  I think you can even do this  for n in  select   from blah  loop     Stick to implicit  they close themselves  they are more readable  they make life easy

User · Answer

In answer to the first question  Straight from the Oracle documentation     A cursor is a pointer to a private SQL   area that stores information about   processing a specific SELECT or DML   statement

User · Answer

Google is your friend   http   docstore mik ua orelly oracle prog2 ch06 03 htm     PL SQL issues an implicit cursor   whenever you execute a SQL statement   directly in your code  as long as that   code does not employ an explicit   cursor  It is called an  implicit    cursor because you  the developer  do   not explicitly declare a cursor for   the SQL statement       An explicit cursor is a SELECT   statement that is explicitly defined   in the declaration section of your   code and  in the process  assigned a   name  There is no such thing as an   explicit cursor for UPDATE  DELETE    and INSERT statements

User · Answer

An explicit cursor is one you declare  like   CURSOR my cursor IS   SELECT table name FROM USER TABLES   An implicit cursor is one created to support any in-line SQL you write  either static or dynamic

User · Answer

Every SQL statement executed by the Oracle database has a cursor associated with it  which is a private work area to store processing information  Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements   You can declare and use Explicit cursors to name the private work area  and access its stored information in your program block

User · Answer

From a performance point of view  Implicit cursors are faster  Let s compare the performance between an explicit and implicit cursor  SQL gt  DECLARE   2    l loops  NUMBER    100000    3    l dummy  dual dummy TYPE    4    l start  NUMBER    5    -- explicit cursor declaration   6    CURSOR c dual IS   7      SELECT dummy   8      FROM   dual    9  BEGIN  10    l start    DBMS UTILITY get time   11    -- explicitly open  fetch and close the cursor  12    FOR i IN 1    l loops LOOP  13      OPEN  c dual   14      FETCH c dual  15      INTO  l dummy   16      CLOSE c dual   17    END LOOP   18  19    DBMS OUTPUT put line  Explicit        20                          DBMS UTILITY get time - l start       hsecs     21  22    l start    DBMS UTILITY get time   23    -- implicit cursor for loop  24    FOR i IN 1    l loops LOOP  25      SELECT dummy  26      INTO   l dummy  27      FROM   dual   28    END LOOP   29  30    DBMS OUTPUT put line  Implicit        31                          DBMS UTILITY get time - l start       hsecs     32  END   33    Explicit  332 hsecs Implicit  176 hsecs  PL SQL procedure successfully completed   So  a significant difference is clearly visible  Implicit cursor is much faster than an explicit cursor  More examples here

User · Answer

An explicit cursor is one you declare  like   CURSOR my cursor IS   SELECT table name FROM USER TABLES   An implicit cursor is one created to support any in-line SQL you write  either static or dynamic

User · Answer

Implicit cursor returns only one record and are called automatically  However  explicit cursors are called manually and can return more than one record

User · Answer

Google is your friend   http   docstore mik ua orelly oracle prog2 ch06 03 htm     PL SQL issues an implicit cursor   whenever you execute a SQL statement   directly in your code  as long as that   code does not employ an explicit   cursor  It is called an  implicit    cursor because you  the developer  do   not explicitly declare a cursor for   the SQL statement       An explicit cursor is a SELECT   statement that is explicitly defined   in the declaration section of your   code and  in the process  assigned a   name  There is no such thing as an   explicit cursor for UPDATE  DELETE    and INSERT statements

User · Answer

1 CURSOR  When PLSQL issues sql statements it creates private work area    to parse  amp  execute the sql statement is called cursor   2 IMPLICIT  When any PL SQLexecutable block issues sql statement     PL SQL creates implicit cursor and manages automatically means    implcit open  amp  close takes place  It used when sql statement return    only one row It has 4 attributes SQL ROWCOUNT  SQL FOUND     SQL NOTFOUND  SQL ISOPEN   3 EXPLICIT  It is created  amp  managed by the programmer  It needs every    time explicit open fetch  amp  close  It is used when sql statement    returns more than one row  It has also 4 attributes    CUR NAME ROWCOUNT  CUR NAME FOUND  CUR NAME NOTFOUND      CUR NAME ISOPEN  It process several rows by using loop      The programmer can pass the parameter too to explicit cursor    Example  Explicit Cursor    nbsp   declare     cursor emp cursor     is     select id name salary dept id     from employees      v id employees id type      v name employees name type      v salary employees salary type      v dept id employees dept id type      begin     open emp cursor      loop     fetch emp cursor into v id v name v salary v dept id      exit when emp cursor notfound     dbms output put line v id        v name        v salary       v dept id       end loop                         close emp cursor      end

User · Answer

A cursor is a SELECTed window on an Oracle table  this means a group of records present in an Oracle table  and satisfying certain conditions   A cursor can SELECT all the content of a table  too   With a cursor you can manipulate Oracle columns  aliasing them in the result   An example of implicit cursor is the following   BEGIN    DECLARE       CURSOR C1       IS          SELECT DROPPED CALLS FROM ALARM UMTS         C1 REC   C1 ROWTYPE     BEGIN       FOR C1 REC IN C1       LOOP          DBMS OUTPUT PUT LINE   DROPPED CALLS       C1 REC DROPPED CALLS         END LOOP     END  END      With FOR     LOOP    END LOOP you open and close the cursor authomatically  when the records of the cursor have been all analyzed   An example of explicit cursor is the following   BEGIN    DECLARE       CURSOR C1       IS          SELECT DROPPED CALLS FROM ALARM UMTS         C1 REC   C1 ROWTYPE     BEGIN       OPEN c1         LOOP          FETCH c1 INTO c1 rec            EXIT WHEN c1 NOTFOUND            DBMS OUTPUT PUT LINE   DROPPED CALLS       C1 REC DROPPED CALLS         END LOOP         CLOSE c1     END  END      In the explicit cursor you open and close the cursor in an explicit way  checking the presence of records and stating an exit condition

User · Answer

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

User · Answer

In PL SQL  A cursor is a pointer to this context area  It contains all the information needed for processing the statement    Implicit Cursors  Implicit cursors are automatically created by Oracle whenever an SQL statement is executed  when there is no explicit cursor for the statement  Programmers cannot control the implicit cursors and the information in it   Explicit Cursors  Explicit cursors are programmer-defined cursors for gaining more control over the context area  An explicit cursor should be defined in the declaration section of the PL SQL Block  It is created on a SELECT Statement which returns more than one row   The syntax for creating an explicit cursor is   CURSOR cursor name IS select statement

User · Answer

Implicit cursors require anonymous buffer memory    Explicit cursors can be executed again and again by using their name They are stored in user defined memory space rather than being stored in an anonymous buffer memory and hence can be easily accessed afterwards

User · Answer

These days implicit cursors are more efficient than explicit cursors   http   www oracle com technology oramag oracle 04-sep o54plsql html  http   asktom oracle com pls asktom f p 100 11 0    P11 QUESTION ID 1205168148688

User · Answer

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

User · Answer

Every SQL statement executed by the Oracle database has a cursor associated with it  which is a private work area to store processing information  Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements   You can declare and use Explicit cursors to name the private work area  and access its stored information in your program block

User · Answer

These days implicit cursors are more efficient than explicit cursors   http   www oracle com technology oramag oracle 04-sep o54plsql html  http   asktom oracle com pls asktom f p 100 11 0    P11 QUESTION ID 1205168148688

User · Answer

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

User · Answer

With explicit cursors  you have complete control over how to access information in the database  You decide when to OPEN the cursor  when to FETCH records from the cursor  and therefore from the table or tables in the SELECT statement of the cursor  how many records to fetch  and when to CLOSE the cursor  Information about the current state of your cursor is available through examination of the cursor attributes   See http   www unix com ua orelly oracle prog2 ch06 03 htm for details

User · Answer

As stated in other answers  implicit cursors are easier to use and less error-prone    And Implicit vs  Explicit Cursors in Oracle PL SQL shows that implicit cursors are up to two times faster than explicit ones too   It s strange that no one had yet mentioned Implicit FOR LOOP Cursor   begin   for cur in       select t id from parent trx pt inner join trx t on pt nested id   t id     where t started at  gt  sysdate - 31 and t finished at is null and t extended code is null       loop     update trx set finished at sysdate  extended code   -1 where id   cur id      update parent trx set result code   -1 where nested id   cur id    end loop cur  end    Another example on SO  PL SQL FOR LOOP IMPLICIT CURSOR   It s way more shorter than explicit form   This also provides a nice workaround for updating multiple tables from CTE

User · Answer

Explicit     cursor foo is select   from blah  begin    open fetch exit when close cursor yada yada yada  don t use them  use implicit  cursor foo is select   from blah   for n in foo loop    x   n some column end loop  I think you can even do this  for n in  select   from blah  loop     Stick to implicit  they close themselves  they are more readable  they make life easy

User · Answer

These days implicit cursors are more efficient than explicit cursors   http   www oracle com technology oramag oracle 04-sep o54plsql html  http   asktom oracle com pls asktom f p 100 11 0    P11 QUESTION ID 1205168148688

User · Answer

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

User · Answer

An explicit cursor is one you declare  like   CURSOR my cursor IS   SELECT table name FROM USER TABLES   An implicit cursor is one created to support any in-line SQL you write  either static or dynamic

User · Answer

Every SQL statement executed by the Oracle database has a cursor associated with it  which is a private work area to store processing information  Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements   You can declare and use Explicit cursors to name the private work area  and access its stored information in your program block

User · Answer

1 CURSOR  When PLSQL issues sql statements it creates private work area    to parse  amp  execute the sql statement is called cursor   2 IMPLICIT  When any PL SQLexecutable block issues sql statement     PL SQL creates implicit cursor and manages automatically means    implcit open  amp  close takes place  It used when sql statement return    only one row It has 4 attributes SQL ROWCOUNT  SQL FOUND     SQL NOTFOUND  SQL ISOPEN   3 EXPLICIT  It is created  amp  managed by the programmer  It needs every    time explicit open fetch  amp  close  It is used when sql statement    returns more than one row  It has also 4 attributes    CUR NAME ROWCOUNT  CUR NAME FOUND  CUR NAME NOTFOUND      CUR NAME ISOPEN  It process several rows by using loop      The programmer can pass the parameter too to explicit cursor    Example  Explicit Cursor    nbsp   declare     cursor emp cursor     is     select id name salary dept id     from employees      v id employees id type      v name employees name type      v salary employees salary type      v dept id employees dept id type      begin     open emp cursor      loop     fetch emp cursor into v id v name v salary v dept id      exit when emp cursor notfound     dbms output put line v id        v name        v salary       v dept id       end loop                         close emp cursor      end

User · Answer

A cursor is a SELECTed window on an Oracle table  this means a group of records present in an Oracle table  and satisfying certain conditions   A cursor can SELECT all the content of a table  too   With a cursor you can manipulate Oracle columns  aliasing them in the result   An example of implicit cursor is the following   BEGIN    DECLARE       CURSOR C1       IS          SELECT DROPPED CALLS FROM ALARM UMTS         C1 REC   C1 ROWTYPE     BEGIN       FOR C1 REC IN C1       LOOP          DBMS OUTPUT PUT LINE   DROPPED CALLS       C1 REC DROPPED CALLS         END LOOP     END  END      With FOR     LOOP    END LOOP you open and close the cursor authomatically  when the records of the cursor have been all analyzed   An example of explicit cursor is the following   BEGIN    DECLARE       CURSOR C1       IS          SELECT DROPPED CALLS FROM ALARM UMTS         C1 REC   C1 ROWTYPE     BEGIN       OPEN c1         LOOP          FETCH c1 INTO c1 rec            EXIT WHEN c1 NOTFOUND            DBMS OUTPUT PUT LINE   DROPPED CALLS       C1 REC DROPPED CALLS         END LOOP         CLOSE c1     END  END      In the explicit cursor you open and close the cursor in an explicit way  checking the presence of records and stating an exit condition

User · Answer

Explicit     cursor foo is select   from blah  begin    open fetch exit when close cursor yada yada yada  don t use them  use implicit  cursor foo is select   from blah   for n in foo loop    x   n some column end loop  I think you can even do this  for n in  select   from blah  loop     Stick to implicit  they close themselves  they are more readable  they make life easy

User · Answer

In answer to the first question  Straight from the Oracle documentation     A cursor is a pointer to a private SQL   area that stores information about   processing a specific SELECT or DML   statement

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

Examples related to oracle

Examples related to plsql