RE: pl/sql and table defaults

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Wed, 1 Nov 2000 14:45:09 -0500
Message-Id: <10667.120845@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0443C.435ACE88
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

"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-----
From: Ron Rogers [mailto:RROGERS_at_galottery.org] Sent: Wednesday, November 01, 2000 1:12 PM To: Multiple recipients of list ORACLE-L Subject: pl/sql and table defaults

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

Author: Ron Rogers
INET: RROGERS_at_galottery.org

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 also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C0443C.435ACE88
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12">

<TITLE>RE: pl/sql and table defaults</TITLE>
</HEAD>
<BODY>

Hi Ron,

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.

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

<P><FONT SIZE=3D2>SQL&gt; create table testit </FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; (col1 varchar2(5), </FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; 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;

<P><FONT SIZE=3D2>COL1&nbsp; COL2</FONT>
<BR><FONT SIZE=3D2>----- ----------</FONT>
<BR><FONT SIZE=3D2>LOSS&nbsp; BUCS</FONT>
<BR><FONT SIZE=3D2>WIN&nbsp;&nbsp; VIKING</FONT>
</P>

SQL> INSERT INTO TESTIT VALUES = ('LOSS',NULL);

1 row created.

SQL>  SELECT * FROM TESTIT;

<P><FONT SIZE=3D2>COL1&nbsp; COL2</FONT>
<BR><FONT SIZE=3D2>----- ----------</FONT>
<BR><FONT SIZE=3D2>LOSS&nbsp; BUCS</FONT>
<BR><FONT SIZE=3D2>WIN&nbsp;&nbsp; VIKING</FONT>
<BR><FONT SIZE=3D2>LOSS</FONT>
</P>

SQL>

<P><FONT SIZE=3D2>Hope this helps you .</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>

Fort Lauderdale, FL  33319

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =

TARGET=3D"_blank">http://www.qode.com</A>

"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----- From: Ron Rogers [<A = HREF=3D"mailto:RROGERS_at_galottery.org">mailto:RROGERS_at_galottery.org</A>]<= /FONT>

<BR><FONT SIZE=3D2>Sent: Wednesday, November 01, 2000 1:12 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: pl/sql and table defaults</FONT>
</P>
<BR>

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?

<P><FONT SIZE=3D2>Thanks,</FONT>
<BR><FONT SIZE=3D2>ROR =AA=BF=AA</FONT>
</P>

-- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Ron Rogers</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: RROGERS_at_galottery.org</FONT>
</P>

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

SIZE=3D2>---------------------------------------------------------------=

-----
 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 = Received on Wed Nov 01 2000 - 13:45:09 CST