Recursive PL/SQL pipelined Function CALL cause ORA-00603

Question

I am new in PL/SQL, and i want to write pipelined function that extract ventilation detail of a payment (Master table). Here The Code

CREATE OR REPLACE FUNCTION F_GetImputationsReglement(Pregid Number)  RETURN 
ImputationsReglementTable PIPELINED IS

BEGIN
DECLARE
ImputationRow  Regimputation%ROWTYPE;
 type cc is REF CURSOR RETURN ImputationRow%ROWTYPE;
 cur0 cc;
 CurrentRow   ImputationRow%ROWTYPE  ;
 out_rec      ImputationReglementRow := ImputationReglementRow (null, 
null,null, null,null);
 vquery VARCHAR2(1000);
 l_result ImputationReglementRow;
BEGIN
OPEN cur0 FOR
select * from regimputation WHERE regid = pregid;
  loop
  Fetch cur0 into CurrentRow;
  EXIT WHEN cur0%NOTFOUND;
  IF CurrentRow.RIMSENS = 'C' OR CurrentRow.RIMSENS IS NULL THEN
     IF CurrentRow.facid IS NOT NULL OR (CurrentRow.facid IS NULL AND 
    CurrentRow.RIMLETTRAGE IS NULL AND CurrentRow.RUBID IS NOT NULL) THEN
        out_rec.REGID         := CurrentRow.REGID;
        out_rec.FACID         := CurrentRow.FACID;
        out_rec.RIMMT         := CurrentRow.RIMMT;
        out_rec.FECORDRE      := CurrentRow.FECORDRE;
        out_rec.RUBID         := CurrentRow.RUBID;
        PIPE ROW(out_rec);
     ELSE
        IF CurrentRow.facid IS NULL AND CurrentRow.RIMLETTRAGE is NOT null 
and CurrentRow.RUBID IS NOT NULL THEN
          vQuery := 'select * from 
table(F_GetImputationsReglement(f_getREGID('''|| CurrentRow.RIMLETTRAGE 
||''')))';
          EXECUTE IMMEDIATE vquery into l_result;
           PIPE ROW(l_result);
         END IF; 
    END IF;
  END IF;  
  END LOOP;
  CLOSE cur0;
 RETURN;
   END;
END;

The EXIT Condition of recursivity is :

  IF CurrentRow.RIMSENS = 'C' OR CurrentRow.RIMSENS IS NULL THEN

But when i execute the Query

select * from table(f_getimputationsReglement(696213))

I get the error :

ORA-00603 ORACLE Server Session terminated by fatal error

Does anyone have an idea of what it is?

Thank you.

Internal error on the server. Check database alert.log for more details on the error. — Rene, Apr 06 '18 at 10:57
The server is fine, I have a doubt about the recursive call, i think it overflow the calls stack indefinitly. — Habib Ghrairi, Apr 06 '18 at 11:00
Doesn't hurt to have a look at the alert.log. You might learn something there. — Rene, Apr 06 '18 at 11:20

score 1 · Answer 1 · answered Apr 06 '18 at 12:51

Well if given a chance, i would rewrite your code in a simplified way. I assumed few things and prepared a short code. You can replace with actual table columns. Also, when you call recursion, it might be the case that code gets into infinite loop and never exits and throws error which you get. I share a simple code for instance. See below if you do like below, code will be in infinte recursion.

In this example var is a table of a object.

CREATE OR REPLACE FUNCTION getSoccerLists
  RETURN var
IS
  --Initialization
  LIST VAR :=var();
  list1 VAR :=var();
BEGIN
  SELECT v_var( NSO ,NAME) BULK COLLECT INTO LIST FROM TEST;
  list1:= getSoccerLists;
  RETURN list;
END;

You code rewrite:

Considering you have an object:

CREATE TYPE v_var IS OBJECT
(
col1 NUMBER,
col1 NUMBER,
col1 number
);

Create type ImputationsReglementTable is table of v_var;


