ORA-00911: invalid character error

leekikwan · Jun 17, 2004

When I run following cfml file in CF V4.5, I get ORA-00911: invalid character error.
<cflock name="InsertNewRecord" type = "EXCLUSIVE" timeout="60">
<cftransaction>
<CFIF #FORM.MELD# LE 10>
<CFSET NEXTDT = DateAdd("d", 365, "#FORM.MDATE#")>
<CFELSEIF #FORM.MELD# GE 11 and #FORM.MELD# LT 19>
<CFSET NEXTDT = DateAdd("d", 90, "#FORM.MDATE#")>
<CFELSEIF #FORM.MELD# GE 19 and #FORM.MELD# LT 25>
<CFSET NEXTDT = DateAdd("d", 30, "#FORM.MDATE#")>
<CFELSEIF #FORM.MELD# GE 25>
<CFSET NEXTDT = DateAdd("d", 7, "#FORM.MDATE#")>
</CFIF>
<cfquery NAME = "AddRecord" datasource="scm">
INSERT INTO T_MELD
(XID, MDATE, MELD, NEXTDT, MP)
VALUES
('#FORM.XID#',
TO_DATE('#FORM.MDATE#', 'mm/dd/yyyy'),
#FORM.MELD#,
#NEXTDT#,
'#FORM.MP#'
)
</cfquery>
</cftransaction>
</cflock>

<html>
<head>
<title>New MELD Info Added! </title>
</head>
<body>
<H2 class="style1">New MELD Record Added!</H2>
</body>
</html>

Will you help me?

ecobb · Jun 18, 2004

Try using <cfqueryparam> in your INSERT statement.

Code:

<cfquery NAME = "AddRecord" datasource="scm">
INSERT INTO T_MELD(XID, MDATE, MELD, NEXTDT, MP)
VALUES(<cfqueryparam value="#FORM.XID#" cfsqltype="CF_SQL_VARCHAR">,
        <cfqueryparam value="#FORM.MDATE#" cfsqltype="CF_SQL_DATE">,
        <cfqueryparam value="#FORM.MELD#" cfsqltype="CF_SQL_INTEGER">,
        <cfqueryparam value="#NEXTDT#" cfsqltype="CF_SQL_INTEGER">,
        <cfqueryparam value="#FORM.MP#" cfsqltype="CF_SQL_VARCHAR">)
</cfquery>

This will validate each entry AND format some things the way the Oracle server likes them (i.e. make sure MDATE is really a date and give it to the database in Oracle's prefered format...no more To_Date!! Woo-Hoo!) Even if it doesn't fix this problem, it will help you figure out which value is causing the problem.

Also, make sure that you are passing the correct datatypes to the database. Maybe you're trying to pass a Varchar value to an Int datatype?

Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

leekikwan · Jun 22, 2004

I have found out the solution.
The problem was NEXTDT variable.
It create a ODBC timestamp date string and ColdFusion 4.5 does not handle it very well. (It works Verision 5.0 and after).
I have corrected it as follows.

<cfset NEXTDT=DateFormat(NEXTDT,"mm/dd/yyyy")>

<cfquery NAME = "AddRecord" datasource="scm">
INSERT INTO T_MELD
(XID, MDATE, MELD, NEXTDT, MP)
VALUES
(#FORM.XID#,
TO_DATE('#FORM.MDATE#', 'mm/dd/yyyy'),
#FORM.MELD#,
TO_DATE('#NEXTDT#','mm/dd/yyyy'),
'#FORM.MP#'
)
</cfquery>

It solves the problem.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ORA-00911: invalid character error

leekikwan

Programmer

ecobb

Programmer

leekikwan

Programmer

Similar threads

Part and Inventory Search

Sponsor