Incorrect "Hint: value assigned to '' never used in" message

[Patrick Holmes]

Is there anything I can do to suppress an incorrect "Hint: value assigned to '' never used in" message? I'm trying to have a clean compile without warnings or errors, but I haven't been able to figure out how to suppress this one warning:
"Hint Value assigned to 'v_prev_email' never used in 'uar_ipclink'

Here's the section of code below. I'm using 'v_prev_email' in an If condition, I'm calling two different functions with it, I tried assigning it to itself. Anything else I can try?

--
dbms_output.put_line(v_prev_email);
--
v_err_info := c_prov_rec.region || ',' || c_prov_rec.title || ',' || c_prov_rec.last_name;
v_prov_count := v_prov_count + 1;
--
IF v_prov_count = 1 THEN
--
-- this only happens once, on initial record in cursor
-- initialize variables
v_prev_region := c_prov_rec.region;
v_prev_email := c_prov_rec.to_email;
v_prev_title := c_prov_rec.title;
v_line_count := 1;
-- start new email
start_ED_email(c_prov_rec.region, c_prov_rec.to_email);
-- column headings
v_message := v_message || c_prov_header;
--
ELSIF c_prov_rec.region != v_prev_region THEN
--
-- end of region, so close-and-send email, then start new email
IF v_prev_email IS NOT NULL THEN
send_ED_email(v_prev_email);
END IF;
start_ED_email(c_prov_rec.region, c_prov_rec.to_email);
-- column headings
v_message := v_message || c_prov_header;
-- reset within-email variables to start new email (the line # counter, the first title)
v_line_count := 1;
v_prev_title := c_prov_rec.title;
--
ELSE
v_line_count := v_line_count + 1;
END IF;
--

 

[Hilarion]

This code sample suggests that indeed you do not use the value assigned to "v_prev_email". Your sample does assign a value and does use the variable value but the assignment is in one branch of "IF" statement and it's value use is in another branch ("ELSIF"). Unless all that code is in some kind of loop (including use of "GOTO"), then the assignment (in the first branch of "IF") is obsolete and the code will work the same with it and without it and that's why you get that hint. If that's so, then simply remove that assignment (or comment it out).

 

[Patrick Holmes]

Thank you for taking the time to try to understand this. However, your response doesn't make sense to me because (a) I am using the value in an IF statement (which controls program logic) and (b) I am using the value as an argument in a procedure call which then does something very important with that value. I don't understand why either or both (a) and (b) don't count as "using" the value. I need the value, I use it, the program will break without it, so I don't know why that doesn't count as "using" it. So here I think is the real question:
What does this "value...not being used" hint look for? What qualifies as a "use"?
(note: I have another variable being used in a similar way in the same section of code in the same locations/scope: v_prev_title. v_prev_title is *only* used in an IF condition and is not passed as an argument to a procedure. So v_prev_email is actually being used *more* than v_prev_title. Yet, v_prev_email generates the "not being used" hint while v_prev_title does not. That suggests to me that I've found some kind of bug.)

 

[Patrick Holmes]

P.S. I do also use the variable outside a loop you can't see. I will post the entire procedure here. If the forum strips out white space (which I think it does), you can just paste the procedure into a SQL window and use the [great!] "Edit/"PLSQL Beautifier" feature to make it readable again. So here is the full procedure:

--
--
PROCEDURE uar_ipclink (p_test IN VARCHAR2 DEFAULT 'Y') AS
--
-- created to perform an IPC-Link user review:
-- A) First, send a "documentation" email to VP-Technology and VP-IS to document how the review was done:
-- 1) document the steps performed to conduct the review; this code is a procedure in a package in database
-- and will be included as an attachment with the first email (A.2.a)
-- 2) simple lists of IPC-Link usernames at the time of the review (A.2.a and A.2.b are in separate emails)
-- a) complete list of active IPC-Link usernames
-- b) (hopefully) empty list of ACTIVE IPC-Link usernames MINUS usernames being emailed
-- c) list of IPC-Link usernames being reviewed
-- B) sends emails to a reviewer for each region of IPC-Link usernames, with a list/subset of usernames to review
-- C) THIS IS A MANUAL STEP! The reviewer (e.g. Pat or Nane) will also need to document that the review was completed successfully
-- To accomplish this, he will repeatedly remind reviewers to return their responses. Once all of the
-- responses have been received, the reviewer will send an email to VP-Tech, VP-IS confirming review is complete.
--
--
v_subject VARCHAR2(200);
v_message CLOB;
v_source_code CLOB;
v_prov_count INTEGER;
v_line_count INTEGER;
v_prev_region casem.regions.region%TYPE;
v_err_info VARCHAR2(150) := NULL;
--
c_reviewer CONSTANT VARCHAR2(100) := 'Fred Jamkotchian ' ;
-- c_reviewer CONSTANT VARCHAR2(100) := 'Pat Holmes ' ; -- testing
c_developer CONSTANT VARCHAR2(100) := 'Pat Holmes ' ;
c_closing CONSTANT VARCHAR2(300) := 'Thank you for your time and patience,' ||
'Pat Holmes, VP Technology
' ||
'on behalf of
' ||
'Pat Holmes, VP Technology
' ||
'Fred Citron, VP Information Systems' ;
--
PROCEDURE start_ED_email (p_region IN VARCHAR2, p_to_email IN VARCHAR2) IS
BEGIN
--
v_subject := 'Please confirm these providers for ' || p_region || ' region';
--
-- build header for email:
--
-- logo:
-- v_message := '

