Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73

    Default values in sql*loader

    Hello,

    How can I specify in sqlldr control file the following condition:
    If the value for specific column in datafile is null (two column separators one after another) I want to insert default column value, otherwise insert that value. I don't want to use "NVL(col, -1)" because column default can change, but control file shouldn't.

    Thanks,
    Grzegorz

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    There are two clauses:

    DEFAULTIF field_condition sets the numeric column to zero when the specified condition is true (applying it on character column sets its value to NULL).
    The same can be achieved by using the DECODE function.

    NULLIF field_condition sets the column's value to NULL if the condition is true.
    Use of the NVL function has the same effect.

    For example:
    column1 position(1:8) char nullif (column1="mouse")

    Obviously, those clauses do things you don't need ... Besides that, you said that column default can change. Does it mean that you'd want to change the default column value dynamically? Passing it as a parameter to the control file? I'm not sure it is possible at all ...

  3. #3
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Thanks for your answer,

    ,,column default can change'' - I meant that the value for column, which is null in datafile, should be taken from column constraint, as if it was just insert with hole on this column.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    Actually, there IS something you could do ... use a database trigger. Here's an example (using a simple INSERT statement; the same goes for SQL*Loader):
    PHP Code:
    CREATE TABLE BRISIME
    (a NUMBER,
     
    b NUMBER DEFAULT 100
    );
     
    CREATE OR REPLACE TRIGGER trg_brisime_bi
      BEFORE INSERT ON BRISIME
      
    FOR EACH ROW
    BEGIN
      
    IF :NEW.b IS NULL
      THEN
        UPDATE BRISIME
        SET b 
    = DEFAULT;
      
    END IF;
    END;
    /
     
    INSERT INTO BRISIME (aVALUES (1);
     
    SELECT FROM BRISIME;
      
    A   B
    --- --- 
      
    1 100 
    This works on Oracle 9 (and it won't work on Oracle 7; can't tell about Oracle 8).
    Last edited by Littlefoot; 07-06-04 at 03:11.

  5. #5
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Thank you for your advice, but unfortunately it doesn't solve my problem - in your example column b should be NOT NULL, so the insert fails. I've developed another solution - in sqlldr control file I've entered
    Code:
    LOAD DATA
    CHARACTERSET WE8ISO8859P1
    APPEND
    INTO TABLE BRISIME
    FIELDS TERMINATED BY X'09'
    TRAILING NULLCOLS
    (
    A,
    B "DECODE(:B, NULL, GetDefaultData('BRISME', 'B'), :B)",
    )
    where GetDefaultData is
    Code:
    CREATE OR REPLACE FUNCTION GetDefaultData (
       p_tname   IN   VARCHAR2,
       p_cname   IN   VARCHAR2
    )
       RETURN VARCHAR2
    AS
       l_cursor     INTEGER         DEFAULT DBMS_SQL.OPEN_CURSOR;
       l_n          NUMBER;
       l_long_val   VARCHAR2 (4000);
       l_long_len   NUMBER;
       l_buflen     NUMBER          := 4000;
       l_curpos     NUMBER          := 0;
    BEGIN
       DBMS_SQL.PARSE (l_cursor, 'SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS utc WHERE UPPER(utc.TABLE_NAME) = ''' || 
    	   p_tname || ''' AND UPPER(utc.COLUMN_NAME) = ''' || p_cname || ''' AND UPPER(utc.NULLABLE) = ''N''' , DBMS_SQL.native);
       DBMS_SQL.DEFINE_COLUMN_LONG (l_cursor, 1);
       l_n := DBMS_SQL.EXECUTE (l_cursor);
    
       IF (DBMS_SQL.FETCH_ROWS (l_cursor) > 0)
       THEN
          DBMS_SQL.COLUMN_VALUE_LONG (l_cursor, 1, l_buflen, l_curpos, l_long_val, l_long_len);
       END IF;
    
       DBMS_SQL.CLOSE_CURSOR (l_cursor);
       RETURN l_long_val;
    END GetDefaultData;
    but it's too slow - it takes about 6x more time than standard insert...
    I don't want to hardcode defaults into triggers because I've got about 500 number columns with NOT NULL constraint and default clause. It would be a lot of code to write/maintain (for example when column default changes). So it would be great to have all changes placed in control files, as they are generated with nice script.

    Thanks,
    Grzegorz

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    Default isn't hardcoded; part of a trigger I wrote ("UPDATE BRISIME SET b = DEFAULT;") says that column 'b' should be updated to the default value provided during table creation. Such a code does NOT need to be updated if you change a column default value using ALTER TABLE command. "DEFAULT" in this UPDATE statement should be considered as a reserved word. Do not substitute it with real column default value.

    In other words, my
    UPDATE BRISIME SET b = DEFAULT;
    must NOT be substituted with
    UPDATE BRISIME SET b = 100;

    Furthermore, such a trigger will do right what you wanted - it will not allow inserting a null value into the column, even though SQL*Loader has nothing to insert into it.

    I believe such a thing should work ...

  7. #7
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Yeah, I know that it doesn't become set ... = 100.
    But UPDATE BRISIME SET b = DEFAULT updates each time all of table, not only our desired row, doesn't it?

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    LOL, but of course it updates all rows, silly me
    But now it will not ...
    CREATE OR REPLACE TRIGGER trg_brisime_bi
    BEFORE INSERT ON BRISIME
    FOR
    EACH ROW
    BEGIN
    IF :NEW.
    b IS NULL
    THEN
    UPDATE BRISIME
    SET b
    = DEFAULT
    WHERE a = :new.a;
    END
    IF;
    END
    ;
    /

    Does it make any sense?

  9. #9
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    It doesn't seem to work, I get

    Record 1: Rejected - Error on table BRISIME.
    ORA-01400: cannot insert NULL into ("BRISIME"."B")

    In fact, I'm not really surprised - assuming that A is unique key of BRISIME this trigger is fired before we insert the row into table, so how can it update anything?
    The other problem is that I'm not sure if I have unique key in each table.

    Thanks,
    Grzegorz

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    What a shame ... However, you still have a solution using your GetDefaultData function, right? It might be slow, but it (opposite to my little trigger) works.

    Hm, on the other hand ... how about this idea: allow NULLs in a column. Load all the data, never mind NULL values. As the loading finishes, execute something like this:

    UPDATE brisime SET
    b = DEFAULT
    WHERE b IS NULL;

    Would that work?

  11. #11
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Yeah, this is a good solution. I like it. But there's one problem - how should I proceed with update? It cannot be done via after insert trigger - table is mutating. Calling a procedure after sqlldr also isn't a good idea. Any suggestions?

    Thanks,
    Grzegorz

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    I have a similar situation - first I load data into a table, and then call SQL procedure which does something with loaded data. It is done through a command procedure (on OpenVMS) which looks like this:

    $ SQLLOAD 'P1/'P2 CONTROL=load_13.ctl LOG=load_13.log SILENT=HEADER
    $ SQLPLUS -S 'P1/'P2 @load_14_insert.sql

    (P1 and P2 are username and password passed as parameters from the SQL*Menu).

    You said it isn't a good idea; why?

    Mutating table; OK, but it can be solved easily (if you know a way, that is ). But, I'm afraid we'd run into another problem which we already discussed - we need to know unique identifier of a row that was inserted. ":new.a" isn't unique, you said. Would it cost too much to include some kind of unique ID into your table (populated by a sequence, for example)? In that case, trigger "trg_brisime_bi" would work. I guess

  13. #13
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Avoiding mutating table isn't so easy - you need few triggers to do that.
    However, I managed to run a procedure updating NULLs to defaults - first I said it isn't a good idea, because I thought there is no place in our application to do so. But it turned out there was

    Thanks for your help!
    Grzegorz

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    I'm glad you solved the problem!

    If you wish (and have time to try it), I have an example of solving mutating table problem. Cost me a bunch of nerves to figure it out; didn't understand it at once, but - who'd tell - it really works. So, here it is:

    Solution to such a problem (mutating table) lies in use of an INDEX-BY table (in pre-Oracle8 versions known as PL/SQL table).

    First, you should declare your type in a package:
    PHP Code:
    CREATE OR REPLACE package pkg IS
       TYPE tip_tab_type IS TABLE OF NUMBER
          INDEX BY BINARY_INTEGER
    ;
       
    tip_tab     tip_tab_type;
       
    tip_index   BINARY_INTEGER;
    END pkg
    Then, there are three triggers that make your update (or insert) possible:

    PHP Code:
    CREATE OR REPLACE TRIGGER TRG_1_BEF_STM
      BEFORE UPDATE 
    OR INSERT ON my_table
    BEGIN
      pkg
    .tip_index := 0;
    END;


    CREATE OR REPLACE TRIGGER TRG_2_AFT_ROW
      AFTER UPDATE 
    OR INSERT ON my_table
      
    FOR EACH ROW
    BEGIN
      pkg
    .tip_index := pkg.tip_index 1;
      
    pkg.tip_tab(pkg.tip_index) := :new.my_id;
    END;


    CREATE OR REPLACE TRIGGER TRG_3_AFT_STM
      AFTER UPDATE 
    OR INSERT ON my_table
    BEGIN
      
    FOR i IN 1 .. pkg.tip_index LOOP
        UPDATE my_table SET
          some_column 
    desired_value
          where my_id 
    pkg.tip_tab(i);
      
    END LOOP;
      
    pkg.tip_index := 0;
    END
    I hope you'll copy-paste it and save for future reference as it really, really helps. Hm, as if Oracle company couldn't write ... some procedure that would solve annoying mutating tables problem.

    OK ... see you!

  15. #15
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Thanks for the code, but...Mutating table's problem is fully explained at AskTom's - q&a and an article

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •