I have a question about how to call several stored procedures inside another in Oracle. I tried to call them in the following way, but I only run the first one. Does anyone know how I can make the call for all three to run? I am using Oracle PL / SQL. The query if it works, and run it separately.
PROCEDURE SP_NOMBRESP(P_EXITO OUT NUMBE
P_ERROR OUT VARCHAR2)
IS
V_CURSOR SYS_REFCURSOR;
V_EXITO1 NUMBER;
V_ERROR1 VARCHAR(200);
BEGIN
OPEN V_CURSOR FOR
SELECT * FROM
(SELECT NUMERO FROM
TABLA1, TABLA2
WHERE
TABLA1.PK = TABLA2.PK
AND NOT (ABLA2.DATO = 1
AND TABLA2.DATO2 != 'X'
AND ABLA1.DATO3 = 'ALGO'))
UNION
(SELECT TABLA3.DATO FROM
TABLA3, TABLA2
WHERE
NOT (TABLA2.DATO1 = 1
AND TABLA2.DATO2 != 'X'
AND TABLA3 = 'ALGO')
AND TXWV152_TABLA3.PK = TABLA2.PK);
PG_PAQUETE.MY_SP1(V_CURSOR, V_EXITO1, V_ERROR1);
PG_PAQUETE.MY_SP2(V_CURSOR, V_EXITO1, V_ERROR1);
PG_PAQUETE.MY_SP3(V_CURSOR, V_EXITO1, V_ERROR1);
PG_PAQUETE.MY_SP4(V_CURSOR, V_EXITO1, V_ERROR1);
P_EXITO := 0;
P_ERROR := 'EJECUCIÓN EXITOSA';
EXCEPTION
WHEN OTHERS THEN
P_EXITO := 1;
P_ERROR := SQLERRM;
END SP_NOMBRESP;