';
-- no logo:
v_message := '';
--
v_message := v_message ||
'You are receiving this email because you are the Executive Director for the ' || p_region || ' region. (' || REPLACE(REPLACE(p_to_email,'') || ')'
|| 'It is imperative that you respond so that we can provide this documentation to our auditors. Please read below and then reply to the email.'
|| 'All of the providers below are ACTIVE IPC-Link users. The last three columns show how many encounters each provider has billed in the last 7 days, last 90 days and last 365 days.'
|| 'Our SOX auditors require that we ask you to confirm whether all of these providers should remain ACTIVE IPC-Link users. See the list below:
'
|| ' * If all of the providers below should remain active, just reply with "KEEP ALL".
'
|| ' * If some of these providers can be removed, please indicate which ones to remove by putting the word "Remove" in the "Remove?" column for those that can be deactivated. Leave the first column blank if the provider should stay active.'
|| 'Note that for any providers that we remove, we will need the appropriate termination PAN or form if one doesn''t exist already. We will follow up with your staff if we need anything.'
|| 'Note also that our SOX auditors require that we do this twice-annually, once in June and once in December.'
;
END;
--
PROCEDURE send_ED_email (p_to_email IN VARCHAR2) IS
BEGIN
--
v_message := v_message || '[/TABLE]' || c_closing;
--
v_message := v_message || '';
--
IF p_to_email IS NOT NULL THEN
--
admin.email_pkg.set_email_server;
IF p_test = 'Y' THEN
admin.send_email_pkg.send(p_sender => c_reviewer,
p_recipients => c_developer,
p_bcc => c_developer,
p_subject => v_subject,
p_message => v_message,
p_mime_type => 'text/html');
ELSE
admin.send_email_pkg.send(p_sender => c_reviewer,
-- p_recipients => c_developer, -- s/b p_to_email, -- use this only for Testing
p_recipients => p_to_email,
p_bcc => c_developer,
p_subject => v_subject,
p_message => v_message,
p_mime_type => 'text/html');
END IF;
ELSE
dbms_output.put_line('>: missing to-email: ');
END IF;
END;
--
BEGIN
--
-- dbms_output.put_line (p_test);
--
--
/*
dbms_output.put_line('remember to turn on "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"');
dbms_output.put_line('remember to turn on "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"');
dbms_output.put_line('remember to turn on "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"');
dbms_output.put_line('remember to turn on "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"');
dbms_output.put_line('remember to turn on "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"');
*/
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- to guarantee read-consistency from beginning to end:
-- just in case the helpdesk adds or drops a user while this is running
-- this also requires a ROLLBACK or COMMIT at the end (I'll use a Rollback since this email does not change anything anyway):
--
--
--
--
--
DECLARE
c_header CONSTANT VARCHAR2(500) :=
'

#' || '[TH]Reg#' || '[TH]Region' || '[TH]Title' || '[TH]Last Name' || '[TH]First name' || '[TH]Username' || '
' || TO_CHAR(v_prov_count) || '	' || TO_CHAR(v_line_count) || '	' || u_rec.region || '	' || u_rec.title || '	' || u_rec.last_name || '	' || u_rec.first_name || '	' || u_rec.username || '

'
|| ' '
|| ' [TH]';
BEGIN
--
--
-- A: send the documentation emails first
--
--
-- A.1: add source code as an attachment to the A.2.a email
-- A.2.a: show list of all ACTIVE IPC-Link usernames
--
--
--
v_subject := 'Documentation email for IPC-Link UAR: A.1, A.2.a: ALL Active IPC-Link usernames';
--
v_message := '';
--
--
BEGIN
v_message := v_message ||
'A.1: See attachment for the source code for entire package, but only uar_ipclink is called for the IPC-Link UAR:';
--
SELECT TRIM(dbms_metadata.get_ddl('PACKAGE', 'UAR_PKG', 'PHOLMES'))
INTO v_source_code
FROM dual;
-- v_message := v_message || v_source_code;
END;
--
--
v_message := v_message ||
'A.2.a: List of ALL IPC-Link usernames at the time the UAR process was executed:';
--
-- column heading:
v_message := v_message || c_header;
--
v_line_count := 0;
v_prov_count := 0;
FOR u_rec IN ( SELECT hp.region, NVL(TRIM(hppgcv.title),'(full-time)') title,
hp.last_name, hp.first_name, hp.username
FROM casem.hubs h, casem.health_prof_pg_current_view hppgcv,
casem.health_profs hp
WHERE hppgcv.health_prof_id = hp.health_prof_id
AND hp.hospitalist_flag = 'Y'
AND hppgcv.status = 'ACTIVE'
AND hp.username IS NOT NULL
AND hp.last_name != 'TEST'
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
ORDER BY 1, 2, 3, 4
) LOOP
v_line_count := v_line_count + 1;
v_prov_count := v_prov_count + 1;
IF v_prov_count = 1 THEN
v_prev_region := u_rec.region;
ELSIF v_prev_region != u_rec.region THEN
v_line_count := 1;
END IF;
-- dbms_output.put_line(u_rec.username);
--
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
v_message := v_message || ' ';
--
v_prev_region := u_rec.region;
END LOOP;
--
v_message := v_message || '

';
-- dbms_output.put_line(' ');
--
-- now close and send email:
--
v_message := v_message || c_closing;
v_message := v_message || '';
--
admin.email_pkg.set_email_server;
admin.send_email_pkg.send_attach_clob(p_sender => c_reviewer,
p_recipients => c_developer,
p_bcc => c_developer,
p_subject => v_subject,
p_message => v_message,
p_attachment => v_source_code,
p_att_filename => 'UAR_source_code.txt',
p_mime_type => 'text/html');
--
--
--
-- A.2.b: documentation email: show that the ((All ACTIVE usernames) MINUS (Usernames being mailed to EDs)) is an empty list
-- A.2.c: documentation email: show list of all IPC-Link usernames that are being emailed to EDs
--
--
v_subject := 'Documentation email for IPC-Link UAR: A.2.b, A.2.c: Active users being emailed to EDs';
--
v_message := '';
--
v_message := v_message ||
'A.2.b: ((All ACTIVE usernames) MINUS (Usernames being mailed to EDs)) is an empty list:';
--
-- column heading:
v_message := v_message || c_header;
--
-- A.3.a:
v_line_count := 0;
v_prov_count := 0;
FOR u_rec IN ( ( SELECT hp.region, NVL(TRIM(hppgcv.title),'(full-time)') title,
hp.last_name, hp.first_name, hp.username
FROM casem.hubs h, casem.health_prof_pg_current_view hppgcv,
casem.health_profs hp
WHERE hppgcv.health_prof_id = hp.health_prof_id
AND hp.hospitalist_flag = 'Y'
AND hppgcv.status = 'ACTIVE'
AND hp.username IS NOT NULL
AND hp.last_name != 'TEST'
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
)
MINUS
( SELECT hp.region, NVL(TRIM(hppgcv.title),'(full-time)') title,
hp.last_name, hp.first_name, hp.username
FROM pholmes.employees_in_adp e, casem.gl_health_profs glhp,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp,
( SELECT eb.billing_health_prof_id,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 7),1,0,1))) count_charges_7_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 90),1,0,1))) count_charges_90_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service-365),1,0,1))) count_charges_365_days
FROM casem.patients p, casem.patient_encounters pe, casem.encounter_billings eb
WHERE p.patient_id = pe.patient_id
AND pe.encounter_id = eb.encounter_id
AND p.last_name != 'TEST'
AND eb.date_of_service > SYSDATE - 365
AND eb.cpt_cd != 0
GROUP BY eb.billing_health_prof_id ) b,
( SELECT DISTINCT hp.region, rm.region_group,
DECODE(rm.region_group,'PETERSON, FRED','Fred Peterson ',
'PETERSON, FREDDY','Fred Peterson ',
INITCAP(iu.first_name || ' ' || iu.last_name) || ' ' to_email
FROM casem.gl_health_profs glhp, casem.ipcm_users iu, casem.region_mapping rm,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp
WHERE hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND rm.region = hp.region
AND iu.last_name(+) = UPPER(rm.region_group)
AND iu.title(+) = 'ED'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
AND h.ipc_flag = 'Y'
ORDER BY hp.region ) r
WHERE r.region(+) = hp.region
AND b.billing_health_prof_id(+) = hp.health_prof_id
AND e.emplid(+) = DECODE(casem.is_number(glhp.hp_clock),1,TO_NUMBER(glhp.hp_clock),NULL)
AND hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
)
ORDER BY 1,2,3,4
) LOOP
v_line_count := v_line_count + 1;
v_prov_count := v_prov_count + 1;
IF v_prov_count = 1 THEN
v_prev_region := u_rec.region;
ELSIF v_prev_region != u_rec.region THEN
v_line_count := 1;
END IF;
v_message := v_message || '[TR]';
v_message := v_message || '[TD]' || TO_CHAR(v_prov_count) || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(v_line_count) || '[/TD]';
v_message := v_message || '[TD]' || u_rec.region || '[/TD]';
v_message := v_message || '[TD]' || u_rec.title || '[/TD]';
v_message := v_message || '[TD]' || u_rec.last_name || '[/TD]';
v_message := v_message || '[TD]' || u_rec.first_name || '[/TD]';
v_message := v_message || '[TD]' || u_rec.username || '[/TD]';
v_message := v_message || '[/TR]';
--
v_prev_region := u_rec.region;
END LOOP;
--
v_message := v_message || '[/TABLE]';
--
v_message := v_message ||
'A.2.c: List of ALL IPC-Link usernames that are being emailed to EDs:';
--
-- column heading:
v_message := v_message || c_header;
--
v_line_count := 0;
v_prov_count := 0;
FOR u_rec IN ( SELECT hp.region, NVL(TRIM(hppgcv.title),'(full-time)') title,
hp.last_name, hp.first_name, hp.username
FROM pholmes.employees_in_adp e, casem.gl_health_profs glhp,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp,
( SELECT eb.billing_health_prof_id,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 7),1,0,1))) count_charges_7_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 90),1,0,1))) count_charges_90_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service-365),1,0,1))) count_charges_365_days
FROM casem.patients p, casem.patient_encounters pe, casem.encounter_billings eb
WHERE p.patient_id = pe.patient_id
AND pe.encounter_id = eb.encounter_id
AND p.last_name != 'TEST'
AND eb.date_of_service > SYSDATE - 365
AND eb.cpt_cd != 0
GROUP BY eb.billing_health_prof_id ) b,
( SELECT DISTINCT hp.region, rm.region_group,
DECODE(rm.region_group,'PETERSON, FRED','Fred Peterson ',
'PETERSON, FREDDY','Fred Peterson ',
INITCAP(iu.first_name || ' ' || iu.last_name) || ' ' to_email
FROM casem.gl_health_profs glhp, casem.ipcm_users iu, casem.region_mapping rm,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp
WHERE hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND rm.region = hp.region
AND iu.last_name(+) = UPPER(rm.region_group)
AND iu.title(+) = 'ED'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
AND h.ipc_flag = 'Y'
ORDER BY hp.region ) r
WHERE r.region(+) = hp.region
AND b.billing_health_prof_id(+) = hp.health_prof_id
AND e.emplid(+) = DECODE(casem.is_number(glhp.hp_clock),1,TO_NUMBER(glhp.hp_clock),NULL)
AND hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
ORDER BY 1,2,3,4
) LOOP
v_line_count := v_line_count + 1;
v_prov_count := v_prov_count + 1;
IF v_prov_count = 1 THEN
v_prev_region := u_rec.region;
ELSIF v_prev_region != u_rec.region THEN
v_line_count := 1;
END IF;
v_message := v_message || '[TR]';
v_message := v_message || '[TD]' || TO_CHAR(v_prov_count) || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(v_line_count) || '[/TD]';
v_message := v_message || '[TD]' || u_rec.region || '[/TD]';
v_message := v_message || '[TD]' || u_rec.title || '[/TD]';
v_message := v_message || '[TD]' || u_rec.last_name || '[/TD]';
v_message := v_message || '[TD]' || u_rec.first_name || '[/TD]';
v_message := v_message || '[TD]' || u_rec.username || '[/TD]';
v_message := v_message || '[/TR]';
--
v_prev_region := u_rec.region;
END LOOP;
--
v_message := v_message || '[/TABLE]';
--
-- now close and send email:
--
v_message := v_message || c_closing;
v_message := v_message || '';
--
admin.email_pkg.set_email_server;
admin.send_email_pkg.send(p_sender => c_reviewer,
p_recipients => c_developer,
p_bcc => c_developer,
p_subject => v_subject,
p_message => v_message,
p_mime_type => 'text/html');
END;
--
--
--
--
--
--
-- B.1 now send providers to be approved by the EDs
--
DECLARE
--
-- send emails to ED for each region
--
v_prev_email VARCHAR2(50);
v_prev_title VARCHAR2(20);
c_prov_header CONSTANT VARCHAR2(600) :=
'

