Example 5-18: Procedure to coordinate new paper submission handling

CREATE OR REPLACE PROCEDURE handleNewSubmission(theSubmission CLOB) IS
  isValid          BOOLEAN;
  EmailSubject     VARCHAR2(80);
  EmailBody        VARCHAR2(32767);
  doc              xmldom.DOMDocument;
  submission       accepted_submission%ROWTYPE;
BEGIN
  doc := xml.parse(theSubmission);
  xpath_rules.validate(doc,
                       ruleset_name => 'AbstractSubmission',
                       valid        => isValid,
                       errors       => EmailBody);

  submission.email := xpath.valueOf(doc,'/Submission/Author/Email');

  IF isValid THEN
    -- Assign a new submission id
    SELECT accepted_submission_seq.nextval INTO submission.id FROM DUAL;
    -- Collect the info we need from the XML Paper Submission
    submission.title     := xpath.valueOf(doc,'/Submission/Title',TRUE);
    submission.presenter := xpath.valueOf(doc,'/Submission/Author/Name/First')||
                            ' '||xpath.valueOf(doc,'/Submission/Author/Name/Last');
    submission.abstract  := xpath.valueOf(doc,'/Submission/Abstract',TRUE);
    -- Insert it into our accepted submissions table
    INSERT INTO accepted_submission
    VALUES(submission.id,submission.title,submission.presenter,
           submission.email,submission.abstract);
    EmailSubject := 'Your abstract was accepted. Reference# '||submission.id;
    EmailBody    := 'Thank you.';
  ELSE
    EmailSubject := 'Your abstract was rejected because...';
    -- EmailBody already contains errors flagged by XPath Validation
  END IF;

  IF submission.email IS NOT NULL THEN
    sendEmail(smtp_server => 'mailserver@you.com',
              from_userid => 'smuench@yahoo.com',
                to_userid => submission.email,
                  subject => EmailSubject,
                     body => EmailBody);
  END IF;
  xml.freeDocument(doc);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN xml.freeDocument(doc); ROLLBACK; RAISE;
END;