Re: PL/SQL Help....

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Tue, 30 Jan 2007 18:39:41 UT
Message-Id: <1170182381.28676.1172019427@webmail.messagingengine.com>

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: [1]bunjibry_at_gmail.com

References

1. mailto:bunjibry_at_gmail.com


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

Received on Tue Jan 30 2007 - 12:39:41 CST