AidanLee has asked for the wisdom of the Perl Monks concerning the following question:

I'm struggling a bit with a query I would expect to work, but errors both running it through my script and from the sqlplus prompt. I was hoping that anyone who had some Oracle experience could point out what it is I'm doing wrong:

Given this table schema for the table 'Promotions': 
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER(5)
 DATE_CREATED                    NOT NULL DATE
 DATE_MODIFIED                   NOT NULL DATE
 TITLE                           NOT NULL VARCHAR2(64)
 PROMOTION_PHRASE                NOT NULL VARCHAR2(255)
 DATE_START                               DATE
 DATE_END                                 DATE
 STATUS                          NOT NULL NUMBER(2)
 TRIGGER_TYPE                    NOT NULL NUMBER(2)
 TRIGGER_VALUE                   NOT NULL VARCHAR2(12)
 TRIGGER_QUANTITY                NOT NULL VARCHAR2(12)
 REWARD_TYPE                     NOT NULL NUMBER(2)
 REWARD_VALUE                    NOT NULL VARCHAR2(12)
 REWARD_QUANTITY                 NOT NULL VARCHAR2(12)
 USES_PERCUSTOMER                NOT NULL NUMBER(5)
 USES_MAXIMUM                    NOT NULL NUMBER(10)

[download]

And my query:

insert into Promotions
 (ID,Date_Created,Date_Modified,Title,
  Promotion_Phrase,Date_Start,Date_End,Status,
  Trigger_Type,Trigger_Value,Trigger_Quantity,
  Reward_Type,Reward_Value,Reward_Quantity,
  Uses_PerCustomer,Uses_Maximum)
values
 (1,SYSDATE,SYSDATE,'test promotion',
  'phrase here',NULL,NULL,0,0,'','',0,'','',1,0);

[download]

in my perl code I'm using placeholders for all the values, and undef's for the two null values I do want

. The error I'm getting is this:

ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

Of course, Oracle doesn't take the courtesy to tell me *which* column it is referring to. Any insights would be much appreciated

Replies are listed 'Best First'.
Oracle and empty strings...
by Rhose (Priest) on Sep 14, 2001 at 01:11 UTC
    Sorry I am so late finding this node, but maybe I can offer a little insight as to what is happening.

    The following information is quoted from Oracle's documentation:

    Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

    This is not a correct implementaion, but it is what Oracle currently does. A NULL value is an unknown value... an empty string is not unknown -- it is known to be empty.

    So, the problem here is the empty strings you want to insert are being converted to NULLs which violate the NOT NULL constraint on the columns. While this may not be much of a "fix", your options would seem to be:

    • Remove the NOT NULL column constraints
    • Insert some other value into the columns (like a single space.)

    I hope this helps... (or at least explains what is happening.)

[reply]
Re: OT: Oracle SQL issues
by seesik (Initiate) on Sep 13, 2001 at 18:05 UTC
    let's see what we're looking at.. 
    ID   =  1
Date_Created  = SYSDATE
Date_Modified = SYSDATE
Title = 'test promotion'
Promotion_Phrase = 'phrase here'
Date_Start = NULL
Date_End = NULL
Status = 0
Trigger_Type = 0
Trigger_Value = ''
Trigger_Quantity = ''
Reward_Type = 0
Reward_Value = ''
Reward_Quantity = ''
Uses_PerCustomer = 1
Uses_Maximum = 0

    [download]
    hmm, all columns are accounted for, so it's not like you're letting oracle implicitly fill in default nulls for some columns excluded from your insert. what else could it be...

    ...and undef's for the two null values I do want

    dbd::oracle is going to treat NULL's as undef coming OUT of queries, but not inversely. in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

    hth; cheers

[reply]
[d/l]
[select]
      dbd::oracle is going to treat NULL's as undef coming OUT of queries, but not inversely. in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

      This is wrong. From the DBI docs on placeholders:

      Undefined values, or "undef", can be used to indicate null values.

      [download]
      But it goes on to say that for SELECT statements, this won't work in 'WHERE field = ?' clauses because nothing is equal to NULL, and suggests an alternative in the case where you're looking for a possibly NULL field.
[reply]
[d/l]
        agreed; pre-coffee mental burp. see here.
[reply]
      ...in other words, you need to pass the string 'NULL' in your bind params or execute instead of passing perl's undef.

      Ok, that's good to know, but I'm wondering how DBD::Oracle is supposed to be able to make the distinction between the value NULL and the string literal 'NULL' (since i am using bind values, not building it into the actual query string)?

[reply]
        so i just got out some old code, and it looks like i've sent undef's in bind_params before. hmm...
        can you see what sort of constraints are on that table ?select CONSTRAINT_NAME from all_constraints where table_name = 'PROMOTIONS'
        i'm going to dig for a bit, post any relevant constraints here if you would..
[reply]
[d/l]
[select]
Re: OT: Oracle SQL issues
by runrig (Abbot) on Sep 13, 2001 at 19:58 UTC
    We may have to see exactly what SQL you're passing to DBI, placeholders and all, and what fields you're binding to the placeholders. You can't, e.g., replace 'SYSDATE' with a placeholder and bind the string 'SYSDATE' to that field.
[reply]

Back to Seekers of Perl Wisdom