CURSOR_SHARING and PL/SQL procedure

With cursor_sharing=FORCE, in most cases, Oracle will replace literal values with bind variables, however,this is not the case for PL/SQL blocks. See the following test case. When using CALL proc_name(), it can. This behavior is mentioned in Jonathen Lewis's book "Cost-based Oracle Fundamentals" p159.

SQL>alter session set cursor_sharing=force;

Session altered.

SQL>drop table t;

Table dropped.

SQL>create table t(a number);

Table created.

SQL>
SQL>create or replace procedure denis_proc(a in number)
  2   is
  3  begin
  4    insert into t values(a);
  5   commit;
  6  end;
  7  /

Procedure created.

SQL>
SQL>exec denis_proc(1);

PL/SQL procedure successfully completed.

SQL>exec denis_proc(2);

PL/SQL procedure successfully completed.

SQL>exec denis_proc(3);

PL/SQL procedure successfully completed.

SQL>call denis_proc(4);

Call completed.

SQL>call denis_proc(5);

Call completed.

SQL>call denis_proc(6);

Call completed.

SQL>
SQL>col sql_text format a30
SQL>
SQL>select sql_id, executions, sql_text from v$sqlarea where sql_text like '%denis_proc%';

SQL_ID1G        EXECUTIONS SQL_TEXT
------------- ---------- ------------------------------
1ms5rkrgwt28v          1 BEGIN denis_proc(1); END;
0vcasv51bxgpv          1 BEGIN denis_proc(3); END;
dcna79fxryrkx          1 BEGIN denis_proc(2); END;
6skbuvu6fb9k0          3 call denis_proc(:"SYS_B_0")

SQL>
SQL>select * from t;

         A
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL>

Effect of a SQL* Plus environment variable: set pagesize 0

Last night, I troubleshooted a ksh shell script, in which a variable is assigned a value that is returned from SQL* Plus commands. I found that without 'set pagesize 0', the value returned had an undesirable leading space. Below is a test case that demonstrate this effect.

SQL> select col1, col3 from t;
 
      COL1 C
---------- -
         1 Y


$ cat test3.sh 
#!/bin/ksh 
 
RET1=` sqlplus -s  / << EOF
set  echo off head off feed off verify off term off  
select col3 from t
where  col1=1;
EOF
`
 
echo "RET1  >>>>"$RET1"<<<<"
if [[ $RET1 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi
 
RET2=` sqlplus -s  / << EOF
set echo off head off feed off verify off term off 
set pagesize 0
select col3 from t
where  col1=1;
EOF
`
 
echo "RET2  >>>>"$RET2"<<<<"
if [[ $RET2 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi


$ ./test3.sh 
RET1  >>>> Y<<<<
No
RET2  >>>>Y<<<<
Yes