PL/SQL - Error when declaring a cursor with tables filled by other cursors

Question

I have a problem when trying to create a cursor that uses tables filled after calling two other cursors, Oracle raised ORA-00942 exception - table or view does not exist. I tried to initialize the tables but it didnt change anything...

The idea was to recover buying prices(tsc.prxtar when tsc.achvte='A) and selling prices(tsc.prxtar when tsc.achvte='V') from a table, storing the results in two different tables and joining them at the end.

Does someone has any advice? Maybe an easier, more efficient way?

Thanks in advance!

DECLARE

CURSOR cursorA IS
    SELECT pro.codpro as CodeProduit,
            pro.nompro as NomProduit,
            tsc.prxtar as PrixAchat
    FROM pro
    INNER JOIN tsc ON pro.codpro=tsc.codpro
    WHERE tsc.achvte='A';
TYPE tableA IS TABLE OF cursorA%ROWTYPE;
tabA tableA:=tableA();

CURSOR cursorV IS
    SELECT pro.codpro as CodeProduit,
            pro.nompro as NomProduit,
            tsc.prxtar as PrixVente
    FROM pro
    INNER JOIN tsc ON pro.codpro=tsc.codpro
    WHERE tsc.achvte='V';
TYPE tableV IS TABLE OF cursorV%ROWTYPE;
tabV tableV:=tableV();

CURSOR cursorAV IS
    SELECT tabA.CodeProduit,
            tabA.NomProduit,
            tabA.PrixAchat,
            tabV.PrixVente
    FROM tabA
    INNER JOIN tabV ON tabA.CodeProduit=tabV.CodeProduit;
                   -- AND tabA.NomProduit=tabB.NomProduit;
TYPE tableAV IS TABLE OF cursorAV%ROWTYPE;
tableauDesPrix tableAV:=tableAV();

BEGIN
OPEN cursorA;
FETCH cursorA BULK COLLECT INTO tabA;
CLOSE cursorA;

OPEN cursorV;
FETCH cursorV BULK COLLECT INTO tabV;
CLOSE cursorV;

OPEN cursorAV;
FETCH cursorAV BULK COLLECT INTO tableauDesPrix;
CLOSE cursorAV;

END;

Answer 1

"Does someone has any advice? Maybe an easier, more efficient way?"

Why not write one SELECT statement which joins PRO to TSC twice?

SELECT pro.codpro  as CodeProduit,
       pro.nompro  as NomProduit,
       tsca.prxtar as PrixAchat,
       tscv.prxtar as PrixVente
FROM       pro
INNER JOIN tsc tsca ON pro.codpro = tsca.codpro
INNER JOIN tsc tscv ON pro.codpro = tscv.codpro
WHERE tsca.achvte = 'A'
AND   tscv.achvte = 'V';

SQL is optimised for joins. It is more efficient to do everything in Plain Old SQL whenever possible. (There are edge cases where we might choose to do something in PL/SQL even though we could do it in SQL, but not here.)