Reg: Change in Query

Question

I have a requirement in oracle forms to write a PLSQL block that would read from the database through a cursor that contains comma separated record. I have completed the logic but wanted to check if this logic which I have developed can be improved in any way.

DECLARE
CURSOR vest_hist (p_ind_id NUMBER,
                  p_plan_id NUMBER)
IS  
SELECT narrative
  FROM dummy_ev_tbld
 WHERE subject_id = p_ind_id
   AND sub_subject_id = p_plan_id
   AND evty_code = 'VEDC'
 ORDER BY dpdate_time;
    
 v_narrative dummy_ev_tbld.narrative%TYPE;
 v_hire_date VARCHAR2(11);
 v_ev_id VARCHAR2(10);
 v_step_seqnbr VARCHAR2(3);
 v_credited_hours VARCHAR2(10);
 BEGIN
 OPEN vest_hist(123,
                123);
 LOOP -- looping for the child ga_id's
 FETCH vest_hist INTO v_narrative;
 EXIT WHEN vest_hist%notfound;   
 v_hire_date := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,3)+1,INSTR(v_narrative,',',1,4)-INSTR(v_narrative,',',1,3)-1),'N/A');
 v_ev_id := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,4)+1,INSTR(v_narrative,',',1,5)-INSTR(v_narrative,',',1,4)-1),'N/A');
 v_step_seqnbr := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,5)+1,INSTR(v_narrative,',',1,6)-INSTR(v_narrative,',',1,5)-1),'N/A');
 v_credited_hours := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,9)+1,INSTR(v_narrative,',',1,10)-INSTR(v_narrative,',',1,9)-1),'N/A');`your text`
         /*DO SOMETHING with these variables......*/
 END LOOP;
 EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('E-1526-0004-0038-'||SQLCODE||':Contact Systems');
 RAISE FORM_TRIGGER_FAILURE;    
 END;

So I am using the substr and instr to split the narrative data and then storing them in the respective variable. So is there any way I can do it in one query or logic?

The v_narrative data: 14-JUL-2019,92149,1,10

Answer 1

There are a few things you could pay attention to.

The first one is that code you posted won't work because of NVL function calls. One of local variables is DATE, the rest of them are NUMBERs and you can't put N/A into any of them because that's a string.

Furthermore, are you sure that - for sample narrative value (14-JUL-2019,92149,1,10) your substrings return correct values? You're searching for e.g. 9th or 10th appearance of a comma character in it. There aren't that many commas there.

As of exception handler: although it won't fail, it won't do anything because Oracle Forms is incapable of displaying output of dbms_output.put_line. Use message built-in (call it twice so that is displayed in a pop-up window on the screen, or switch to alert.

How to simplify that code? Get rid of explicitly declared cursor, cursor variable, local variables, opening the cursor (and closing it - which is what you failed to do), worrying about exiting the loop and switch to a cursor for loop.

Here's a suggestion which shows alternative way of doing it.

 begin
   for cur_r in 
     (select to_date(substr(narrative, 1, 11), 'dd-mon-yyyy', 'nls_date_language = english') hire_date,
       substr(narrative, instr(narrative, ',', 1, 1) + 1,
                   instr(narrative, ',', 1, 2) - instr(narrative, ',', 1, 1) - 1
             ) ev_id,
       substr(narrative, instr(narrative, ',', 1, 2) + 1,
                   instr(narrative, ',', 1, 3) - instr(narrative, ',', 1, 2) - 1
             ) step_seqnbr,
       substr(narrative, instr(narrative, ',', 1, 3) + 1) credited_hours             
      from dummy_ev_tbld
   ) loop
     null;
     -- do something with values returned by cursor. Reference them as e.g.
     -- if cur_r.hire_date is null then ...
  end loop;
exception
  when others then
    message('E-1526-0004-0038-'||SQLCODE||':Contact Systems');
    raise form_trigger_failure;
end;
/