Handle NULL within Oracle PL/SQL

Question

Data is imported into data_import table through sql insert statements. These insert statements may not be the most accurate so I want to let all data in to the table (hence no PK to prevent duplicates)

The code checks the contents of the data_import table for duplicate instances using a count on occurrences any occurrences are mark as in error.

DECLARE
   CURSOR C_DUPLICATE_IMPORT_IDS
   IS
        SELECT COUNT (D.LEARNER_ID), D.LEARNER_ID
          FROM ILA500.DATA_IMPORT D
         WHERE     D.USER_ID IN (SELECT S.OSUSER
                                   FROM V$SESSION S
                                  WHERE S.SID IN (SELECT DISTINCT V.SID
                                                    FROM V$MYSTAT V))
               AND D.ERROR_FLAG = 'N'
               AND D.LEARNER_ID <> NULL
        HAVING COUNT (D.LEARNER_ID) > 1
      GROUP BY D.LEARNER_ID;


   V_DUPLICATE_IMPORT_IDS   C_DUPLICATE_IMPORT_IDS%ROWTYPE;
BEGIN
   OPEN C_DUPLICATE_IMPORT_IDS;

   LOOP
      FETCH C_DUPLICATE_IMPORT_IDS INTO V_DUPLICATE_IMPORT_IDS;

      EXIT WHEN C_DUPLICATE_IMPORT_IDS%NOTFOUND;

      UPDATE ILA500.DATA_IMPORT D
         SET D.ERROR_FLAG = 'Y'
       WHERE D.LEARNER_ID = V_DUPLICATE_IMPORT_IDS.LEARNER_ID;

      UPDATE ILA500.DATA_IMPORT D
         SET D.IMPORT_NOTIFICATION =
                   'DUPLICATE LEARNER_ID IDENTIFIED ('
                || V_DUPLICATE_IMPORT_IDS.LEARNER_ID
                || ').  LEARNER_IDS ERROR_FLAG SET, THIS CASE WILL NOT IMPORT UNTIL CORRECTED.'
       WHERE D.LEARNER_ID = V_DUPLICATE_IMPORT_IDS.LEARNER_ID;

      IF V_DUPLICATE_IMPORT_IDS.LEARNER_ID IS NOT NULL
      THEN
         DBMS_OUTPUT.PUT_LINE (
               'THE FOLLOWING LEARNER WAS IDENTIFIED AS A DUPLICATE '
            || V_DUPLICATE_IMPORT_IDS.LEARNER_ID);
            ELSE 
            DBMS_OUTPUT.PUT_LINE (
            CHR (10)
         || 'THERE ARE NO DUPLICATE LEARNER_IDS WITHIN THIS UPLOAD.');

      END IF;
      
   END LOOP;
   
      CLOSE C_DUPLICATE_IMPORT_IDS;
         DBMS_OUTPUT.PUT_LINE (CHR (10) || 'STEP 1 COMPLETED');
   COMMIT;
END;

My problem comes within the IF statement

IF V_DUPLICATE_IMPORT_IDS.LEARNER_ID IS NOT NULL
      THEN
         DBMS_OUTPUT.PUT_LINE (
               'THE FOLLOWING LEARNER WAS IDENTIFIED AS A DUPLICATE '
            || V_DUPLICATE_IMPORT_IDS.LEARNER_ID);
            ELSE 
            DBMS_OUTPUT.PUT_LINE (
            CHR (10)
         || 'THERE ARE NO DUPLICATE LEARNER_IDS WITHIN THIS UPLOAD.');

If the count is greater than 1 then data is returned and the

DBMS_OUTPUT.PUT_LINE ('THE FOLLOWING LEARNER WAS IDENTIFIED AS A DUPLICATE '|| DUPLICATE_IMPORT_IDS.LEARNER_ID);

statement successfully outputs the line .

However if a null count is returned within the query the

ELSE (DBMS_OUTPUT.PUT_LINE (
            CHR (10)
         || 'THERE ARE NO DUPLICATE LEARNER_IDS WITHIN THIS UPLOAD.');)

doesn't output the line.

How to get a dmbs_output to produce data from a null?

user_266304 · Answer

However if a null count is returned ...

A null count can't be returned. The COUNT (D.LEARNER_ID) in your cursor query can't evaluate to null; it could produce zero but you're filtering any such results with your HAVING clause (which would also exclude null, if it could happen). And a null ID can't be returned because of how you are counting the values.

Your cursor query includes:

AND D.LEARNER_ID <> NULL

which isn't right; null isn't equal or unequal to anything, so this excludes all rows; you could do:

AND D.LEARNER_ID IS NOT NULL

but it's redundant anyway because you're doing COUNT(D.LERARNER_ID), which won't count nulls. It would make a difference if you were doing COUNT(*) though. db<>fiddle with just the cursor query, showing that effect.

Anyway... if you wanted to display a message for each ID if that had no duplicates you could remove the HAVING clause and test for non-zero instead of not-null; but from the text it looks like you want a single message if your cursor finds no rows at all. You can handle that with a 'found' flag which you set to true if you go into the cursor loop, and then test after the loop:

DECLARE
   CURSOR C_DUPLICATE_IMPORT_IDS
   IS
...

   V_DUPLICATE_IMPORT_IDS   C_DUPLICATE_IMPORT_IDS%ROWTYPE;

   V_FOUND BOOLEAN := FALSE;
BEGIN
   OPEN C_DUPLICATE_IMPORT_IDS;

   LOOP
      FETCH C_DUPLICATE_IMPORT_IDS INTO V_DUPLICATE_IMPORT_IDS;

      EXIT WHEN C_DUPLICATE_IMPORT_IDS%NOTFOUND;
...

      DBMS_OUTPUT.PUT_LINE (
            'THE FOLLOWING LEARNER WAS IDENTIFIED AS A DUPLICATE '
         || V_DUPLICATE_IMPORT_IDS.LEARNER_ID);

      V_FOUND := TRUE;
   END LOOP;
   
   CLOSE C_DUPLICATE_IMPORT_IDS;

   IF NOT V_FOUND THEN
      DBMS_OUTPUT.PUT_LINE (
         CHR (10)
         || 'THERE ARE NO DUPLICATE LEARNER_IDS WITHIN THIS UPLOAD.');

   END IF;

   DBMS_OUTPUT.PUT_LINE (CHR (10) || 'STEP 1 COMPLETED');
   COMMIT;
END;

db<>fiddle with and without matching data.

You could also simplify this a bit with an implicit cursor loop but the 'found' logic would be the same.

user_4818540 · Answer

In the past, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. the only way around this problem was to process each row individually. There's a DBMS_ERRLOG package that provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and rollback. This enables you to save time and system resources.

You can use this package and set the primary key in your table. For example:

-- First, create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'dest_table');
END;

Then in your insert staement do this:

INSERT INTO dest_table
SELECT *
FROM   source_table
LOG ERRORS INTO err$_dest_table ('INSERT') REJECT LIMIT UNLIMITED;

Finally, You don't need these codes.

For more information read here and here.

Handle NULL within Oracle PL/SQL

2 Answers