ocasionally, in the testing environment we receive following error:
(we using Oracle 9i), error is reported by the EXCEPTION
WHEN OTHERS THEN (see below)
upon logging ad posting status for 250
Normal -6550 ORA-06550: line 4, column 308:
PLS-00103:
Encountered the symbol "S" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem => .. <>
or != or ~= >= <= <> and or like between ||
************************************************** *******
I am providing a complete PL/SQL, this error only occurs at the run-time
PROCEDURE logPostingStatus(p_session_id IN NUMBER,
p_person_id IN NUMBER,
p_ad_id IN NUMBER,
p_production IN VARCHAR2 DEFAULT 'y',
p_status IN VARCHAR2 DEFAULT 'started',
p_files IN VARCHAR2 DEFAULT null,
p_note IN VARCHAR2 DEFAULT null,
p_site_profile_id IN NUMBER DEFAULT -1,
p_site_id IN NUMBER DEFAULT -1)
AS
v_posted_before DATE;
v_change_type VARCHAR2(32);
v_job_num NUMBER;
v_files_passed VARCHAR2(2048) DEFAULT replace(ltrim(rtrim(p_files)), ',', '<br>');
v_files_just_pushed VARCHAR2(2048);
v_files_pushed_before VARCHAR2(2048);
v_files_to_purge VARCHAR2(2048);
v_filename VARCHAR2(256);
v_status VARCHAR2(16) DEFAULT p_status; -- the new status
v_pos NUMBER;
v_phase VARCHAR2(16);
BEGIN
UPDATE ad_post_status
SET end_time = SYSDATE,
-- update the status in the posting status, repeating status => ended
status = decode(status, 'aborted', 'aborted',
lower(p_status), 'ended',
lower(p_status)),
-- append the files pushed by this thread.
files_pushed = decode(files_pushed, null, v_files_passed,
files_pushed || '<br>' || v_files_passed),
note = decode(note, null, p_note, note || '. ' || p_note)
WHERE session_id = p_session_id and ad_id = p_ad_id and production = p_production
RETURNING files_pushed, status INTO v_files_just_pushed, v_status;
-- files_pushed now holds the files pushed by the both threads
-- v_status is the new status
IF (SQL%ROWCOUNT = 0) THEN
-- no log exists for the job yet: we are starting a new job (status will be
-- 'started', the column default)
INSERT INTO ad_post_status (session_id, ad_id, production, note, initiated_by)
VALUES(p_session_id, p_ad_id, p_production, p_note, p_person_id);
END IF;
-- log the session ID in the site/site profile if it is site/site profile roll-out
IF (p_site_profile_id > 0) THEN
UPDATE site_profile
SET roll_session = p_session_id
WHERE site_profile_id = p_site_profile_id and roll_session is null;
ELSIF (p_site_id > 0) THEN
UPDATE site
SET roll_session = p_session_id
WHERE site_id = p_site_id and roll_session is null;
END IF;
-- if two threads complete successfully ...
IF (v_status = 'ended') THEN
-- update posting time
IF (p_production <> 'y') THEN
UPDATE ad SET posted_preview = SYSDATE where ad_id = p_ad_id;
ELSE
SELECT posted_production, change_type
INTO v_posted_before, v_change_type
FROM ad
WHERE ad_id = p_ad_id;
-- ad descriptor_status: off (non-deleted) -> on
UPDATE ad SET descriptor_status = 'on',
dtor_status_modified_by = p_person_id,
dtor_status_modified_reason = 'ad approved and files pushed sucessfully'
WHERE ad_id = p_ad_id
AND status not in ('deleted','failed')
AND descriptor_status = 'off';
-- ad status pending -> live
UPDATE ad SET status = 'live',
status_modified_by = p_person_id,
status_modified_reason = 'ad approved and files pushed successfully'
WHERE ad_id = p_ad_id AND status = 'pending';
UPDATE ad SET posted_production = SYSDATE,
change_type = null
WHERE ad_id = p_ad_id;
-- decrement the live ad count as we are rolling out site profile changes
IF (p_site_profile_id > 0) THEN
UPDATE site_profile
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_profile_id = p_site_profile_id;
ELSIF (p_site_id > 0) THEN
UPDATE site
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_id = p_site_id;
END IF;
-- send traffic reminder email in 24 hours if the ad was approved not for the first time
-- (and not trafficed in 24 hours)
IF (v_posted_before is not null and instr(v_change_type, 'T') > 0) THEN
DBMS_JOB.SUBMIT(v_job_num,
'notify.trafficReminder(' || p_ad_id || ',' || p_person_id || ');',
SYSDATE + 1);
END IF;
/*
* handle akamai file purge: store the files to be pushed;
* detect if there is any file that needs to be purged,
* if yes, send reminder email
*/
v_files_to_purge := '';
IF (v_files_just_pushed || '' = '') THEN
UPDATE ad_post_status
SET files_pushed = '(no file to push)'
WHERE session_id = p_session_id and ad_id = p_ad_id and production = p_production;
RETURN;
END IF;
BEGIN
-- files just pushed
SELECT value INTO v_files_pushed_before
FROM ad_prop
WHERE ad_id = p_ad_id AND name = 'files_pushed';
-- find files to be purged, e.g., files that are passed from p_files and already
-- in the ad_prop 'files_pushed'
LOOP
-- parse the filenames from files_pushed
EXIT WHEN (length(v_files_just_pushed || 'x') = 1);
v_pos := instr( v_files_just_pushed, '<br>' );
IF (nvl(v_pos, 0) = 0) THEN
v_pos := length(v_files_just_pushed) + 1;
END IF;
v_filename := ltrim(rtrim(substr(v_files_just_pushed,1,v_pos-1)));
IF (length(v_filename) > 0) THEN
IF (instr(v_files_pushed_before, v_filename) <= 0) THEN
UPDATE ad_prop
SET value = value || '<br>' || v_filename
WHERE ad_id = p_ad_id
AND name = 'files_pushed';
ELSE
-- the string @@ is used as a place-holder for the site tag file path
-- the string !! is used as a place-holder for the ad file path
-- the string ## is used for the creative (generic) file path
IF (instr(v_filename, '_sitetag.txt') > 0) THEN
v_files_to_purge := v_files_to_purge || '..@@' || v_filename;
ELSIF (instr(v_filename, 'SiteId_') = 1) THEN
v_files_to_purge := v_files_to_purge || '..!!' || v_filename;
ELSE
v_files_to_purge := v_files_to_purge || '..##' || v_filename;
END IF;
END IF;
END IF;
-- jump over the delimiter <br>
v_files_just_pushed := substr( v_files_just_pushed, v_pos+4 );
END LOOP;
-- notify of files to be purged
IF (v_files_to_purge || 'x' <> 'x') THEN
notify.filesToPurge(p_ad_id, v_files_to_purge, p_person_id, p_site_profile_id, p_site_id);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no file has been pushed before: insert the ad_prop
INSERT INTO ad_prop (ad_id, type, name, value)
VALUES(p_ad_id, '-', 'files_pushed', v_files_just_pushed);
END;
END IF;
ELSIF (v_status = 'aborted') THEN
-- determine phase to be written into error notification
IF (p_production = 'y') THEN
v_phase := 'production';
ELSE
v_phase := 'live preview';
END IF;
-- decrement the live ad count as we are rolling out site profile changes
IF (p_site_profile_id > 0) THEN
UPDATE site_profile
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_profile_id = p_site_profile_id
AND roll_ad_count > 0;
ELSIF (p_site_id > 0) THEN
UPDATE site
SET roll_ad_count_rolled = roll_ad_count_rolled + 1,
roll_session = p_session_id,
status_modified_by = p_person_id
WHERE site_id = p_site_id
AND roll_ad_count > 0;
END IF;
notify.postError(p_ad_id, p_person_id, p_site_profile_id, p_site_id, v_phase, p_note);
END IF;
EXCEPTION
WHEN OTHERS THEN
library.logError('upon logging ad posting status for ' || to_char(p_ad_id));
--raise_application_error( -20002, 'logging ad posting status failed');
END logPostingStatus;
dBforums
>
run time error: Encountered the symbol "S" when expecting one of the following:
05-23-03, 12:17
Linear Mode
