safety

Consider the following SQL:

SELECT title FROM books WHERE isbn = '0-321-12742-0';

This could be written with parameters as follows:

SELECT title FROM books WHERE isbn = :isbn_value;

The :isbn_value is the parameter in that statement. You can then supply a value for that. (How you supply the value depends upon your Oracle client. (This is not the important bit))

The great thing about the second SQL statement is that to Oracle it looks identical each time it's executed, irrespective of the actual value associated with the parameter.

So if that second SQL statement is executed frequently, Oracle will find it in the cache and therefore will not parse it again. This can have real performance gains.

But this is not the main reason I like parameters. Oh, yes, performance is important, but there's something else that they do.

Consider this SQL statement:

SELECT first_name FROM employees WHERE last_name = :LastName_Value;

In this statement, the :LastName_Value is the parameter.

If you supplied a value of "Smith" to this parameter, then the SQL would find all employees that have a surname of Smith. Good.

If I supplied a value of "O'Connor", it would find all employees that have a surname of O'Connor. Good. Hang on, it worked with a sting value that had an apostrophe in it!

If the SQL statement had been constructed in Code and the value substituted then the SQL would have been:

SELECT first_name FROM employees WHERE last_name = 'O'Connor';

And that would have failed. This can happen in PROD. That makes you look bad.

So the GOOD thing that parameters gives you is safety against time bombs in code. You will never have to worry about apostrophes in data again!

Note: It's not recommended to use parameters when the comparison between the field and the parameter is the LIKE statement. In that case it's better to use literal values. EG:

SELECT first_name FROM employees WHERE last_name LIKE 'Smi%';

But then, you'll have to deal with those damned apostrophes again. Oh well...