PL/SQL oddity

sokrates@12.1 > create procedure p is begin null; end this_does_not_compile;
  2  /

Warning: Procedure created with compilation errors.

sokrates@12.1 > show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/32	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
	 at line 1, column 11

sokrates@12.1 > REM expected
sokrates@12.1 > create function f return number is begin return null; end this_does_not_compile;
  2  /

Warning: Function created with compilation errors.

sokrates@12.1 > show errors
Errors for FUNCTION F:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/52	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'F'
	 at line 1, column 10

sokrates@12.1 > REM expected
sokrates@12.1 > create package pk is end this_does_not_compile;
  2  /

Warning: Package created with compilation errors.

sokrates@12.1 > show errors
Errors for PACKAGE PK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/19	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'PK'
	 at line 1, column 9

sokrates@12.1 > REM expected
sokrates@12.1 > REM 
sokrates@12.1 > REM but now:
sokrates@12.1 > REM 
sokrates@12.1 > create table t ( i int );

Table created.

sokrates@12.1 > create trigger tr before insert on t begin null; end funny_this_DOES_COMPILE;
  2  /

Trigger created.

sokrates@12.1 > show errors
No errors.

Yet Another Elementary SQL Bug

Environment

sokrates@12.1 > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > !echo $NLS_LANG
american_america.UTF8

Setup is

create table t ( r varchar2(1 char));
insert into t values('£');
commit;

alter session set nls_length_semantics=BYTE;
alter table t add( v as ( cast(r as varchar2(1))));

alter session set nls_length_semantics=CHAR;
create index t_idx on t(v);

exec dbms_stats.set_table_stats(null, 'T', numrows => 1e7)

We get different result for count(v) when using length-semantics BYTE, depending if a table scan or an index scan is used:

sokrates@12.1 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@12.1 > select /* index full scan used */ count(v) from t;

  COUNT(V)
----------
	 1

sokrates@12.1 > select /* full table scan used */ count(*), count(v) from t;

  COUNT(*)   COUNT(V)
---------- ----------
	 1	    0

Same behaviour was observed on 11.2

Issue with updatable views

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine

sokrates@12.1 > insert /* exception expected because 1st value is lost */ into v
  2  values('this one is lost', 'why isnt that one lost ?');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine
why isnt that one lost ?       why isnt that one lost ?

Best Practice in 12c

Since PL/SQL now is closely integrated into SQL, we hence can happily state

sokrates@12.1 > with function bestpractice return varchar2
  2  is
  3  begin
  4     return 'Do not use PL/SQL when it can be done with SQL alone !';
  5  end bestpractice;
  6  select bestpractice() from dual
  7  /

BESTPRACTICE()
--------------------------------------------------------------------------------
Do not use PL/SQL when it can be done with SQL alone !