Call several stored procedures inside another

1

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;
    
asked by MMora 08.02.2017 в 18:58
source

1 answer

-1

If what you want is to execute other stored procedures from a parent, to put it one way, you must add the following line of code within the main SP:

EXECUTE IMMEDIATE 'BEGIN SP_1; END; '; EXECUTE IMMEDIATE 'BEGIN SP_2; END; '; EXECUTE IMMEDIATE 'BEGIN SP_3; END; ';

You must handle the exceptions well in case someone throws you an error and you can easily correct it.

If you want to join 3 stored procedures in one, you must put the code of these, if it is not difficult that we can help you.

    
answered by 01.03.2017 в 16:14