Form String with single quote. Error ORA-01756


I am running a ORACLE statement from C++ and I receive this error:


ORA-01756: quoted string not properly terminated (OCI_ERROR)

I form the string sentence as follows:

sprintf(sentence, "INSERT INTO \"TABLA\".\"AUTORES\" VALUES('%s', '%s')", autor.nombre, autor.apellido);

Where autor is a struct declared like this:

struct Autor{
    char nombre[100];
    char apellido[100];

My problem comes when, for example, the name is " O'Neill ". In those cases the single quote gives me the error. I know it is solved by putting two single quotes '' . But it is a loop that will read me a set of struct author very big and I would like to be able to solve it for all cases and also not going through each name and each last name.

Thanks in advance!

asked by cluna 11.11.2016 в 09:37

1 answer


Oracle says what next :


When the apostrophe / single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

That is, in these cases you have to put two single quotes in a row:


O'Neil - > O''Neil

The easiest thing to do is to create a function that allows you to prepare the strings:

std::string Fix(const std::string& cad)
  std::string toReturn;
  for( auto c : cad )
    toReturn += c;
    if( c == '\'' )
      toReturn += c;

  return toReturn;

With what the conflicting line would be:

sprintf(sentence, "INSERT INTO \"TABLA\".\"AUTORES\" VALUES('%s', '%s')", Fix(autor.nombre), Fix(autor.apellido));

The struct variables are char*

It's not a problem. One of the constructors of std::string accepts a parameter of type const char* , that's why the following code is able to compile:

std::string test = "una prueba";

On the other hand, std::string allows you to obtain your internal pointer, of type char* in the following way:

const char* ptr = test.c_str();

With this the correct call to sprintf could look like this:

sprintf(sentence, "INSERT INTO \"TABLA\".\"AUTORES\" VALUES('%s', '%s')",

While this should work, it does not seem like the most elegant solution. How about we replace sprintf with std::stringstream ?

std::stringstream stream;
       << Fix(autor.nombre) << "','"
       << Fix(autor.apellido) << "')";

std::stringstream being an own C ++ object (instead of a function inherited from C as is the case of sprintf ) has adequate overloads to work directly with class std::string . And why use std::string instead of char* , because for several reasons:

  • Manage dynamic memory transparently for the user, which means fewer headaches and cleaner code.
  • It has multiple utilities (like length() to know the size or append() to add content at the end) that greatly facilitate its use.

Of course, if in addition to this we start pulling templates we can give a new twist to the code. For example we can have a generic function that decides, at compile time, if there is a transformation to be applied to the different parameters or not:

template<class T>
struct func_traits
  typedef T type;

template<class T>
typename func_traits<T>::type Parse(T value)
{ return value; }

// Esta especialización sirve únicamente como demostración
int Parse<std::string>(int value)
  return value*2;

std::string Parse<std::string>(std::string value)
  return Fix(value);

const char* Parse<const char*>(const char* value)
  return Fix(value).c_str();

int main()
  std::cout << Parse(std::string("O'Neil")) << '\n'; // std::string
  std::cout << Parse("O'Neil") << '\n'; // const char*
  std::cout << Parse(12) << '\n'; // int
  std::cout << Parse(1.5) << '\n'; // Función genérica



If you wonder about the existence of struct traits you have to know that, directly, it is not possible to declare a function template whose return value depends on the specialization of the template. Said with an example, the following does not compile:

template<class T>
T func(T val)
{ return val; }

However, if you allow the return value to be deducted from the specialization of a template.

another alternative

Another option you have is to stop creating SQL statements directly and move on to using stored statements. Have a look here . The idea is to let the Oracle API prepare the arguments:

oraub8 largeRowCount = 0;
Statement *stmt = conn->createStatement("INSERT INTO TABLA.AUTORES values (:1,:2)");
stmt->setString(1, autor.nombre);
stmt->setString(2, autor.apellido);
largeRowCount = stmt->getUb8RowCount();
answered by 11.11.2016 в 10:49