ORA-00917: missing comma

Question

Name               Null     Type           
------------------ -------- -------------- 
RESOURCE_ID        NOT NULL NUMBER(38)     
RESOURCE_FIRST_NM  NOT NULL VARCHAR2(30)   
RESOURCE_MIDDLE_NM          VARCHAR2(30)   
RESOURCE_LAST_NM   NOT NULL VARCHAR2(30)   
RESOURCE_TYPE_CD   NOT NULL VARCHAR2(10)   
EMPLOYEE_ID                 VARCHAR2(20)   
EMPLOYEE_TYPE_CD            VARCHAR2(10)   
FUNCTIONAL_LEAD_CD          VARCHAR2(10)   
PHONE_1                     VARCHAR2(15)   
PHONE_2                     VARCHAR2(15)   
FAX_NR                      VARCHAR2(15)   
EMAIL_DE                    VARCHAR2(50)   
RESOURCE_STATUS_CD NOT NULL VARCHAR2(10)   
HIRING_DT          NOT NULL DATE           
LEAVE_START_DT              DATE           
LEAVE_END_DT                DATE           
TERMINATED_DT               DATE           
ADDRESS1                    VARCHAR2(50)   
ADDRESS2                    VARCHAR2(50)   
CITY                        VARCHAR2(15)   
STATE                       VARCHAR2(15) 


int i=statement.executeUpdate("
     insert into RAS_T_RESOURCES(
         RESOURCE_ID, 
         RESOURCE_FIRST_NM, 
         RESOURCE_MIDDLE_NM, 
         RESOURCE_LAST_NM, 
         RESOURCE_TYPE_CD, 
         EMPLOYEE_ID, 
         FUNCTIONAL_LEAD_CD, 
         ADDRESS1, 
         ADDRESS2, 
         PHONE_1, 
         PHONE_2, 
         FAX_NR, 
         EMAIL_DE, 
         RESOURCE_STATUS_CD, 
         HIRING_DT, 
         LEAVE_START_DT, 
         LEAVE_END_DT, 
         TERMINATED_DT,
         CITY,
         STATE)
    values(
         2,  
         '"+strFirstname+"', 
         '"+strMiddlename+"', 
         '"+strLastname+"', 
         '"+strResourceType+"', 
         '"+strEmpId+"', 
         '"+strHiringMngr+"', 
         '"+strAddress1+"', 
         '"+strAddress2+"',
         '"+strPhone1+"', 
         '"+strPhone2+"', 
         '"+strFax+"', 
         '"+strEMail+"', 
         '"+strstatus+"', 
         "+strHiringDate+", 
         "+strStartDate+", 
         "+strEndDate+", 
         "+strTerminatedDate+", 
         '"+strCity+"', 
         '"+strState+"');");

Can any one help? What is the problem?

Answer 1

My guess is that the problem is that you're not using bind variables and that you're getting an error related to concatenating a string. If there are commas in the data, for example, that will cause problems. Any of the Oracle DATE columns will generate issues if the Java string happens not to match the session's NLS_DATE_FORMAT.

For performance, usability, and security reasons, I'd strongly suggest using bind variables instead.

Answer 2

Thinking purely as a compiler, expressions like this:

     '"+strFirstname+"'

would be interpreted as literal strings. (No variable substitution would take place, if that's what you're thinking.)

This bit here is probably the problem:

     "+strHiringDate+", 
     "+strStartDate+", 
     "+strEndDate+", 
     "+strTerminatedDate+", 

These would be parsed by Oracle as identifiers, not strings - because you've omitted the single quotes ('), and Oracle identifiers are optionally delimited by double quotes (").