Default new record value from the table

oracleoracle-11goracle-11g-r2

If I had a table called person with the fk country_id.
Assuming that we have a table called country, which has a default_country column. And all rows except one (for USA), have default_country set to 0, only USA row has default_country to 1.

So if I m inserting person record, and I don't provide country_id, how can I default all new records to have country_id save with the id from row USA.

I know I can set a default column value to id (which I hardcode), but I don't know if its possible to get that from the table, here is what I mean:

"COUNTRY_ID" NUMBER(38,0) DEFAULT -1 CONSTRAINT PR_CN_NOT_NULL NOT NULL

-1 is the hardcoded. it would be nice if I could select it from country table using default_country flag rather than knowing the ID in advance, any ideas?

Best Answer

It's not possible with DEFAULT. Docs says:

Restrictions on Default Column Values
Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

So DEFAULT (SELECT country_if FROM country where default_country =1 and rownum=1), and even DEFAULT (SELECT 1 FROM DUAL) fail because it cannot contain subquery . You cannot work it around by defining your own function either.
The only way is to create row level before insert trigger .

Related Solutions

Migrating to Oracle Flashback for Historization on Oracle 11g

It sounds like Oracle Total Recall may fit your needs:

Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as well as internal audits, require companies to keep historical data available for long periods of time. Oracle Total Recall, part of Oracle's comprehensive portfolio of database security solutions, works with Oracle Database 11g, Enterprise Edition to help companies store their data in secure, tamper-proof databases while keeping it accessible to existing applications

The documentation states that it can support refactoring:

In Oracle Database 11g Release 1, the Add Column DDL operation is supported with Flashback Data Archive. With Oracle Database 11g Release 2, the following DDL operations are supported, with full support for Flashback queries across all associated changes:

• Add, Drop, Rename, Modify Column

• Drop, Truncate Partition

• Rename, Truncate, Drop Table

For more complex DDL – upgrades, split table, etc. – the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Flashback Data Archive on specified tables.

Reset every Oracle sequence to the MAX value of its target table PK

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;

Best Answer

Related Solutions

Migrating to Oracle Flashback for Historization on Oracle 11g

Reset every Oracle sequence to the MAX value of its target table PK

Related Question