CODEWITHSUNDEEP
oracle-databasestored-procedures
Shamnad K M
Shamnad K M

Reputation: 35

How to skip insert to an Oracle table in procedure where specified columns going to insert are null

I'm inserting lots of rows into a table and some of the columns are blank for some of the rows.

How can I skip insert if some important fields are blank? for example there is a table 'people' and my important fields are name,cityName and age.

   1 INSERT INTO people VALUES('customerid1','name', 'cityName', 50, 'anotherValue')
   2 INSERT INTO people VALUES('customerid2','', '', '' , 'anotherValue')
   3 INSERT INTO people VALUES('customerid3','name', 'cityName', 20, 'anotherValue')
   4 INSERT INTO people VALUES('customerid4','name', 'cityName', 19, 'anotherValue')

here 2nd row name,cityName and age are blank. if those three fields are blank then dont insert that row.this is just an example i have more fields to check so need to avoid 'if condition' to check blank or not.

another example

FUNCTION TEST_FUN (increment_i in VARCHAR2, increment_j IN VARCHAR2,mod_id IN VARCHAR2 ) 
      RETURN numeric IS
          j_val VARCHAR2(100);
          i_val VARCHAR2(100);
      BEGIN
          i_val := increment_i;
          j_val := increment_j;
          IF mod_id != 'loop' THEN
            j_val := i_val;
          END IF;

          INSERT
          INTO TEST.testpartytable
            (
              reffer_id,
              customer_id,
              customer_joint,
              fullname,
              nature,
              counter_bus,
              country,
              status
            )
            VALUES
            (
              REFFER_ID_AR,
              CUSTOMER_ID_ARR(i_val),
              CUSTOMER_JOINT,
              LEGALNAME_KBC_ARR(i_val),
              NATURERE_KBC_ARR(j_val),
              COUNTERBUSACT_KBC_ARR(j_val),
              COUNTRY_KBC_ARR(j_val),
              STATUS
            );
          return i_val;
      END TEST_FUN ;

skip insert if 'fullname,nature,counter_bus,country' fields are blank .Datas coming from colletion.

Any help is appreciated.

Thanks!

Upvotes: 1

Views: 779

Answers (2)

eifla001
eifla001

Reputation: 1157

You can apply a NOT NULL constraint to your important columns so that when any of them assigned with a NULL value an error will be raised, specifically ORA -1400 (cannot insert null) exception. You can then catch this exception in your program and just do nothing when this exception is raised.

Sample below,

CREATE TABLE TEST_TABLE
(col1 NUMBER NOT NULL, col2 NUMBER NOT NULL);

DECLARE
    CANNOT_INSERT_NULL EXCEPTION;
    PRAGMA EXCEPTION_INIT(cannot_insert_null, -1400);
    num NUMBER;
BEGIN
    FOR i IN 1..10 LOOP
        num := 2;
        IF i BETWEEN 3 AND 5 THEN
            num := NULL; //since this is null, insert statement below will error and handled and won't be inserted
        END IF;
        BEGIN
            INSERt INTO test_table
            (col1,col2)
            VALUES
            (i, num);
        EXCEPTION
            WHEN CANNOT_INSERT_NULL THEN
                NULL;
        END;
    END LOOP;
END;
/

SELECT *
  FROM test_table;

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

well you can check first if the values are null or not null:

declare CHECK_VAL varchar2(100);
BEGIN
select CUSTOMER_ID_ARR(i_val) into CHECK_VAL from dual;
if(i_val is not null) then
insert...
end if;
END;

You can alternavely make the column to not null, and raise exeception when you get error for a value not null.

Upvotes: 1

Related Questions