RE: pl/sql and table defaults

From: William Beilstein <BeilstWH_at_OBG.com>
Date: Wed, 01 Nov 2000 15:54:32 -0500
Message-Id: <10667.120867@fatcity.com>

A trigger would be a quick and simple way to correct the actions of all = the procedures. make a trigger like the following

CREATE OR REPLACE TRIGGER myschema.total_tab_T1 BEFORE
INSERT=20
ON MYSCHEMA.TOTAL_TAB
FOR EACH ROW
BEGIN

:NEW.COLUMN1 :=3D NVL(:NEW_COLUMN1,0);
:NEW.COLUMN2 :=3D NVL(:NEW_COLUMN2,0);
:NEW.COLUMN3 :=3D NVL(:NEW_COLUMN3,0);
:NEW.COLUMN4 :=3D NVL(:NEW_COLUMN4,0);

END;
>>> "Koivu, Lisa" <lkoivu_at_qode.com> 11/01/00 02:50PM >>>
Hi Ron,=20

I have seen exactly what you are describing. DEFAULT column values are = only
applied when the insert statement does not specify a value for that = column.
See below. But back to your specific problem: There is no elegant way around this. It sounds like your processes are not DEFAULT friendly. You could define some triggers, but I am so anti-trigger. That would be ugly. So would some extremely dynamic SQL. =20

Hope this helps you. I have a feeling I didn't help at all...! Sorry!

SQL> create table testit=20
2 (col1 varchar2(5),=20
3 col2 varchar2(10) DEFAULT 'VIKING');

Table created.

SQL> insert into testit values('LOSS','BUCS');

1 row created.

SQL> INSERT INTO TESTIT (COL1) VALUES ('WIN'); 1 row created.

SQL> SELECT * FROM TESTIT; COL1 COL2
----- ----------
LOSS BUCS
WIN VIKING SQL> INSERT INTO TESTIT VALUES ('LOSS',NULL); 1 row created.

SQL> SELECT * FROM TESTIT; COL1 COL2
----- ----------
LOSS BUCS
WIN VIKING
LOSS SQL>=20 Hope this helps you .

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933=20
C: 954.658.5849

http://www.qode.com=20

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
Sent: Wednesday, November 01, 2000 1:12 PM To: Multiple recipients of list ORACLE-L

List,
I have a problem with the loading of tables using pl/sql. In my = procedure
I have defaulted the variables to 0 and the procedure will gather data = from
2 sales tables based on store ID and place it in a S_temp table. The procedure will also gather data from two order tables based on store ID = and
place it in a O_temp table. This portion of the procedure work ok. The problem shows it head when the procedure gathers data from the sales and order tables based on the store ID and enters the data in a total_tab. If there is no entries for the store in the sales tables but there are = entries
in the order tables, the procedure will make and entry in the total_tab = and
have "null" values in the fields related to the sales columns. I have set the defaults for the columns in the total_tab to 0 for all numeric columns = .

How do I get around the loading of nulls when the default value is set = to
0 on the columns when using pl/sql?? Short of re-writing the procedure = with
a lot of NVL's , is there any other answer?

Thanks,
ROR =AA=BF=AA --=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20 --=20
Author: Ron Rogers
INET: RROGERS_at_galottery.org=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Wed Nov 01 2000 - 14:54:32 CST