Re: PL/SQL Help....

This example would be similar to one that Govindan send, however it uses TABLE instead of VARRAY - which is not creating artificial limit of records to be selected. Also it demonstrates BULK COLLECT - to fast populate collection.


CREATE OR  REPLACE TYPE SomeObjType AS OBJECT (
   Col1 VARCHAR2(10),
   Col2 VARCHAR2(10),
)
/
CREATE OR REPLACE TYPE SomeColType AS TABLE OF SomeObjType 
/

CREATE OR REPLACE PACKAGE BODY pkg_rpt
AS
 PROCEDURE PR_RTN_DETAIL(in_s1 IN varchar2,-- start date
              in_e1 IN varchar2,-- end date
              in_s2 IN varchar2,-- start date + release_time/24
              in_e2 IN varchar2,-- end date + release_time/24 
              in_acct IN varchar2,
              in_subacct IN varchar2,
              out_array OUT SomeColType ) IS
BEGIN
  SELECT SomeObjType (col1, col2) BULK COLLECT INTO out_array FROM some_table
    WHERE ...




Regards
Mindaugas





----- Original Message ----
From: GovindanK <gkatteri_at_fastmail.fm>
To: bunjibry_at_gmail.com; Oracle-L <oracle-l_at_freelists.org>
Sent: Tuesday, January 30, 2007 1:39:41 PM
Subject: Re: PL/SQL Help....


Hello Bryan
Here is a sample 
CREATE OR  REPLACE TYPE Batch_RecTyp AS OBJECT (
     BAT_SYS_NO                   NUMBER(8)
    ,BAT_STATUS                   VARCHAR2(10)
    ,BAT_SENT_DT                  VARCHAR2(10)
)
/
CREATE OR REPLACE TYPE Batch_RecList AS VARRAY(5000) OF Batch_RecTyp
/
CREATE OR REPLACE PACKAGE process_patch
AS
TYPE me_cursor  IS REF CURSOR;
TYPE  Batch_RecTyp_r IS RECORD (
     BAT_SYS_NO                   NUMBER(8)
    ,BAT_STATUS                   VARCHAR2(10)
    ,BAT_SENT_DT                  VARCHAR2(10)
) ;
TYPE Batch_CurTyp                      IS REF CURSOR RETURN Batch_RecTyp_r                ;
PROCEDURE p_save_batch(i_batch_det                  IN batch_RecList
                      ,o_err_code                  OUT number
                      ,o_err_mesg                  OUT varchar2
                     );
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY process_batch
AS
PROCEDURE p_save_batch(i_batch_det                  IN batch_RecList
                      ,o_err_code                  OUT number
                      ,o_err_mesg                  OUT varchar2
                     )
AS
l_first_batch_no      number                               ; --internal use,hence no %TYPE
BEGIN --{
  FOR bat in 1..i_batch_det.COUNT
  LOOP -- {
      IF i_batch_det.EXISTS(bat) THEN -- {
        if bat > 1 then
           o_err_code := 9009;
           o_err_mesg := 'FATAL ERROR .. MORE THAN ONE BATCH NOT ALLOWED AT A TIME';
           return;
        end if;
        if bat = 1 then
            l_first_batch_no := i_batch_det(bat).BAT_SYS_NO ;
        end if;
        /* Do your processing here */
            INSERT INTO BATCHES(BAT_SYS_NO
                                       ,BAT_STATUS
                                       ,BAT_SENT_DT
                                ) VALUES
                                       (i_batch_det(bat).BAT_SYS_NO
                                       ,i_batch_det(bat).BAT_STATUS
                                       ,to_date(i_batch_det(bat).BAT_SENT_DT,'MM/DD/YYYY')
                                       );
      END IF; -- }
  END LOOP;-- }
END; --Package Body
/
show errors

HTH
GovindanK
 
On Tue, 30 Jan 2007 08:34:41 -0700, "Bryan Wells" <bunjibry_at_gmail.com> said:

All,
 
Since my pl/sql is less than stellar, well, way less than that; i was hoping to get some direction on how to pass an array of records back to the calling application?  i have initialized a record type, have been able to write and read from it within the pl/sql package but just cant figure out how to pass it out of the package.  I have been able to pass an array out as a varchar and clob, but cant get the array to distinguish end of record.    Yes I'm a novice ;-) 
 
Here is a snippet of what i have done;
 
CREATE OR REPLACE PACKAGE BODY pkg_rpt
AS
 PROCEDURE PR_RTN_DETAIL(in_s1 IN varchar2,-- start date
              in_e1 IN varchar2,-- end date
              in_s2 IN varchar2,-- start date + release_time/24
              in_e2 IN varchar2,-- end date + release_time/24 
              in_acct IN varchar2,
              in_subacct IN varchar2,
              out_array OUT ?)
              
   --
 /* Cursors to retrieve records for record type to pass back to application */ 
   .
   .
   .
   --
   
   /* create record type */  
   --
   TYPE rpt_detail_type IS RECORD
      (PHN table.phone%TYPE,
       FIRST table.first%TYPE,
       ACCT table.acct%TYPE,
       TRANID table.transaction%TYPE,
       DT date,
       STATUS varchar2(1 BYTE),
       INTCODE0 table.intcode0%TYPE,
       ID table.id%type);
  v_br rpt_detail_type;
   --
   .
   .
   .
   --
 /* populate records here */
Thanks All!  As usual your expertise is much appreciated!

-- 
Bryan S Wells
DBA VoiceLog
Email: bunjibry_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l