Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have an Oracle 11g table with not null column and default value. Not null constraint is enabled, there are no triggers (in dba_triggers) which could to modify inserted value. It's impossible to insert null into this column by insert command from script, but thesame insert from package inserts default value in such case.
I can't find any help with Google. May be, someone knows what's the matter and how to correct it?

More info:
One of inserts(here're 3):
SQL
INSERT INTO ks_salda_kont
  (id, ksk_id, instrument, numer_rejestru, saldo, obroty_d, obroty_k,
   saldo_energii, obroty_energ_d, obroty_energ_k, udtczas_ks, udtczas_sys,
   ksdo_id)
VALUES
  (kssk_sq.nextval, iid, vInst, vRej, rr.kw_s, rr.kw_dt, rr.kw_kr, rr.en_s,
   rr.en_dt, rr.en_kr, dData, dSys, iddo);


All variable names differ from column names. Erronous column is numer_rejestru. Table creation statement is:
SQL
CREATE TABLE "PMSKS"."KS_SALDA_KONT" 
   (	"ID" NUMBER(17,0) NOT NULL ENABLE, 
	"KSK_ID" NUMBER NOT NULL ENABLE, 
	"INSTRUMENT" VARCHAR2(130) DEFAULT 'XXX' NOT NULL ENABLE, 
	"NUMER_REJESTRU" VARCHAR2(100) DEFAULT 'XXX' NOT NULL ENABLE, 
	"SALDO" NUMBER(17,2) DEFAULT 0 NOT NULL ENABLE, 
	"OBROTY_D" NUMBER(17,2) DEFAULT 0 NOT NULL ENABLE, 
	"OBROTY_K" NUMBER(17,2) DEFAULT 0 NOT NULL ENABLE, 
	"OBROTY_ENERG_D" NUMBER(20,4) DEFAULT 0 NOT NULL ENABLE, 
	"OBROTY_ENERG_K" NUMBER(20,4) DEFAULT 0 NOT NULL ENABLE, 
	"SALDO_ENERGII" NUMBER(20,4) DEFAULT 0 NOT NULL ENABLE, 
	"BILANS_OTW_D" NUMBER(17,2) DEFAULT 0 NOT NULL ENABLE, 
	"BILANS_OTW_K" NUMBER(17,2) DEFAULT 0 NOT NULL ENABLE, 
	"UDTCZAS_KS" DATE DEFAULT to_date('19000101','yyyymmdd') NOT NULL ENABLE, 
	"UDTCZAS_SYS" DATE DEFAULT to_date('19000101','yyyymmdd') NOT NULL ENABLE, 
	"KSDO_ID" NUMBER DEFAULT 0 NOT NULL ENABLE, 
	 CONSTRAINT "KSSK_KSSK_2_UK" UNIQUE ("KSK_ID", "INSTRUMENT", "NUMER_REJESTRU")
Posted
Updated 16-Oct-14 1:36am
v3
Comments
Bernhard Hiller 16-Oct-14 4:57am    
"inserts default value" - I expect exactly this behavior.
[no name] 16-Oct-14 5:17am    
No. "The DEFAULT clause lets you specify a value to be assigned to the column if a subsequent INSERT statement omits a value for the column"
[no name] 16-Oct-14 5:10am    
Why?
a VARCHAR2(1):=NULL;
INSERT INTO tbl(not_null_column) VALUES(a);
generates exception in SQL window, but inserts default value while executed from package
Jörgen Andersson 16-Oct-14 7:05am    
What is the default value?
[no name] 16-Oct-14 7:18am    
Simply 'XXX', column is varchar2(100)

Have a look here: Oracle Tip: How to use default values with database columns[^]

Quote:
You must specify at least one column, but you may use the DEFAULT keyword to allow the default value rather than hard-coding a value, so the following is valid syntax that will create a row with all DEFAULT values.


So, finally, INSERT statement should looks like:
SQL
INSERT INTO TableName (<NumericField>, <FieldWithDefaultValue>)
VALUES(0, DEFAULT)


In case, you are addind values from other table, use COALESCE[^] function, which replaces NULLs with default value.

SQL
INSERT INTO Table1 (<Field_list>)
VALUES(Table2.Field1, COALESCE(Table2.Field2,DEFAULT))


But, i think that Table2 returns empty string ('').

Note: Not tested, because i don't have Oracle database ;)
As Jörgen Andersson suggested, the script is here: http://sqlfiddle.com/#!4/6e974/4[^]

Cheers from Poland ;)
 
Share this answer
 
v4
Comments
Jörgen Andersson 16-Oct-14 16:27pm    
You can test it using sqlfiddle.com
But I have a feeling you might have misunderstood the problem.
Maciej Los 16-Oct-14 16:31pm    
Thank you for suggestion.
Why do you think i misunderstood the problem? Can you elaborate?
Jörgen Andersson 16-Oct-14 16:40pm    
As I understand it, he doesn't want to know how to use default values, I rather believe he understands how it works.
I understand the question as that he wants to know how it comes that the code in a package can insert a null value on a column with a not null constraint. It should actually be impossible.
Maciej Los 16-Oct-14 16:56pm    
You might be right. I think, that the vRej returns empty string (''), not NULL. See updated answer.
[no name] 16-Oct-14 23:57pm    
Unfortunately, no. vRej is null (calling argument is null and PL/SQL Developer debugger sees this variable as simply NULL too). Oracle is configured to see empty strings as nulls. There are two possibilities, I think:
1. Table is corrupted. I try to destroy it and recreate (strange)
2. Debugger watch error: vRej isn't null, it's changed to 'XXX' in the package, in spite of I can't find it

Now, I use a workaround: if vRej is null I set it to DEFAULT.
Workaround works ok. But, I am not glad; without workaround it works too. My last test had a silly mistake, error results were erronous too ... May be the table was corrupted. Maybe (another solution) the person, who used my package, do commit after errors.

Thx for Jörgen Andersson and Maciej Los
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900