#' || '[TH]Remove?' || '[TH]Title' || '[TH]Last Name' || '[TH]First Name' || '[TH]Username' || '[TH]Start Date' || '[TH]# Encs 7 days' || '[TH]# Encs 90 days' || '[TH]# Encs 365 days' || '

'
|| ' '
|| ' [TH]';
--
-- IPC-Link usernames to be reviewed
-- note the "p_region" parameter: we will send one email per region (e.g. if an ED is ED for five regions, that person will get five emails)
--
CURSOR c_prov IS
SELECT hp.region, NVL(TRIM(hppgcv.title),'(full-time)') title,
hp.last_name, hp.first_name, hp.username, hppgcv.actual_start_date,
NVL(b.count_charges_7_days,0) count_charges_7_days,
NVL(b.count_charges_90_days,0) count_charges_90_days,
NVL(b.count_charges_365_days,0) count_charges_365_days,
glhp.hp_clock, e.emplid, e.it_uid, e.emplstatus, hppgcv.status,
hppgcv.start_date pg_start_date, hppgcv.pg_id, hp.health_prof_id,
r.region_group, r.to_email
FROM pholmes.employees_in_adp e, casem.gl_health_profs glhp,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp,
( SELECT eb.billing_health_prof_id,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 7),1,0,1))) count_charges_7_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service- 90),1,0,1))) count_charges_90_days,
SUM(DECODE(p.patient_id,NULL,0,DECODE(SIGN(TRUNC(SYSDATE)-eb.date_of_service-365),1,0,1))) count_charges_365_days
FROM casem.patients p, casem.patient_encounters pe, casem.encounter_billings eb
WHERE p.patient_id = pe.patient_id
AND pe.encounter_id = eb.encounter_id
AND p.last_name != 'TEST'
AND eb.date_of_service > SYSDATE - 365
AND eb.cpt_cd != 0
GROUP BY eb.billing_health_prof_id ) b,
( SELECT DISTINCT hp.region, rm.region_group,
DECODE(rm.region_group,'PETERSON, FRED','Fred Peterson ',
'PETERSON, FREDDY','Fred Peterson ',
INITCAP(iu.first_name || ' ' || iu.last_name) || ' ' to_email
FROM casem.gl_health_profs glhp, casem.ipcm_users iu, casem.region_mapping rm,
casem.hubs h, casem.health_prof_pg_current_view hppgcv, casem.health_profs hp
WHERE hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND rm.region = hp.region
AND iu.last_name(+) = UPPER(rm.region_group)
AND iu.title(+) = 'ED'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
AND h.ipc_flag = 'Y'
ORDER BY hp.region ) r
WHERE r.region(+) = hp.region
AND b.billing_health_prof_id(+) = hp.health_prof_id
AND e.emplid(+) = DECODE(casem.is_number(glhp.hp_clock),1,TO_NUMBER(glhp.hp_clock),NULL)
AND hppgcv.health_prof_id = hp.health_prof_id
AND glhp.health_prof_id(+) = hp.health_prof_id
AND h.hub = hp.hub
AND h.ipc_flag = 'Y'
AND hp.last_name != 'TEST'
AND hp.hospitalist_flag = 'Y'
AND hp.username IS NOT NULL
AND hppgcv.status = 'ACTIVE'
-- AND hp.region IN ('ALA','AUS','CHI','DEN') -- test, test, test!
ORDER BY 1,2,3,4 ;
BEGIN
--
v_prov_count := 0;
FOR c_prov_rec IN c_prov LOOP
--
dbms_output.put_line(v_prev_email);
--
v_err_info := c_prov_rec.region || ',' || c_prov_rec.title || ',' || c_prov_rec.last_name;
v_prov_count := v_prov_count + 1;
--
IF v_prov_count = 1 THEN
--
-- this only happens once, on initial record in cursor
-- initialize variables
v_prev_region := c_prov_rec.region;
v_prev_email := c_prov_rec.to_email;
v_prev_title := c_prov_rec.title;
v_line_count := 1;
-- start new email
start_ED_email(c_prov_rec.region, c_prov_rec.to_email);
-- column headings
v_message := v_message || c_prov_header;
--
ELSIF c_prov_rec.region != v_prev_region THEN
--
-- end of region, so close-and-send email, then start new email
IF v_prev_email IS NOT NULL THEN
send_ED_email(v_prev_email);
END IF;
start_ED_email(c_prov_rec.region, c_prov_rec.to_email);
-- column headings
v_message := v_message || c_prov_header;
-- reset within-email variables to start new email (the line # counter, the first title)
v_line_count := 1;
v_prev_title := c_prov_rec.title;
--
ELSE
v_line_count := v_line_count + 1;
END IF;
--
IF c_prov_rec.title != v_prev_title THEN
v_message := v_message || '

';
v_message := v_message || c_prov_header || '
';
END IF;
-- now display data for current row:
BEGIN
/*
dbms_output.put(' --> ');
dbms_output.put_line(c_prov_rec.region
|| ',' || c_prov_rec.title
|| ',' || c_prov_rec.last_name
|| ',' || c_prov_rec.first_name
|| ',' || c_prov_rec.username
|| ',' || c_prov_rec.status
|| ',' || TO_CHAR(c_prov_rec.actual_start_date,'MM/DD/YYYY')
|| ',' || TO_CHAR(c_prov_rec.pg_start_date,'MM/DD/YYYY')
|| ',' || c_prov_rec.count_charges_7_days
|| ',' || c_prov_rec.count_charges_90_days
|| ',' || c_prov_rec.count_charges_365_days );
*/
--
v_message := v_message || '[TR]';
v_message := v_message || '[TD]' || TO_CHAR(v_line_count) || '[/TD]';
v_message := v_message || '[TD]' || ' ' || '[/TD]';
v_message := v_message || '[TD]' || c_prov_rec.title || '[/TD]';
v_message := v_message || '[TD]' || c_prov_rec.last_name || '[/TD]';
v_message := v_message || '[TD]' || c_prov_rec.first_name || '[/TD]';
v_message := v_message || '[TD]' || c_prov_rec.username || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(c_prov_rec.actual_start_date,'MM/DD/YYYY') || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(c_prov_rec.count_charges_7_days) || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(c_prov_rec.count_charges_90_days) || '[/TD]';
v_message := v_message || '[TD]' || TO_CHAR(c_prov_rec.count_charges_365_days) || '[/TD]';
v_message := v_message || '[/TR]';
END;
v_prev_region := c_prov_rec.region;
v_prev_email := c_prov_rec.to_email;
v_prev_title := c_prov_rec.title;
-- next region
END LOOP c_prov_rec;
--
-- send email after last provider
send_ED_email(v_prev_email);
--
dbms_output.put_line('v_prov_count= ' || TO_CHAR(v_prov_count));
--
END;
--
--
--
--
--
--
--
-- even though no data changed, need to rollback if using "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"
ROLLBACK;
--
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Region,Title,LastName: ' || v_err_info || ': ' || SQLERRM || ': ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
admin.event_log_pkg.log_event(p_user_name => USER,
p_program_name => 'PHOLMES.UAR_PKG.UAR_IPCLINK',
p_event_desc => 'Region,Title,LastName: ' || v_err_info || ': ' || SQLERRM || ': ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END; -- uar_ipclink
--

 

[Hilarion]

The "value...not being used" hint tells you that you have assigned a value to a variable and that value will not be used.
What can be considered a use of a variable? As far, as I know, practically anything that is not an assignment to that variable (maybe apart of passing the variable as an "out" argument to a procedure/function).

It looks like in this case PL/SQL Developer is right.
If "v_prov_count" is equal to 1, you assign "c_prov_rec.to_email" to "v_prev_email" but then you are not using the "v_prev_email" variable at all (it's use is in different - second - "IF" branch). It gets assigned again the same value (just before the end of the LOOP), so the previous assignment was unnecessary (it's result does not influence anything).

The "v_prev_title" is a different case. It is assigned a value ("c_prov_rec.title") in the same conditions as "v_prev_email", but what's different is that "v_prev_title" is then used ("IF c_prov_rec.title != v_prev_title THEN") before it gets assigned again, so the assignment can't be considered unnecessary.

I hope this helps you. English is not my native language, so it's possible that what I wrote is not exactly what I meant to express. Sorry for the inconvenience.

 

[Patrick Holmes]

Thanks for your feedback. I think I've proved below that this is a bug. I know you don't have time to decipher my logic, but there are valid logical reasons why both "identical" assignments are necessary and if either one of them is missing, then the program will not function correctly.

Here is why I think this is a bug (Marco: are you listening?):

If I add this unproductive but valid IF-ENDIF right after the assignment, within the "if" branch, then the hint goes away:
IF v_prev_email IS NOT NULL THEN
NULL;
END IF;

I shouldn't have to do that because I have a separate IF-ENDIF within the ELSE of the outer IF-ELSIF-ENDIF, just a few lines lower:
IF v_prev_email IS NOT NULL THEN
send_ed_email(v_prev_email);
END IF;

Note that the scope of a variable should matter. If I use the variable anywhere within scope, then that should satisfy the "not being used" hint. I should not have to use it within just the *same* IF part of an IF-ELSE-ENDIF. (That doesn't even make sense--the reason we use IF logic in programs is that we can choose to do things at different times...) In this case, not only am I using the variable in a number of different locations, but I am even using it within the very same outer IF-ELSE-ENDIF.

 

[Hilarion]

I still think that PL/SQL Developer behaved correctly in your case and my previous explanation is valid.
Maybe it'll be easier if we use a bit simplified version of your code. It will not work/compile but it still has all the logic that influences the use of "v_prev_email" variable.

01: procedure uar_ipclink as
02:   v_prev_email  varchar2(50);
03:   v_prov_count  integer;
04:   v_prev_title  varchar2(20);
05:   v_prev_region casem.regions.region%type;
06: begin
07:   v_prov_count := 0;
08:
09:   for c_prov_rec in c_prov loop
10:     dbms_output.put_line(v_prev_email);
11:
12:     v_prov_count := v_prov_count + 1;
13:
14:     if v_prov_count = 1 then
15:       v_prev_region := c_prov_rec.region;
16:       v_prev_email := c_prov_rec.to_email;
17:       v_prev_title := c_prov_rec.title;
18:       v_line_count := 1;
19:     elsif c_prov_rec.region != v_prev_region then
20:       if v_prev_email is not null then
21:         send_ED_email(v_prev_email);
22:       end if;
23:       start_ED_email(c_prov_rec.region, c_prov_rec.to_email);
24:       v_prev_title := c_prov_rec.title;
25:     end if;
26:
27:     v_prev_region := c_prov_rec.region;
28:     v_prev_email := c_prov_rec.to_email;
29:     v_prev_title := c_prov_rec.title;
30:   end loop c_prov_rec;
31:
32:   send_ED_email(v_prev_email);
33:
34: end uar_ipclink;

When determining if a value assigned to a variable can be used by the code or is never used (as in this case), we have to consider all code paths (which in this case include loop iterations and "if" branches) that are connected to the considered assignment. This means all the code that can be executed after that assignment is executed but before next assignment to that same variable is made.
I believe that in your case, the PL/SQL Developer is hinting you that the value assigned in line 16 is never used, so I'll concentrate on that one.
If you look at that code, you'll see that the "v_prev_email" variable is used (not assigned a value) in lines 20, 21 and 32 and assigned a value in line 28 (beside line 16). What we have to consider is if it's possible to reach (in code execution) lines 20, 21 or 32 from line 16 without first reaching line 28.
To reach line 16, the condition specified in line 14 has to be met. If it is met, then the whole code branch between lines 19 and 25 can't be reached in the same loop iteration. Which means we can't reach lines 20 and 21 in that iteration. This means that between line 16 and 28 we do not have any code that uses the value assigned in line 16.
What about line 32? Not much. It does not influence our analysis at all. In case there are no loop iterations ("c_prov" cursor returns no rows), the line 16 is never reached. In case there are any loop iterations, they all will reach line 28 before reaching line 32.
So in all cases, the value assigned in line 16 is overwritten by another assignment (line 28) before the variable is used. This means that this assignment value is never used (as the hint states) so that assignment is unnecessary.

You wrote that when you insert "if v_prev_email is not null then null; end if;" somewhere between line 16 and 19, the hint goes away. Of course it does. That "trick" code uses the assigned value and the PL/SQL Developer does not evaluate if that usage makes any sense or not (in this case it does not). That way you tricked the PL/SQL Developer to "think" that the obsolete code (line 16) really does possibly influence the way that the code works.

 

[mike]

I looked at it also and it looks to me that Hilarion is correct.

When v_prov_count = 1. You assign v_prev_email in the if statement. After the if statement, you assign v_prev_email again.

When v_prov_count >= 2. This is the only time that v_prev_email can be used.

You can remove the assignment to v_prev_email within the if statement and it will still have the same value the second time through the loop, because you assign it at the end of the loop on the first iteration. It will work the same. Try it!

 

[Gustavo]

Indeed. v_prov_count is not reset within the loop. It means that the assignment within "if v_prov_count = 1" will only happen once. This time, however, this value will not be used within the "elsif" and the assignment will end up discarded by the end of the "loop". The assignment that happens in the end of the loop is the one that counts when v_prov_count > 1. You can safely remove the 1st assignment and debug your program. You'll notice nothing changes in functionality.

Regards,
Gustavo