Re: Store multiple values in a variable

Joe wrote:
> Hi Everyone,
>
> 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

Look into using Advanced Queuing (AQ):

Morgan's Library at www.psoug.org.

You seem to be working hard to reinvent the wheel. A messaging implementation shouldn't take more than one day to implement.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)