create or replace FUNCTION FINDNOVEL(numero NOVEL.NOVELID%type) RETURN VARCHAR2 IS 
v_Return varchar2(2000);
chaine varchar2(2000);
myNovel Novel%rowtype;
writerJson varchar2(500);
TYPE tabCritJson IS TABLE OF varchar2(200) index by BINARY_INTEGER;
type indexCrit is record(us Critic.userId%type, nov Critic.novelId%type);
TYPE tabCritInt is table of indexCrit index by BINARY_INTEGER;
myTabOfJson tabCritJson;
myTabOfInt tabCritInt;
begin
    select * into myNovel from Novel where novelId = numero;

    writerJson:=findwriter(myNovel.writerId);
    chaine:='{ "novelId" : '||myNovel.novelId||', "title" : "'||myNovel.title||'", "year" : '||myNovel.publishingYear||', "writer" : '||writerJson||', "synopsis" : "'||myNovel.synosis||'", "setOfCritics" : [';
    select userId, novelId bulk collect into myTabOfInt from CRITIC where novelId=numero;
    
    if(myTabOfInt.count>1)
    then
        FOR i IN myTabOfInt.FIRST .. myTabOfInt.LAST LOOP
            myTabOfJson(i):=findCritic(myTabOfInt(i).us, myTabOfInt(i).nov);
        END LOOP;
    else
        if(myTabOfInt.count=1)
        then
            myTabOfJson(1):=findCritic(myTabOfInt(1).us, myTabOfInt(1).nov);
        end if;
    end if;
    
    
    if(myTabOfJson.Count>1)
    then
        for i in myTabOfJson.first .. myTabOfJson.last-1 loop
            chaine:=chaine||myTabOfJson(i)||', ';
        end loop;
        chaine:=chaine||myTabOfJson(myTabOfJson.last);
    else
        if(myTabOfJson.Count=1)
        then
            chaine:=chaine||myTabOfJson(1);
        end if;
    end if;
   
    chaine:=chaine||'] }';

    return chaine;
end;