Oracle error=917 when trying to insert into database

[QueenBee]

I get the following error when I try to insert into table -

Oracle Error Code = 917

ORA-00917: missing comma



SQL = "INSERT INTO ISSCWEB_REQUEST (REQUESTOR_NAME, REQUESTOR_PHONE,
REQUESTOR_EMAIL, CONTENT_SECTION, CONTENT_OWNER, URL_CHANGE, NEW_CONTENT,
MODIFY_CONTENT, REMOVE_CONTENT, MODIFY_DATE, CHANGES_REMAIN, REMOVE_CHANGES,
COMMENTS) VALUES ('Danna Sturdivant', '978/659-3738',
'Danna.Sturdivant@philips.com', 'WINTEL', 'Mike Conroy',
'http://www.wwww.wwww.wwww.com', 'Add new content to web site', 'change section
2 to say - this is modified', 'Remove section 3', '{d '2006-05-01'}', 'No', '{d
'2006-06-01'}', 'This is a test')"


Any ideas? :confused;

<cfparam name="session.isscwebrequest" default="">
<cfparam name="form.content_section" default="">
<cfparam name="form.content_section" default="">
<cfparam name="form.urlchange" default="">
<cfparam name="form.newcontent" default="">
<cfparam name="form.modify_content" default="">
<cfparam name="form.remove_content" default="">
<cfparam name="form.modify_date" default="">
<cfparam name="form.changes_remain" default="">
<cfparam name="form.remove_changes" default="">
<cfparam name="form.comments" default="">

<cfset requestor = #session.stcUserInfo.userfname# &' '&
#session.stcUserInfo.userlname#>

<!--- can't put cf function is query statement. Setting variables to
accomodate date values. --->
<cfset modifydate = #createODBCDate(modify_date)#>

<cfif remove_changes is not "">
<cfset removechanges = #createODBCDate(remove_changes)#>
<cfelse>
<cfset removechanges = "">
</cfif>
<title>ISSC Web Request - Process Page</title>

<cftransaction>

<cfquery name="insert_request" datasource="#client.oracledsn#"
username="#client.oracleuser#" password="#client.oraclepass#">
INSERT INTO
ISSCWEB_REQUEST
(REQUESTOR_NAME,
REQUESTOR_PHONE,
REQUESTOR_EMAIL,
CONTENT_SECTION,
CONTENT_OWNER,
URL_CHANGE,
NEW_CONTENT,
MODIFY_CONTENT,
REMOVE_CONTENT,
MODIFY_DATE,
CHANGES_REMAIN,
REMOVE_CHANGES,
COMMENTS)
VALUES
('#requestor#',
'#session.stcUserInfo.userphone#',
'#session.stcUserInfo.useremail#',
'#content_section#',
'#contentowner#',
'#urlchange#',
'#newcontent#',
'#modify_content#',
'#remove_content#',
'#modifydate#',
'#changes_remain#',
'#removechanges#',
'#comments#')
</cfquery>



<CFQUERY name="getRequestID" datasource="#client.oracledsn#"
username="#client.oracleuser#" password="#client.oraclepass#">
SELECT MAX(ISSCWEB_ID) as ISSC_ID
FROM ISSCWEB_REQUEST
</CFQUERY>

</cftransaction>

<!--- send email confirmation --->
<!--- cc="#form.contentowneremail#" --->
<cfmail from="Change-Mgmt.PMS-IT@philips.com"
to="#session.stcUserInfo.useremail#"
cc="Change-Mgmt.PMS-IT@philips.com"
bcc="infrastructure.admin@philips.com"
subject="ISSC Web Content Change Request - #getRequestID.ISSCID# --
TESTING"
type="HTML">

This is the confirmation for your request <strong>REQUEST ID</strong>.<br><br>
For status updates, please <strong>REPLY TO ALL</strong> on this request.<br>
<hr>
<br>


#session.isscwebrequest#

</cfmail>

<!--- end send email --->

<!--- go to confirmation page --->
<cflocation url="isscweb_confirm.cfm?requestid=#getrequestid.i sscid#"
addtoken="no">

[paross1]

You have a comment that states <!--- can't put cf function is query
statement.... Why not? I do it all of the time. Also, I don't think that you
want to enclose your date values in single quotes.

Phil

[QueenBee]

I was having other errors, that's why i have the variables set at the top. I
removed the quotes from the dates. Now I get the following error:

ORA-00911: invalid character



SQL = "INSERT INTO ISSCWEB_REQUEST (REQUESTOR_NAME, REQUESTOR_PHONE,
REQUESTOR_EMAIL, CONTENT_SECTION, CONTENT_OWNER, URL_CHANGE, NEW_CONTENT,
MODIFY_CONTENT, REMOVE_CONTENT, MODIFY_DATE, CHANGES_REMAIN, REMOVE_CHANGES,
COMMENTS) VALUES ('Danna Sturdivant', '978/659-3738',
'Danna.Sturdivant@philips.com', 'EUC', 'Dan Lawrence', 'l;kjlkjsxdljl',
'aosidufpu', 'apsiofdau', 'apsdifao', {d '2006-04-01'}, 'No', {d '2006-06-01'},
'eraspdiursae')"



