Oracle Stored Procedure and Cursor

Question

I'm trying to write a stored procedure to transform this:

|----------|----------|----------|----------|----------|
|    ID    |   ESD    |  TD      |   IS_DB  | TEST_SET |
|----------|----------|----------|----------|----------|
|    1     |  10      |  20      |    1     |    2     |
|    2     |  30      |  (null)  |    1     |    2     |
|    3     |  40      |  (null)  |    1     |    2     |
|    4     |  50      |  60      |    0     |    2     |
|    5     |  (null)  |  70      |    1     |    2     |
|    6     |  75      |  100     |    1     |    2     |
|    7     |  (null)  |  80      |    1     |    2     |
|----------|----------|----------|----------|----------|

to this:

|----------|----------|
|  DT      |  FLAG    |
|----------|----------|
|  10      |  E       |
|  20      |  H       |
|  30      |  E       |
|  40      |  E       |
|  50      |  E       |
|  60      |  S       |
|  70      |  H       |
|  75      |  E       |
|  80      |  H       |
|  100     |  H       |
|----------|----------|

The business rules being:

For each row in the TEST_DATA_SOVLP:

1. If the current ESD value is not null then:

   • insert a row in TEMP with the values: <ESD value>, E

2. If the current TD value is not null then:

   • If IS_DB=0 => insert in TEMP the values: <TD value>, S
   • If IS_DB=1 => insert in TEMP the values: <TD value>, H

but I'm not getting anything close to what I'm expecting:

• Nothing get written in the TEMP table.

• The console display values that make no sense to me:

  20 H 10 E 20 H 10 E 20 H ...

Questions :

1. Could someone tell me what I'm doing wrong in my proc ?

2. Why no data are stored in TEMP ?

3. What could be a cleaner way to solve this problem ? I tried using a SQL query (see here) without success. Note: I have multiple similar proc to write that I would like then to call from a 'master' proc that will summarize the logic.

Thank you

1. Table storing some data

CREATE TABLE "TEST_DATA_SOVLP" 
   (    "ID" NUMBER,
   "ESD" NUMBER, 
    "TD" NUMBER, 
    "IS_DB" NUMBER(1,0) DEFAULT 0, 
    "TEST_SET" NUMBER
   )

Some data

INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB, TEST_SET) VALUES ('1', '10', '20', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, IS_DB, TEST_SET) VALUES ('2', '30', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, IS_DB, TEST_SET) VALUES ('3', '40', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB, TEST_SET) VALUES ('4', '50', '60', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, TD, IS_DB, TEST_SET) VALUES ('5', '70', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB,TEST_SET) VALUES ('6', '75', '100', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, TD, IS_DB, TEST_SET) VALUES ('7', '80', '1', '2');

2. Table to store results

  CREATE TABLE "TEMP" 
   (    "DT" NUMBER, 
    "FLAG" VARCHAR2(1 BYTE)
   ) 

3. PL/SQL manipulating the data and storing the results

CREATE OR REPLACE PROCEDURE S_OVLP 

AS 

   CURSOR cSH IS
      SELECT ID, ESD, TD, IS_DB, TEST_SET
      FROM TEST_DATA_SOVLP
      WHERE TEST_SET = 2;

    rec_csh cSH%ROWTYPE;

BEGIN

 -- DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TEMP');

  OPEN cSH;    

    LOOP

        FETCH cSH INTO rec_csh;
        EXIT WHEN cSH%NOTFOUND;

        IF rec_csh.esd IS NOT NULL THEN
            INSERT INTO TEMP VALUES (rec_csh.esd, 'E');
            dbms_output.put_line(rec_csh.esd || ' E');
        END IF;

        IF rec_csh.td IS NOT NULL THEN 
            IF rec_csh.is_db = 1 THEN     
                INSERT INTO TEMP VALUES (rec_csh.td, 'H');
                dbms_output.put_line(rec_csh.td || ' H');
            ELSE
                INSERT INTO TEMP VALUES (rec_csh.td, 'S');
                dbms_output.put_line(rec_csh.td || ' S');
            END IF;
        END IF;

    END LOOP;

  CLOSE cSH;

END S_OVLP;

Answer 1

From your business rules, it seems you can do it just with two inserts, like these:

insert into temp
select esd, 'E' from TEST_DATA_SOVLP where test_set=2 and esd is not null;

insert into temp
select td, decode(is_db, 1, 'H', 'S') from TEST_DATA_SOVLP 
     where test_set=2 and td is not null;

Besides the conditions on esd and td, the selects also “embed” the condition on test_set, given by the cursor in your procedure.

The decode compares is_db with 1 and if matches, use 'H', otherwise uses 'S' (so 2 would give the same result as 0; but then I suppose you need to check your data, since you have defined what to give only for 1 or 0).

About the procedure

I've tested it on https://livesql.oracle.com and the output looks fine:

10 E
20 H
30 E
40 E
50 E
60 H
70 H
75 E
100 H
80 H

(The table has all is_db = 1, see the inserts you gave in the question).

Also, the temp table is populated accordingly. So, the problem can't be replicated and your original procedure seems to work fine as expected.

Answer 2

Just run this INSERT INTO , there is no need of procedure.

INSERT INTO temp 
SELECT esd, 
       'E' 
FROM   test_data_sovlp 
WHERE  esd IS NOT NULL 
  UNION ALL 
SELECT td, 
       CASE is_db 
         WHEN 0 THEN 'S' 
         WHEN 1 THEN 'H' 
       END AS FLAG 
FROM   test_data_sovlp 
WHERE  td IS NOT NULL 

Demo

Answer 3

Well, I finally got it right with the code below, changing the nested IF statement. But I'm really disappointed with the way PL/SQL work.

CREATE OR REPLACE PROCEDURE S_OVLP 

AS 

   CURSOR cSH IS
      SELECT ID, ESD, TD, IS_DB, TEST_SET
      FROM TEST_DATA_SOVLP
      WHERE TEST_SET = 2;

    rec_csh cSH%ROWTYPE;

BEGIN

  DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TEMP');

  OPEN cSH;    

    LOOP

        FETCH cSH INTO rec_csh;
        EXIT WHEN cSH%NOTFOUND;

        IF rec_csh.esd IS NOT NULL THEN
            INSERT INTO TEMP VALUES (rec_csh.esd, 'E');
            dbms_output.put_line(rec_csh.esd || ' E');
        END IF;

        IF rec_csh.td IS NULL THEN 
            CONTINUE;
        END IF;

        IF rec_csh.is_db = 1 THEN     
            INSERT INTO TEMP VALUES (rec_csh.td, 'H');
            dbms_output.put_line(rec_csh.td || ' H');
        ELSE
            INSERT INTO TEMP VALUES (rec_csh.td, 'S');
            dbms_output.put_line(rec_csh.td || ' S');
        END IF;

    END LOOP;

  CLOSE cSH;

END S_OVLP;