SQL: Preparation of SQL query fails. But is executed when done manually

Question

I am getting an error which can be seen below when I am preparing the query:

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

The query 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 query from the log file is:

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',
    '[email protected]',
    '01192966',
    'HGJF',
    '00000000',
    '',
    '900429',
    '1',
    '98989897',
    '',
    'Aargau / Solothurn (CIC)',
    'VCD',
    'RB9',
    'VCD',
    'Observer'
    )

If I execute it manually, the data is getting inserted.

But programatically it is failing for many such rows.

Note that the input text for insert query contains special chars like é, ü.

Also, the same program is working on development system perfectly. But on the production, it is failing.

Manual insertion is working on production properly.

What might be the issue? Any configuration issues?

Thanks in advance.

Answer 1

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

Remove backslash characters from your query just to check if this is the real cause.

Answer 2

There is a fundamental difference between executing a query manually, and using dynamic SQL to prepare a query with bind variables for executing multiple times with different parameters.

Here's a good overview on Dynamic SQL Statements. What you might find especially useful is the section on Preparing Dynamic SQL Statements - specifically the details about placeholders in Oracle, and the section on Executing Dynamic SQL Statements.

It's hard to tell what the problem might be without seeing the dynamic SQL statement you are using, and how you are declaring your placeholders.

Some pointers:

• It seems you are using one variable while attempting to insert multiple values. In Oracle, at the time the statement is executed, there must be one variable for each placeholder.

• The procedure you are showing is not complete.

  • What does the query in :stmt look like?
  • What do your placeholders look like? Are strings properly quoted?

  • What are the values in the variables you are using for the placeholders?

  • Your statement is missing END-EXEC

Example taken from above 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