SQL: SQL query preparation fails. But executed when executed manually

Question

I get an error message that can be seen below when I prepare the request:

SQL-ERR:Preparation of INSERT Query Failed: Ora-Err: -1756 ORA-01756: quoted string not properly terminated 

The request is as follows:

 EXEC SQL declare INSDTA STATEMENT; EXEC SQL PREPARE INSDTA FROM :stmt; if(sqlca.sqlcode < 0) { DEBUG_LOG("SQL-ERR:Preparation of INSERT Query Failed: Ora-Err: %d %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc); DEBUG_LOG("The Query is: %s\n", insertQuery); return PREPARATION_FAILURE; } 

And the request from the log file:

 INSERT INTO TABLENAME VALUES ( '00000001', '00004467', '0', 'R56565', '03404395', '20110601', '999', '87685785', '2017-01-10-23.05.26.000000', 'KRMAR', 'KRMAR', '77898878', '03', '00000001', 'U', '01', '1', '87685785', 'R56565', '89878988', 'cde', 'Andr\351', ' [e-mail] ', '01192966', 'HGJF', '00000000', '', '900429', '1', '98989897', '', 'Aargau / Solothurn (CIC)', 'VCD', 'RB9', 'VCD', 'Observer' ) 

If I performed it manually, the data will be inserted.

But programmatically, it fails for many such lines.

Please note that the input text for the insert query contains special characters such as é , ü .

In addition, this same program works great on a development system. But in production, he fails.

Manual installation works correctly.

What could be the problem? Any configuration issues?

Thanks in advance.

Answer 1

Since \ is an escape character, I think the error comes from 'Andr\351' , which should probably be 'André' .

Remove backslash characters from your query to see if this is the real reason.

Answer 2

There is a fundamental difference between executing a query manually and using dynamic SQL to prepare a query with binding variables to execute several times with different parameters.

Here is a good overview of Dynamic SQL Statements . The “Preparing Dynamic SQL Statements” section, in particular, information about placeholders in Oracle and the section “Executing Dynamic SQL Queries”, may be especially useful.

It's hard to say what the problem is without seeing the dynamic SQL statement that you are using, and how you declare your placeholders.

Some pointers:

• It seems you are using a single variable while trying to insert multiple values. In Oracle, at run time, there must be one variable for each placeholder.

• The procedure you are showing is not completed.

  • What does the request look like in :stmt ?
  • What do your placeholders look like? Are the lines correct?

  • What are the values ​​in the variables you use for placeholders?

  • Your statement is missing END-EXEC

Example taken from source:

 move "INSERT INTO publishers " & "VALUES (?,?,?,?)" to stmtbuf EXEC SQL PREPARE stmt1 FROM :stmtbuf END-EXEC ... EXEC SQL EXECUTE stmt1 USING :pubid,:pubname,:city,:state END-EXEC 

Answer 3

remove all ('') from integer values

Answer 4

Your request fails because the forward space is in the "Aargau / Solothurn (CIC)" column. Backspace is a special character, so you need to take care to use backspace or delete it altogether. Then you can successfully execute it.