Where's My Invalid Character (Ora-00911)

Where's my invalid character (ORA-00911)

If you use the string literal exactly as you have shown us, the problem is the ; character at the end. You may not include that in the query string in the JDBC calls.

As you are inserting only a single row, a regular INSERT should be just fine even when inserting multiple rows. Using a batched statement is probable more efficient anywy. No need for INSERT ALL. Additionally you don't need the temporary clob and all that. You can simplify your method to something like this (assuming I got the parameters right):

String query1 = "select substr(to_char(max_data),1,4) as year, " + 
  "substr(to_char(max_data),5,6) as month, max_data " +
  "from dss_fin_user.acq_dashboard_src_load_success " + 
  "where source = 'CHQ PeopleSoft FS'";

String query2 = ".....";

String sql = "insert into domo_queries (clob_column) values (?)";
PreparedStatement pstmt = con.prepareStatement(sql);
StringReader reader = new StringReader(query1);
pstmt.setCharacterStream(1, reader, query1.length());
pstmt.addBatch();

reader = new StringReader(query2);
pstmt.setCharacterStream(1, reader, query2.length());
pstmt.addBatch();

pstmt.executeBatch();   
con.commit();

Error: ORA-00911: invalid character

Try removing the semicolon ; at the end.

Also, look at this question: Where's my invalid character (ORA-00911)

How can I solve ORA-00911: invalid character error?

The statement you're executing is valid. The error seems to mean that Toad is including the trailing semicolon as part of the command, which does cause an ORA-00911 when it's included as part of a statement - since it is a statement separator in the client, not part of the statement itself.

It may be the following commented-out line that is confusing Toad (as described here); or it might be because you're trying to run everything as a single statement, in which case you can try to use the run script command (F9) instead of run statement (F5).

Just removing the commented-out line makes the problem go away, but if you also saw this with an actual commit then it's likely to be that you're using the wrong method to run the statements.

There is a bit more information about how Toad parses the semicolons in a comment on this related question, but I'm not familiar enough with Toad to go into more detail.

java.sql.SQLException: ORA-00911: invalid character

Instead of:

Statement st = con.createStatement();
ResultSet rs = st.executeQuery("insert into users values('" + uname + "','" + fname + "','"  + lname + "','" + cpswd + "',"+ phon + ",'" + cemail + "','" + sex + "')");

I'd suggest doing the following:

PreparedStatement st = con.prepareeStatement("insert into users values(?,?,?,?,?,?,?);");
st.setString(1, uname);
st.setString(2, fname); 
st.setString(3, lname); 
st.setString(4, cpswd); 
st.setLong(5, phon); 
st.setString(6, cemail); 
st.setString(7, sex);
ResultSet rs = st.executeQuery();

Your problem is not evident by your question, but using PreparedStatement as I have will prevent future problems you will encounter. I hope that helps.

SQL - ORA-00911 Invalid character on my insertions

First thing is change ‘ to '

Second. This value exceeded the maximum length of your Title column which is 25

VALUES (‘Harry Potter and the Sorcerer’s Stone’, ‘J.K. Rowling’); -> #of chars = 37

Third is escape your single quote by adding ', ‘Dante'’s Inferno’

VALUES (‘Harry Potter and the Sorcerer'’s Stone’, ‘J.K. Rowling’); -> #of chars = 37

try SQLFIDDLE

ORA-00911: invalid character error while executing a query

An SQL statement does not include a trailing semicolon. That is used as a statement separator in SQL*Plus and other clients (though even there you can change that to a different character), and it is not part of the statement itself. (PL/SQL requires semicolons as separators, but that's off-topic).

When you're running the statement over JDBC you should not include the semicolon. You should just be running

"select salution||' '||firstname||' '||lastname as custName, "
    + "city as custCity, state as custState, zip as custZip, "
    + "dob as custDob, idhardtoken as custAdarNo from mstuser"

(split just to show it without sideways scrolling...)

---

Related Topics