If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Default values in sql*loader

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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 ...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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 ...
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #8 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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?
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #14 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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!
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On