select into

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.

SQL> CREATE OR REPLACE function is_active(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select 1 into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      if ret is null then ret := 0;
11      end if;
12      return ret;
13  end;
14  /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
 2  ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
          1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select count(*) into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      return ret;
11  end;
12  /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
 2  ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
                     1                       0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-

You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.

CREATE OR REPLACE function is_active(i_is_active number)
       return number as
       ret number := 0;
  begin
       dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
       select 1 into ret
       from dual
      where i_is_active = 1;
     dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
     if ret is null then ret := 0;
     end if;
     return ret;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN return -1;
    end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1            -1

Only one (yet again)

Is that not a normal DBA work week.
Installing Oracle on HP-UX cluster
Trying to resolve problematic CPU usage -Solaris
Rewriting loooong query more readable
Fiquring out 9.2 RAC
Talking about a PL/SQL function having comments from the 90's
Talking about modeling tools
Partitioning postgresql
A very good chinese dinner at Dong Bei Hu
Some dark beer
Night in a hotel
Reading awr reports and creating some of my own
Making ssh connections to a 10.2 RAC environment
Installing 11.2
Dumping a datafile block, boy that wait information in v$session mislead me. Should have used snapper. Well the blocks were the same both in primary and standby
Rewriting that loooong query so that it will perform when there will be 10M rows in the future
Sitting in a company meeting and hearing that Solita is recruiting new people


Luckily some actual modeling work also. How to constrain a model where there is a variation of the only one problem. The difference here was that a country does not have to have a default currency. But if it has any currencies there has to be one as a default. And also a restriction that no virtual columns. Oracle version 10.2. In the following example currencies goes to A table and countries to D.

drop  materialized view da_atleast_one_mv;

drop table a cascade constraints purge;

drop table d cascade constraints purge;


create table d(d_id number primary key, current_a_id number)
;

create table a(a_id number primary key
             , d_id references d not null
             , constraint a_u unique(d_id,a_id))
;

alter table d add constraint d_curr_fk foreign key (d_id,current_a_id) references a(d_id,a_id) deferrable initially deferred;


create materialized view log on d with rowid;

create materialized view log on a with rowid;

create materialized view da_atleast_one_mv 
refresh on commit as 
select d.rowid drid, a.rowid arid, d.d_id, a.a_id
  from d, a
 where d.current_a_id(+)=a.a_id
;

alter table da_atleast_one_mv modify d_id constraint da_atleast_one_nn not null;

insert into d values (0,null);

commit;

--now we have a country without any currencies

insert into d values (1,null);

insert into a values (1,1);

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

insert into d values (2,2);

insert into a values (2,2);

commit;

select * from da_atleast_one_mv;

update d set current_a_id=null where d_id = 2;

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

Potential not nulls

Something to send to a development team of the model or just run the clauses from these generators. Here are ways to figure out potential missing not null constraints.

First a test table

create table nnc(n number, m number);

insert into nnc values (1,2);

commit;

By counting the actual rows

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Or another way by using stats

exec dbms_stats.gather_table_stats(user,'NNC');

select t.table_name, c.column_name, t.num_rows 
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;


select '--alter table '||t.table_name||' modify '||c.column_name||' not null;'
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;

And the thirdth option putting those together. Candidates from stats and after that checking the actual data.

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
   and table_name in (
    select t.table_name
      from user_tab_cols c 
     inner join user_tables t 
        on t.table_name=c.table_name
     where c.nullable='Y'
       and t.temporary='N'
       and num_rows > 0
       and num_nulls = 0
   )
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Well maybe the stats version is the one to use.

I prefer having names also to the not null constraints. So after dealing with the developer

alter table NNC modify M constraint nnc_m_nn not null;

Visualizing plans

http://tech.e2sn.com/apps/planviz seems something that will help me tomorrow.

Rely constraint validated mess

Not so happy with the html support pages. Could not create a SR. Had to go back to flash pages.

Today's issue is about a constraint that is in validated state. Oracle documentation says that if you use rely constraints, you should know what you are doing. Maybe you did not know and want to go back to norely mode. Here is an example what you should not do.

SQL> create table pa (pa_id number constraint pa_pk primary key);

Table created.

SQL>
SQL> insert into pa
  2  select level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> create table ch(ch_id number constraint ch_pk primary key
  2                , pa_id not null constraint ch_pa_fk references pa
  3                )
  4  ;

Table created.

SQL>
SQL> insert into ch select level ch_id, level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk rely;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
DISABLED NOT VALIDATED RELY

SQL>
SQL> insert into ch select level+10 ch_id, level+10 pa_id from dual connect by level < 3;

2 rows created.

SQL> select * from pa;

     PA_ID
----------
         1
         2

SQL> select * from ch;

     CH_ID      PA_ID
---------- ----------
         1          1
         2          2
        11         11
        12         12

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk validate;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk norely;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable validate;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
ENABLED  VALIDATED

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

no rows selected

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

     CH_ID      PA_ID
---------- ----------
        11         11
        12         12

SQL> alter table ch modify constraint ch_pa_fk enable validate;
alter table ch modify constraint ch_pa_fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYSTEM.CH_PA_FK) - parent keys not found

Going back from rely mode to norely. Go first to norely and validate after.

New year even happier

After migrating to 11.2 flash really has been driving me nuts. Mainly the migration from 11.1 gave us improved performance. But being a SR generator has made me feel not so warm thoughts for flash interface. Not yet faced a problem that we have not been able to overcome. Hopefully supporthtml.oracle.com makes my life happier. Thank you Laurent Schneider.