问题描述:

I've been trying to get a native UPDATE query executed but I keep getting an ORA-01747 error.

public void editemployee (String id, String fname, String lname, String email, String phone,

String hdate, String job, String salary, String comm, String dept) {

StringBuilder editquery = new StringBuilder();

editquery.append("UPDATE EMPLOYEES ");

editquery.append("SET ");

editquery.append(" FIRST_NAME = '"+fname+"', ");

editquery.append(" LAST_NAME = '"+lname+"', ");

editquery.append(" EMAIL = '"+email+"', ");

editquery.append(" PHONE_NUMBER = '"+phone+"', ");

editquery.append(" HIRE_DATE = TO_DATE('"+hdate+"', 'YYYY/MM/DD'), ");

editquery.append(" SALARY = "+salary+", ");

editquery.append(" COMMISSION_PCT = "+comm+", ");

editquery.append(" JOB_ID = (SELECT JOB_ID FROM JOBS WHERE JOB_TITLE = '"+job+"'), ");

editquery.append(" DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '"+dept+"'), ");

editquery.append(" MANAGER_ID = (SELECT MANAGER_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '"+dept+"') ");

editquery.append("WHERE ");

editquery.append(" EMPLOYEE_ID = "+id);

JPA.em().createNativeQuery(editquery.toString()).executeUpdate();

}

This is for a training activity, so we were required to use StringBuilders to format our queries as such.

Here's a part of the error logs from the console.

2016-08-19 16:16:45,054 - [INFO] - from play in play-internal-execution-context-1

Application started (Dev)

2016-08-19 16:16:47,177 - [ERROR] - from org.hibernate.util.JDBCExceptionReporter in play-akka.actor.default-dispatcher-38

ORA-01747: invalid user.table.column, table.column, or column specification

2016-08-19 16:16:47,178 - [ERROR] - from play in play-internal-execution-context-1

Cannot invoke the action, eventually got an error: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

2016-08-19 16:16:47,181 - [ERROR] - from application in play-internal-execution-context-1

As for the database itself, the column names are correct, seeing as I was able to execute other native queries using these column names as well.

public void addemployee (String fname, String lname, String email, String phone,

String hdate, String job, String salary, String comm, String dept) {

StringBuilder addquery = new StringBuilder();

addquery.append("INSERT INTO ");

addquery.append(" EMPLOYEES( ");

addquery.append(" EMPLOYEE_ID, ");

addquery.append(" FIRST_NAME, ");

addquery.append(" LAST_NAME, ");

addquery.append(" EMAIL, ");

addquery.append(" PHONE_NUMBER, ");

addquery.append(" HIRE_DATE, ");

addquery.append(" SALARY, ");

addquery.append(" COMMISSION_PCT, ");

addquery.append(" JOB_ID, ");

addquery.append(" DEPARTMENT_ID, ");

addquery.append(" MANAGER_ID ) ");

addquery.append("VALUES ( ");

addquery.append(" (SELECT max(EMPLOYEE_ID) FROM EMPLOYEES) + 1, ");

addquery.append(" '"+fname+"', ");

addquery.append(" '"+lname+"', ");

addquery.append(" '"+email+"', ");

addquery.append(" '"+phone+"', ");

addquery.append(" TO_DATE('"+hdate+"', 'YYYY/MM/DD'), ");

addquery.append(" "+salary+", ");

addquery.append(" "+comm+", ");

addquery.append(" (SELECT JOB_ID FROM JOBS WHERE JOB_TITLE = '"+job+"'), ");

addquery.append(" (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '"+dept+"'), ");

addquery.append(" (SELECT MANAGER_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '"+dept+"')) ");

JPA.em().createNativeQuery(addquery.toString()).executeUpdate();

}

This particular INSERT query executes just fine, so I'm not entirely sure how and why I'm getting the ORA-01747 on the UPDATE query. I also tested both queries on my Oracle SQL Developer and they're both executing.

I did try to look around for similar questions, but most of what I saw were for INSERT queries, which made me all the more confused as to why the errors are occurring.

相关阅读:
Top