title: How to pass a user-defined record from a procedure back to the calling program in pl/sql?
body: <p>The package below will insert 10 records into a table, then I need to pass those records back to the calling program and display them. I've tried different things that I could think of and the many searches over the internet, haven't been successful in finding what I'm trying to do. </p>
  
  <p>PACKAGE SPECIFICATION:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE Hospital AS 
  
    TYPE TrtRecTyp IS RECORD (
        Pat_Nbr Treatment.Pat_Nbr%TYPE, 
        Trt_Procedure Treatment.Trt_Procedure%TYPE,
        Phys_ID Physician.Phys_ID%TYPE,
        Phys_Name Physician.Phys_Name%TYPE,
        Phys_Specialty Physician.Phys_Specialty%TYPE);          
  
   PROCEDURE BuildPatTbl                                                 
    (p_trt_rec OUT TrtRecTyp,
     p_num_rows IN OUT NUMBER);
  </code></pre>
  
  <p>PACKAGE BODY:</p>
  
  <pre><code>PROCEDURE BuildPatTbl                                                 
    (p_trt_rec OUT TrtRecTyp,
     p_num_rows IN OUT NUMBER) AS   
  
      CURSOR pat_cur  IS    
      SELECT trt.Pat_Nbr, Trt_Procedure, trt.Phys_ID, Phys_Name, Phys_Specialty   
      FROM Treatment trt, Physician phys 
      WHERE trt.Phys_ID = phys.Phys_ID;    
  
      lv_rec_cnt_num NUMBER(2);
  
  BEGIN
        lv_rec_cnt_num := p_num_rows;
        FOR lv_trt_rec IN pat_cur  LOOP  
           INSERT INTO t_patTrt (T_patTrt_ID, Pat_Nbr, Trt_Procedure, 
                   Phys_ID, Phys_Name,  Phys_Specialty)  
           VALUES(T_patTrt_ID_seq.NEXTVAL, lv_trt_rec.Pat_Nbr,
                   lv_trt_rec.Trt_Procedure, lv_trt_rec.Phys_ID, 
                   lv_trt_rec.Phys_Name, lv_trt_rec.Phys_Specialty);
  
             lv_rec_cnt_num := lv_rec_cnt_num + 1 ;         
  
        END LOOP;     
  
        p_num_rows := lv_rec_cnt_num;
  
  END BuildPatTbl;
  </code></pre>
  
  <p>CALLING PROGRAM:</p>
  
  <pre><code>DECLARE
  treat_rec Hospital.TrtRecTyp;
  lv_cnt_num NUMBER(2) := 0;
  lv_row_cnt_num NUMBER(2);
  
  BEGIN    
  
     i Hospital.BuildPatTbl(treat_rec, lv_cnt_num);     
  
      FOR i IN 1..10 LOOP
  
             DBMS_OUTPUT.PUT_LINE('Pat_Nbr = ' || treat_rec.Pat_Nbr);
             DBMS_OUTPUT.PUT_LINE('Trt_Procedure = ' || treat_rec.Trt_Procedure);
             DBMS_OUTPUT.PUT_LINE('Phys_ID = ' || treat_rec.Phys_ID);
             DBMS_OUTPUT.PUT_LINE('Phys_Name = ' || treat_rec.Phys_Name);
             DBMS_OUTPUT.PUT_LINE('Phys_Specialty = ' || treat_rec.Phys_Specialty);   
  
      END LOOP;
  
      DBMS_OUTPUT.PUT_LINE('The number of rows returned in the table is: ' || lv_cnt_num);
  
  END;
  </code></pre>