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; |