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

To add to what Frank mentioned in his post.

If you are assigning a value to a variable in a loop, instead of appending/concatenating to it as per Frank's post, then when you break out of your loop, you will only see the value of the last iteration.

Having said that, you have set the is_message variable to varchar2(100)... you can set it to max varchar2(32767). Unless you know that you won't exceed this limit in your loop and run into "numeric or value error", you should add an exception block to handle it.

Regards
/Rauf Received on Thu Mar 30 2006 - 14:52:45 CEST