Tom,
I tried to manipulate the code above to utilize...but the insert statement doesn't seem to like all of the special characters in my XML file below. I'm sending the XML packet to Oracle via ColdFusion.
Here's the error I get once the stored procedure is called.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at "SASSOH.LOADXML", line 3 ORA-06512: at line 1
Thanks in advance for you help.
Jim
-------------------------------------------------------
CREATE TABLE JOBS
(
ID NUMBER,
HOST VARCHAR2(100 BYTE),
SEARCH VARCHAR2(100 BYTE),
LINK VARCHAR2(4000 BYTE),
POSTDATE DATE,
TITLE VARCHAR2(4000 BYTE),
COMPANY VARCHAR2(4000 BYTE),
LOCATION VARCHAR2(100 BYTE),
D_UPD DATE,
STATE VARCHAR2(100 BYTE),
CITY VARCHAR2(100 BYTE),
TITLE_NOLINK VARCHAR2(100 BYTE),
COMPANY_NOLINK VARCHAR2(100 BYTE),
XMLDOC SYS.XMLTYPE
)
CREATE OR REPLACE PROCEDURE LoadXML (cfxml in varchar) AS
begin
insert into JOBS (xmldoc) values (xmltype.createxml(cfxml));
end;
/
CREATE OR REPLACE TRIGGER jobs_bifert
before insert on jobs
for each row
begin
if (:new.xmldoc is null) then
return;
end if;
select xmltype.getstringval(:new.xmldoc.extract('/row/date/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/title/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/company/text()')),
xmltype.getstringval(:new.xmldoc.extract('/row/location/text()'))
into :new.postdate, :new.title, :new.company, :new.location
from dual;
end loop;
/
<?xml version="1.0" encoding="iso-8859-1">
<recordset>
<row>
<date><![CDATA[Jul 25]]></date>
<title><![CDATA[<a href="</code>
http://jobsearch.monster.com:80/getjob.asp?JobID=27427955&AVSDM=2005%2D07%2D25+08%3A11%3A26&Logo=1&q=php&cy=US&sort=dt" >SENIOR
HTML/CSS/JAVASCRIPT DEVELOPER</a>]]></title>
<company><![CDATA[RCG Information Technology]]></company>
<location><![CDATA[<a onClick="popMMLL('FL','Orlando'); return false;" href="
http://jobsearch.monster.com:80/385" >FL-Orlando</a>]]></location>
<myfile><![CDATA[<a href="
http://my.monster.com/JobFile/JobFile.aspx?action=ADD&JobID=27427955&jobTitle=SENIOR+HTML%2FCSS%2FJAVASCRIPT+DEVELOPER&fjs=1&q=php&cy=US&sort=dt" ></a>]]></myfile> <code>
</row>
</recordset>