Cursor reuse in PL/SQL

==> Lower case or upper case makes difference !

nbk9lsj@GEN3QB> ho cat cursor_in_sharedpool_8i.sql
-- pre Oracle 10gR1
colum sql_text format a30
SELECT hash_value,
address,
sql_text,
parse_calls,
executions
FROM v$sql
WHERE INSTR(UPPER(sql_text),'ALL_OBJECTS') > 0
AND INSTR(UPPER(sql_text),'SQL_TEXT') = 0
AND command_type = 3;

nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql

HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
2893531678 D4C259A4 select count(*) from all_objec 5
5
ts

1481688868 D4BDECC8 select count(*) from All_objec 3
3
ts

nbk9lsj@GEN3QB> select count(*) from all_objects;

COUNT(*)
----------
11306

nbk9lsj@GEN3QB> select count(*) from All_objects;

COUNT(*)
----------
11306

nbk9lsj@GEN3QB> select count(*) from all_Objects;

COUNT(*)
----------
11306

nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql

HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
2893531678 D4C259A4 select count(*) from all_objec 6
6
ts

3230163941 D4BB7348 select count(*) from all_Objec 1
1
ts

1481688868 D4BDECC8 select count(*) from All_objec 4
4
ts

==> Cursor in PL/SQL is reused. PL/SQL compiler makes it all upper
case.

nbk9lsj@GEN3QB> ho cat simple_demo_pro.sql
CREATE OR REPLACE PROCEDURE simple_demo AS
CURSOR one IS
select count(*) from all_objects;
CURSOR two IS
select count(*) from All_objects;
CURSOR three IS
select count(*) from all_Objects;

v_count number;
BEGIN
-- open and close correct cursor
OPEN one;
FETCH one INTO v_count;
CLOSE one;
OPEN two;
FETCH two INTO v_count;
CLOSE two;
OPEN three;
FETCH three INTO v_count;
CLOSE three;
END;

nbk9lsj@GEN3QB> exec simple_demo;

PL/SQL procedure successfully completed.

nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql

HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
3365040921 D4C02F74 SELECT COUNT(*) FROM ALL_OBJ 3
3
ECTS

2893531678 D4C259A4 select count(*) from all_objec 0
6
ts

3230163941 D4BB7348 select count(*) from all_Objec 0
1
ts

1481688868 D4BDECC8 select count(*) from All_objec 0
4
ts
note: why PA

nbk9lsj@GEN3QB> exec simple_demo;

PL/SQL procedure successfully completed.

nbk9lsj@GEN3QB> exec simple_demo;

PL/SQL procedure successfully completed.

nbk9lsj@GEN3QB> exec simple_demo;

PL/SQL procedure successfully completed.

nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql

HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
3365040921 D4C02F74 SELECT COUNT(*) FROM ALL_OBJ 3
12
ECTS

2893531678 D4C259A4 select count(*) from all_objec 0
6
ts

3230163941 D4BB7348 select count(*) from all_Objec 0
1
ts

1481688868 D4BDECC8 select count(*) from All_objec 0
4
ts