Parameters for COMMIT operations

A quick link to a blog post by Christian Antognini : "COMMIT_WAIT and COMMIT_LOGGING".

.
.
.

Primary Key name appears to be different

Here is a quick post on a scenario where Oracle attempts to validate a Primary Key (index present) when creating a Materialized View and apparently reports a different name for the constraint !. The Primary Key and Indexis called "TARGET_TABLE" but the ORA-2437 error reports the error as being against "TARGET_TABLE1" -- with a "1" seemingly appended.

SQL> drop table target_table purge;

Table dropped.

SQL> create table target_table (column_1 number not null, column_2 varchar2(30), column_3 varchar2(300));

Table created.

SQL> 
SQL> insert into target_table
  2  select rownum, dbms_random.string('X',8),dbms_random.string('U',30)
  3  from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from target_table;

  COUNT(*)
----------
       100

SQL> 
SQL> create index target_table on target_table(column_1);

Index created.

SQL> insert into target_table values (5,'ABC','XYZXZYZ');

1 row created.

SQL> 
SQL> alter table target_table add constraint target_table primary key (column_1) enable novalidate ;

Table altered.

SQL> 
SQL> select index_name from user_indexes where table_name = 'TARGET_TABLE';

INDEX_NAME
------------------------------
TARGET_TABLE

SQL> 
SQL> select constraint_name, index_name
  2  from user_constraints
  3  where table_name = 'TARGET_TABLE'
  4  and constraint_type in ('U','P');

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
TARGET_TABLE                   TARGET_TABLE

SQL> 
SQL> create materialized view log on target_table;

Materialized view log created.

SQL> 
SQL> create materialized view tt_mv
  2  refresh fast on demand
  3  as select column_1, column_2
  4  from target_table
  5  /
from target_table
     *
ERROR at line 4:
ORA-02437: cannot validate (HEMANT.TARGET_TABLE1) - primary key violated


SQL> 
SQL> 
SQL> select count(*) from target_table where column_1 = 5;

  COUNT(*)
----------
         2

SQL> 

Today's question : Can you create another test case where a "1" appears to be appended ?
.
.
.

TOO_MANY_ROWS and Variable Assignment

UPDATE : Also see Jeffrey Kemp's blog post on the same topic, earlier in 2008.

When you have a cursor that is assigning a return value to a variable, you expect the cursor to return a single row.  If it returns more than one row, the resulting error is a TOO_MANY_ROWS error.  This can be handled as an Exception.
However, this blog post is about what might be happening to the variable that you assign the fetched value to.

In this demo, I use a "CUSTOMER" table and a VALIDATE_CUSTOMER procedure :

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> 
SQL> drop table CUSTOMER purge;

Table dropped.

SQL> 
SQL> create table CUSTOMER
  2  (cust_id  number,  sales_mgr_id number, cust_name varchar2(50), cust_start_date date, status_flag varchar2(1));

Table created.

SQL> 
SQL> insert into CUSTOMER values (1,1,'USA-NY Customer',to_date('01-JAN-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (2,1,'USA-LA Customer',to_date('01-JUL-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (3,2,'GB-LN Customer',to_date('01-SEP-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (4,3,'FR-PR Customer',to_date('01-OCT-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> -- this cust_id 4 will be replacing the current one from 01-JAN-2013
SQL> -- unfortunately, the date entered is 01-JAN-2011
SQL> insert into CUSTOMER values (4,4,'JP-TY Customer',to_date('01-JAN-2011','DD-MON-YYYY'),'N');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> create or replace procedure VALIDATE_CUSTOMER (in_cust_id in number, out_cust_status out varchar2)
  2  as
  3  -- initialize status
  4  -- U for Unknown
  5  l_cust_flag  varchar2(1) := 'U';
  6  begin
  7  select status_flag into l_cust_flag
  8  from  CUSTOMER
  9  where cust_id = in_cust_id
 10  and cust_start_date <= SYSDATE;
 11  out_cust_status := l_cust_flag;
 12  dbms_output.put_line('Flag : ' || l_cust_flag);
 13  dbms_output.put_line('Cust Status Returned : ' || out_cust_status);
 14  exception
 15  when others then
 16  dbms_output.put_line('Flag : ' || l_cust_flag);
 17  dbms_output.put_line('Exception  !! Cust Status Returned : ' || out_cust_status);
 18  end;
 19  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 

So, I have a table with 2 rows for CUST_ID=4.  How will the procedure handle it ?
I have deliberately added dbms_output.put_line to show the values that the procedure "sees".

I run with three test CUST_IDs  : 1  (existant and valid),   9  (not existant),  4  ("too_many_rows").

SQL> SET SERVEROUTPUT ON
SQL> 
SQL> -- declare a variable to get the return value in sqlplus
SQL> 
SQL> -- a valid customer
SQL> variable cust_status_1 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(1,:cust_status_1);
Flag : Y
Cust Status Returned : Y

PL/SQL procedure successfully completed.

SQL> print :cust_status_1

CUST_STATUS_1
--------------------------------
Y

SQL> 
SQL> -- a non-existent customer
SQL> variable cust_status_9 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(9,:cust_status_9);
Flag : U
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_9

CUST_STATUS_9
--------------------------------------------------------------------------------


SQL> 
SQL> -- a cust_id with too_many_rows
SQL> variable cust_status_4 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(4,:cust_status_4);
Flag : Y
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_4

CUST_STATUS_4
--------------------------------------------------------------------------------


SQL> 

The "Flag" and returned (output out_cust_status) values are correct for CUST_IDs 1 and 9. (For 9, the Flag is the initialized value of "U" because the cursor did not overwrite it).
However, for CUST_ID 4, although the procedure raises an EXCEPTION and returns no value, the Flag that is present *did* get reset from the initialized value of "U" to "Y" !! Apparently, when a cursor returns a row, it does assign the return value to the designated variable. If the cursor then returns *another* row, it raises an Exception but does not clear the value that was already assigned from the first row.

.
.
.