Store multiple values in a variable

From: Joe <balu422_at_yahoo.com>
Date: 29 Mar 2006 11:36:32 -0800
Message-ID: <1143660992.512768.104320_at_i40g2000cwc.googlegroups.com>

[Quoted] Hi Everyone,

[Quoted] I have a field called is_message in the procedure and it will be populated based on the criteria in the procedure but the thing is since it is in the loop each time the message is overwritten and when the output is printed - there is only one value. I need a way to collect all the messages and output them together in one variable(os_message). I know how to do it in a dbms_output.putline but I need it in a variable called os_message.
Example:
You are about to overwrite an existing table: DSC P&G
TLC P&G
All stored in one variable.
My procedure is as follows:
CREATE OR REPLACE PROCEDURE JW_CREATE_ADVER_CUSTOMDAYPARTS

  (in_template_id IN dci_template_custom_dp_hdr.template_id%TYPE,
   ib_override IN BOOLEAN,
   on_status OUT NUMBER,
   os_message OUT VARCHAR2)

IS
  is_message VARCHAR2(100) := null;
  is_status NUMBER := 0;

/******************************************************************************
   NAME:       JW_CREATE_ADVER_CUSTOMDAYPARTS

   PURPOSE: Custom Dayparts 2006 upfront Enhancement.

   REVISIONS:
   Ver Date Author Description

• ---------- ---------------

  ---

  1.0 3/22/2006 Balu Custom Dayparts 2006 upfront Enhancement.

   NOTES:    Automatically available Auto Replace Keywords:

      Object Name:     JW_CREATE_ADVER_CUSTOMDAYPARTS
      Sysdate:         3/22/2006
      Date and Time:   3/22/2006, 12:31:09 PM, and 3/22/2006 12:31:09
PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

BEGIN
  FOR crec IN (SELECT dtdc.template_id, dtcd.agency_id, dtcd.start_year_num, dtcd.start_qtr_num,

            dtcd.end_year_num, dtcd.end_qtr_num, dtcd.daypart_name, dtcd.DAYPART_START_DT,

          dtcd.DAYPART_END_DT, dtcd.AIR_BYTE, dtcd.START_TM_SECS, dtcd.END_TM_SECS,

	  gn.call_sign, gn.net_id, ga.adver_id, ga.name
	FROM dci_template_daypart_combo dtdc, dci_template_custom_daypart
dtcd,
   	  ge_net gn,
   	  ge_advertisers ga,
   	  dci_template_custom_dp_hdr dth
	WHERE dtcd.template_id = in_template_id
   	  and dtdc.template_id = dtcd.template_id
   	  and gn.net_id = dtdc.net_id
   	  and ga.adver_id = dtdc.adver_id
   	  and dth.template_id = dtcd.template_id
   	ORDER BY gn.call_sign,
   	  gn.name)

  LOOP
    IF ib_override = FALSE THEN

            FOR creca IN (SELECT adver_id, net_id, start_year_num, start_qtr_num, end_year_num, end_qtr_num

    	  FROM dci_advertiser_custom_daypart
	  WHERE adver_id = crec.adver_id
	    AND net_id = crec.net_id
	    AND ((((start_year_num * 100) + start_qtr_num) >=


((crec.start_year_num * 100) + crec.start_qtr_num)


	    AND ((start_year_num * 100) + start_qtr_num) <=


((crec.end_year_num * 100) + crec.end_qtr_num))


    	      OR (((end_year_num * 100) + end_qtr_num) >=


((crec.start_year_num * 100) + crec.start_qtr_num)


	    AND ((end_year_num * 100) + end_qtr_num) <= ((crec.end_year_num *
100) + crec.end_qtr_num))))
	LOOP -- if overlap exists
	  is_status := -1;
	  is_message := 'Call Sign: ' || crec.call_sign || 'Advertiser Name: '
|| crec.name;
	END LOOP;

    END IF;
    IF is_status = 0 THEN

• insert records INSERT into dci_advertiser_custom_daypart (custom_daypart_id, adver_id, net_id, daypart_start_dt, daypart_end_dt, air_byte, start_tm_secs, end_tm_secs) VALUES (dci_advertiser_daypart_seq.NEXTVAL, crec.adver_id, crec.net_id, crec.daypart_start_dt, crec.daypart_end_dt, crec.air_byte, crec.start_tm_secs, crec.end_tm_secs); END IF; os_message := is_message; on_status := is_status; END LOOP; END JW_CREATE_ADVER_CUSTOMDAYPARTS;

/

Could you please
help. Your effort is appreciated.
Thanks,

Balu Received on Wed Mar 29 2006 - 21:36:32 CEST