Monday, August 28, 2017

The amazing ANSI join syntax quirk

The kind of quirks I love: those that you can find a workaround for without having to wait for a patch.

I am talking Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production running on some unix flavor (I ignore which flavor, I've no access to the unix box).

You execute the following query and it works.

select f.cuaa,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O'
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

Then you execute:

insert into mp_part (cua, id_fgra, id_cons, id_part, id_prt, shape_sim, cod_na, fol, part)
select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

SQL Error: No more data to read from socket
it caused a core dump.

Then you try 
create table test as
select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O'
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

and it works without a hitch.

It turns out that the problem is with the INSERT SELECT ANSI join syntax combined with a spatial function call in the projection list.

If I rewrite the query with the traditional Oracle syntax, it runs smoothly.


Monday, August 07, 2017

SQLDeveloper 4.2 problem with some bind variables values


I just found out that SQLDeveloper version 4.2.0.17.089 (tested on windows 10) might execute SQL or PL/SQL code containing bind variables with wrong argument values when such values are strings (VARCHAR2) containing just digits with leading zeros (i.e. international phone numbers, VAT codes, UPC codes...) entered at the bind variables prompt.

This seems to happen only with PL/SQL code or certain SQL containing statements like INSERT, not pure SELECTs.

The practical consequences of this problem range from statements ending with unexpected errors or executing a block of PL/SQL with wrong parameter values which could lead to a variety of anomalies like inserting, deleting or updating the wrong rows.

You can easily see the problem by yourself executing the following sample code:

create or replace function function_returning_collection(
   p_arg in varchar2)
   return ORA_MINING_VARCHAR2_NT
as
   l_str_tab       ORA_MINING_VARCHAR2_NT;
begin

   select str
    bulk collect into l_str_tab
    from(
      select '01234567' as str
        from dual
       union all
      select '31234567' as str
        from dual
      union all
      select '01234567' as str
        from dual)
    where str = p_arg;
   
   return l_str_tab; 
end;


create table test_bind (str varchar2(255));

insert into test_bind
select * from table(function_returning_collection(:var));

Enter 01234567 at SQLDeveloper's prompt and it will say "0 rows inserted", then execute just the SELECT portion of the query and it will return two rows instead.
Embedding the number within tick marks at the prompt won't fix the problem, SQLDeveloper is clearly taking the input value verbatim as a string.
I believe the only clean way to fix this in a future release is to allow the user to specify the data type being entered at the prompt, because looking at SQLDeveloper's statement log it's clear that SQLDeveloper is trimming the leading zero and then, owing to the implicit conversion, adding a leading blank, which transforms the initial "01234567" into a "_1234567" ( where "_" represents the blank character).

Note also that SQLDeveloper's online help makes no mistery of this "feature":

Execute Statement executes the statement at the mouse pointer in the Enter SQL Statement box. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary);

Friday, July 07, 2017

The importance of being CURRENT_USER

Say I am user FLAVIO.
SCHEMA1 is another schema.
I have been granted EXECUTE and DEBUG privileges on SCHEMA1.TEST_PROC through a role.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

If I perform a query like this:

  select s.owner, s.name, s.type, s.line, s.text
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');

the query returns the source lines.

If I perfom the same query inside an anonymous PL/SQL block:

declare
  type source_line is record (
    owner  varchar2(30),
    name   varchar2(30),
    type   varchar2(30),
    line   integer,
    text   varchar2(4000)
  );
  type source_lines is table of source_line;
  sources   source_lines;
begin
  select s.owner, s.name, s.type, s.line, s.text
    bulk collect into sources
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');
         
  dbms_output.put_line(sources.count);
    
end;
/

It works as well.

But if I create a procedure equal to the block above:

create or replace procedure Proc1 as
  type source_line is record (
    owner  varchar2(30),
    name   varchar2(30),
    type   varchar2(30),
    line   integer,
    text   varchar2(4000)
  );
  type source_lines is table of source_line;
  sources   source_lines;
begin
  select s.owner, s.name, s.type, s.line, s.text
    bulk collect into sources
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');
         
  dbms_output.put_line(sources.count);
    
end;
/

It returns no rows.

Privileges granted through roles do not work in this case unless you add AUTHID CURRENT_USER in the procedure declaration, as follows:

create or replace procedure Proc1 AUTHID CURRENT_USER as...

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles