I need to use a SELECT - JOIN sentence on a stored procedure PL/SQL

Question

I had a problem with Oracle SQL. I need to use this PL/SQL sentence on a stored procedure:

select t.ID_CURSO,p.DESCRIPCION,p.FECHA_INICIO,p.FECHA_FIN 
      from curso t 
      inner join contrato c on t.ID_CURSO = c.curso_id_curso
      inner join PAQUETE_ACTIVIDADES p on 
            c.ID_PAQUETE_ACTIVIDADES_ID = p.ID_PAQUETE_ACTIVIDADES
where id_curso=value;

I need to show the data of this select query How can I do that? I'm new at this Thanks for your help

Answer 1

If you are using Oracle 12c, you could use DBMS_SQL.RETURN_RESULT.

CREATE OR REPLACE PROCEDURE pr_show_output
AS
   l_cursor   SYS_REFCURSOR;
BEGIN
   OPEN l_cursor FOR
      SELECT t.ID_CURSO,
             p.DESCRIPCION,
             p.FECHA_INICIO,
             p.FECHA_FIN
        FROM curso t
             INNER JOIN contrato c ON t.ID_CURSO = c.curso_id_curso
             INNER JOIN PAQUETE_ACTIVIDADES p
                ON c.ID_PAQUETE_ACTIVIDADES_ID = p.ID_PAQUETE_ACTIVIDADES
       WHERE id_curso = VALUE;

   DBMS_SQL.return_result (l_cursor);
END;
/