Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    1

    Unanswered: run time error: Encountered the symbol "S" when expecting one of the following:

    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;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: run time error: Encountered the symbol "S" when expecting one of the following:

    I can't see where the error is coming from, but try this: comment out (or remove) all the exception handling:

    --EXCEPTION
    --WHEN OTHERS THEN
    --library.logError('upon logging ad posting status for ' || to_char(p_ad_id));

    Then the error message will inform you of precisely which line of code the error occured on.

    One error I did spot (but is irrelevant to your problem):

    IF (v_files_just_pushed || '' = '') THEN

    This will never be true, because in Oracle '' is equivalent to NULL, and NULL = NULL is never true. Your test should be:

    IF v_files_just_pushed IS NULL THEN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •