ErrorCode: 904 Message: Oracle JDBC Driver ORA-00904: : invalid identifier SQLState: 42S22

Question

What is wrong with this function? I call it with an insert and I get the 904 error?

<!---- create ---->
<cffunction name="create" returntype="void">
      <cfargument name="dsn" type="any" required="true" />
      <cfargument name="FNAME" type="string" required="true" />
      <cfargument name="LNAME" type="string" required="true" />
      <cfargument name="EMAIL" type="string" required="true" />
      <cfargument name="PHONE" type="string" required="true" />
      <cfargument name="TITLE" type="string" required="true" />
      <cfargument name="EXPERTISE" type="string" required="true" />
      <cfargument name="TASCS" type="string" required="true" />
      <cfargument name="LEADERSHIP" type="string" required="true" />
      <cfargument name="MANAGEMENT_TEAM" type="string" required="true" />
      <cfargument name="PARTNERS" type="string" required="true" />
      <cfargument name="CONSULTANTS" type="string" required="true" />
      <cfargument name="MEDIA_EVENTS" type="string" required="true" />
      <cfargument name="WEBSITE" type="string" required="true" />
      <cfargument name="ORGANIZATION" type="string" required="true" />
      <cfargument name="PHOTO_NAME" type="string" required="true" />
      <cfargument name="CV_FILE" type="string" required="true" />
      <cfargument name="BIO" type="string" required="true" />

    <cfset var qry="" />    
    <!----TODO:  Below code is for table without auto increment enabled for primary key .Change the query Appropriately---->
    <!---- insert record ---->
    <cftry>
    <cfquery name="qry" datasource="#dsn.getName()#">
        INSERT INTO DIRECTORY
        (
            DIRECTORY_ID,
            FNAME,
            LNAME,
            EMAIL,
            PHONE,
            TITLE,
            EXPERTISE,
            TASCS,
            LEADERSHIP,
            MANAGEMENT_TEAM,
            PARTNERS,
            CONSULTANTS,
            MEDIA_EVENTS,
            WEBSITE,
            ORGANIZATION,
            BIO,
            CREATEDBY,
            ISACTIVE
        )
        VALUES
        (
            DIRECTORY_SEQ.nextval,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.FNAME#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.LNAME#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.EMAIL#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.PHONE#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.TITLE#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.EXPERTISE#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.TASCS#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.LEADERSHIP#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.MANAGEMENT_TEAM#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.PARTNERS#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.CONSULTANTS#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.MEDIA_EVENTS#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.WEBSITE#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR"        value="#ARGUMENTS.ORGANIZATION#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR"    value="#ARGUMENTS.BIO#" null="false" />,
            <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP"      value="#now()#" null="false" />,
            1
        )
    </cfquery>

        <cfcatch type="Any" >
            <cfdump var="#CFCATCH#" output="C:\data\directorydao_create.txt">
        </cfcatch>
    </cftry>

</cffunction>

The error that ends up in the directorydao_create.txt file.

struct

Cause:  
    [struct]
    ErrorCode: 904
    Message: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: "BIO": invalid identifier
    SQLState: 42S22
    StackTrace: java.sql.SQLSyntaxErrorException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: "BIO": invalid identifier
        at macromedia.jdbc.oraclebase.ddca.b(Unknown Source)
        at macromedia.jdbc.oraclebase.ddca.a(Unknown Source)
        at macromedia.jdbc.oraclebase.ddb9.b(Unknown Source)
        at macromedia.jdbc.oraclebase.ddb9.a(Unknown Source)
        at macromedia.jdbc.oracle.ddm.q(Unknown Source)
        at macromedia.jdbc.oraclebase.ddem.v(Unknown Source)
        at macromedia.jdbc.oraclebase.ddem.r(Unknown Source)
        at macromedia.jdbc.oraclebase.dddg.execute(Unknown Source)
        at macromedia.jdbc.oraclebase.dddk.execute(Unknown Source)
        at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101)
        at coldfusion.sql.Executive.executeQuery(Executive.java:1458)

It looks like Oracle is saying that the BIO column doesn't exist. If you run a CFQuery with select BIO from DIRECTORY does that run correctly? — barnyr

Robert Waddell Robert Waddell · Accepted Answer · 2014-02-12T00:00:43

It appears that "BIO" is either a column that doesn't exist in the table or it is an invalid column name (which I don't believe to be true since it is not a reserved word and matches all other criteria to be a valid column name). Ensure that the "BIO" column exists in your database table.

ErrorCode: 904 Message: Oracle JDBC Driver ORA-00904: : invalid identifier SQLState: 42S22

1 Answers