<cfquery name="insert_request" datasource="#client.oracledsn#"
username="#client.oracleuser#" password="#client.oraclepass#">
INSERT INTO
ISSCWEB_REQUEST
(REQUESTOR_NAME,
REQUESTOR_PHONE,
REQUESTOR_EMAIL,
CONTENT_SECTION,
CONTENT_OWNER,
URL_CHANGE,
NEW_CONTENT,
MODIFY_CONTENT,
REMOVE_CONTENT,
MODIFY_DATE,
CHANGES_REMAIN,
REMOVE_CHANGES,
COMMENTS)
VALUES
('#requestor#',
'#session.stcUserInfo.userphone#',
'#session.stcUserInfo.useremail#',
'#content_section#',
'#contentowner#',
'#urlchange#',
'#newcontent#',
'#modify_content#',
'#remove_content#',
#createODBCDate(modify_date)#,
'#changes_remain#',
#createODBCDate(remove_changes)#,
'#comments#')
</cfquery>

[Dan Bracuk]

Nothing is obviously wrong. My suggestion is to comment out all the fields
except the first one and try to run it. Then un-comment the fields one at a
time until it crashes. Then you'll at least know what field it was.

[QueenBee]

I've narrowed it down to the date fields that is doesn't like. Everything else will insert just fine.

Any other ideas?

[JMGibson3]

It depends on your exact Driver/Oracle Version/Connection environment and I
haven't done it in many years, but I do recall updating dates succesfully with
DD-MMM-YY syntax and no quotes. Oracle seemed to like that just fine. I'd
give about an 80% chance that SET myDate = 35-JUL-06 will work for you.

[QueenBee]

1) If I put #dateformat(modify_date,'dd-mmm-yy')# - I get error :
Oracle Error Code = 984

ORA-00984: column not allowed here



SQL = "INSERT INTO ISSCWEB_REQUEST (REQUESTOR_NAME, REQUESTOR_PHONE,
REQUESTOR_EMAIL, CONTENT_SECTION, CONTENT_OWNER, URL_CHANGE, NEW_CONTENT,
MODIFY_CONTENT, REMOVE_CONTENT, MODIFY_DATE, CHANGES_REMAIN, COMMENTS,
ISSCWEB_ID) VALUES ('Danna Sturdivant', '978/659-3738',
'Danna.Sturdivant@philips.com', 'EUC', 'Dan Lawrence', 'l;kjlkjsxdljl',
'aosidufpu', 'apsiofdau', 'apsdifao', 01-Apr-06, 'No', 'eraspdiursae',
ISSC_WEB_SEQ.Nextval)"

2) If I put #createODBCDate(modify_date)# - I get:
ORA-00911: invalid character



SQL = "INSERT INTO ISSCWEB_REQUEST (REQUESTOR_NAME, REQUESTOR_PHONE,
REQUESTOR_EMAIL, CONTENT_SECTION, CONTENT_OWNER, URL_CHANGE, NEW_CONTENT,
MODIFY_CONTENT, REMOVE_CONTENT, MODIFY_DATE, CHANGES_REMAIN, COMMENTS,
ISSCWEB_ID) VALUES ('Danna Sturdivant', '978/659-3738',
'Danna.Sturdivant@philips.com', 'EUC', 'Dan Lawrence', 'l;kjlkjsxdljl',
'aosidufpu', 'apsiofdau', 'apsdifao', {d '2006-04-01'}, 'No', 'eraspdiursae',
ISSC_WEB_SEQ.Nextval)"

[QueenBee]

I fixed the problem - instead of using CreateODBCDate(modify_date), I used
'#dateformat(modify_date,'dd-mmm-yyyy')# -- this is using the format that
ORACLE likes and that solved the issue.

Thanks to all who responded!