Pasted

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;