CREATE OR REPLACE  FUNCTION F_GetImputationsReglement(  Pregid NUMBER)
    RETURN ImputationsReglementTable PIPELINED
  IS
  BEGIN   
     var  ImputationsReglementTable:=ImputationsReglementTable();
     l_result ImputationsReglementTable:=ImputationsReglementTable();
    BEGIN    
      --Assuming table regimputation has col1 , col2 and col2
      SELECT * 
      BULK COLLECT INTO var
      FROM regimputation 
      WHERE regid = pregid;

      FOR i IN 1..var.count
      loop      
        IF var(i).COL1  = 'C' OR var(i).col1 IS NULL THEN

         IF var(i).col2 IS NOT NULL OR (var(i).col2 IS NULL AND var(i).col1 IS NULL AND var(i).col2 IS NOT NULL) THEN

          PIPE ROW(v_var(var(i)));          

          ELSE
          IF var(i).col1 IS NULL AND var(i).col1 IS NOT NULL AND var(i).col2 IS NOT NULL THEN
            vQuery            := 'select * from table(F_GetImputationsReglement(f_getREGID('''|| var(i).col1 ||''')))';
            EXECUTE IMMEDIATE vquery BULK COLLECT INTO l_result;
            FOR j IN 1..l_result.count
            loop
             PIPE ROW(v_var(l_result(j)));
            end loop;
          END IF;
        END IF;
      END IF;
    END LOOP;   
    RETURN;
  END;
END;

score 0 · Answer 2 · answered Apr 06 '18 at 12:12

Not an answer to your question but I would suggest you use bind variables in your dynamic query. You can rewrite the following to use bind variables:

vquery := 'select * from table(F_GetImputationsReglement(f_getREGID(''' || currentrow.rimlettrage ||
                                ''')))';
 execute immediate vquery
              into l_result;

to

vquery := 'select * from table(F_GetImputationsReglement(f_getREGID(:rimlettrage)))';
execute immediate vquery
            into l_result
           using currentrow.rimlettrage;

not working yet. I forgot to mention That before and afeter your suggestion, It works for a simple case (rimmletrage is null) , but when it is not null, i need to enter into additional levels to extract all details — Habib Ghrairi, Apr 06 '18 at 12:37

score 0 · Answer 3 · answered Apr 06 '18 at 13:31

Thank you XING for your answer, but as i said, i am new in PL/SQL and i did not work with BULK, So i have many syntaxic errors. I have modified my code as follows and it works fine :

CREATE OR REPLACE FUNCTION F_GetImputationsReglement(Pregid Number)  RETURN 
ImputationsReglementTable PIPELINED IS

BEGIN
DECLARE
 Type tmpOut  is record
(
       REGID Number(20),
       FACID Number(20),
       RIMMT NUMBER(12,3),
       FECORDRE Number(3),
       RUBID            Number(3)
);

 ImputationRow  Regimputation%ROWTYPE;
 type cc is REF CURSOR RETURN ImputationRow%ROWTYPE;
 type cc2 is REF CURSOR  return tmpOut;
 cur0 cc;
 cur00 cc2; 
 CurrentRow                          ImputationRow%ROWTYPE  ;
 out_rec                             ImputationReglementRow := 
ImputationReglementRow (null, null,null, null,null);
 CurrentRow2                        tmpOut;                   
BEGIN
OPEN cur0 FOR
select * from regimputation WHERE regid = pregid;
  loop
  Fetch cur0 into CurrentRow;
  EXIT WHEN cur0%NOTFOUND;
  IF CurrentRow.RIMSENS = 'C' OR CurrentRow.RIMSENS IS NULL THEN --Condition de sortie
     IF CurrentRow.facid IS NOT NULL OR (CurrentRow.facid IS NULL AND CurrentRow.RIMLETTRAGE IS NULL AND CurrentRow.RUBID IS NOT NULL) THEN
        out_rec.REGID         := CurrentRow.REGID;
        out_rec.FACID         := CurrentRow.FACID;
        out_rec.RIMMT         := CurrentRow.RIMMT;
        out_rec.FECORDRE      := CurrentRow.FECORDRE;
        out_rec.RUBID         := CurrentRow.RUBID;
        dbms_output.put_line('Facture ' || CurrentRow.FACID);
        PIPE ROW(out_rec);
     ELSE
        IF CurrentRow.facid IS NULL AND CurrentRow.RIMLETTRAGE is NOT null and CurrentRow.RUBID IS NOT NULL THEN
           open cur00 for 
           select * from table(F_GetImputationsReglement(f_getREGID(CurrentRow.RIMLETTRAGE)));
           loop
           Fetch cur00 into CurrentRow2;
           EXIT WHEN cur00%NOTFOUND;
           out_rec.REGID         := CurrentRow2.REGID;
           out_rec.FACID         := CurrentRow2.FACID;
           out_rec.RIMMT         := CurrentRow2.RIMMT;
           out_rec.FECORDRE      := CurrentRow2.FECORDRE;
           out_rec.RUBID         := CurrentRow2.RUBID;
           dbms_output.put_line('Facture ' || CurrentRow2.FACID);
           PIPE ROW(out_rec);
        END LOOP;
        CLOSE cur00;
    END IF;
    END IF;
  END IF;
  END LOOP;
  CLOSE cur0;
 RETURN;
END;
END;

Recursive PL/SQL pipelined Function CALL cause ORA-00603

3 Answers3