Re: Store multiple values in a variable
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Wed, 29 Mar 2006 11:45:43 -0800
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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.
- References:
- Store multiple values in a variable
- From: Joe
- Store multiple values in a variable
- Prev by Date: Re: Seding mail forms using outlook with attachment
- Next by Date: Running reports from Forms-application in 10g
- Previous by thread: Store multiple values in a variable
- Next by thread: Re: Store multiple values in a variable
- Index(es):
Relevant Pages
|
