create or replace function FINDCREATURE(numero CREATURE.CREATUREID%type) return varchar2 is
chaine varchar2(2000);
myCreature Creature%rowtype;
jsonWriter varchar2(500);
type tabOfInt is table of NOVEL.NOVELID%TYPE index by BINARY_INTEGER;
myTabOfInt1 tabOfInt;
myTabOfInt2 tabOfInt;

type tabOfJson is table of varchar2(2000) index by BINARY_INTEGER;
myTabOfJson1 tabOfJson;
myTabOfJson2 tabOfJson;

begin
    select * into myCreature from Creature where CREATUREID = numero;
    jsonWriter:=findwriter(myCreature.writerId);
    chaine:='{ "creatureId" : '||myCreature.creatureId||', "description" : "'||myCreature.description||'", "firstWriter" : '||jsonWriter||', "setOfNovels" : [';
    --On cherche les Novel pour le setOfNovels
    select novelId bulk collect into myTabOfInt1 from APPEARANCE where CREATUREID=numero;
    
    if(myTabOfInt1.count>1)
    then
        FOR i IN myTabOfInt1.FIRST .. myTabOfInt1.LAST LOOP
        myTabOfJson1(i):=findNovel(myTabOfInt1(i));
        END LOOP;
    else
        if(myTabOfInt1.count=1)
        then
        myTabOfJson1(1):=findNovel(myTabOfInt1(1));
        end if;
    end if;
    
    
    if(myTabOfJson1.count>1)
    then
        for i in myTabOfJson1.first .. myTabOfJson1.last-1 loop
            chaine:=chaine||myTabOfJson1(i)||', ';
        end loop;
        chaine:=chaine||myTabOfJson1(myTabOfJson1.last);
    else
        if(myTabOfInt1.count=1)
        then
            chaine:=concat(chaine,myTabOfJson1(1));
        end if;
    end if;
    
    chaine:=concat(chaine,'], "setOfNames" : [ ');

    --On cherche les noms pour setOfNames
    select creatureNameId bulk collect into myTabOfInt2 from CreatureName where creatureId=numero;
    if(myTabOfInt2.count>1)
    then
        FOR i IN myTabOfInt2.FIRST .. myTabOfInt2.LAST LOOP
        myTabOfJson2(i):=findCreatureName(myTabOfInt2(i));
        END LOOP;
    else
        if(myTabOfInt2.count=1)
        then
            myTabOfJson2(1):=findCreatureName(myTabOfInt2(1));
        end if;
    end if;
    
    if(myTabOfJson2.count>1)
    then
        for i in myTabOfJson2.first .. myTabOfJson2.last-1 loop
            chaine:=chaine||myTabOfJson2(i)||', ';
        end loop;
        chaine:=chaine||myTabOfJson2(myTabOfJson2.last);
    else
        if(myTabOfInt2.count=1)
        then
            chaine:=concat(chaine,myTabOfJson2(1));
        end if;
    end if;
    
    chaine:=concat(chaine, '] }');
    return chaine;
end;