What does the colon sign ":" do in a SQL query?

sql oracle plsql bind-variables
Jla picture Jla · Feb 1, 2010 · Viewed 82.8k times · Source

What does : stand for in a query?

INSERT INTO MyTable (ID) VALUES (:myId)

How does it fetch the desired value?

Edit: Also what is that sign called? I wanted to search on google, but what's the name for :?

Answer

Jeffrey Kemp picture Jeffrey Kemp · Feb 2, 2010

What does ":" stand for in a query?

A bind variable. Bind variables allow a single SQL statement (whether a query or DML) to be re-used many times, which helps security (by disallowing SQL injection attacks) and performance (by reducing the amount of parsing required).

How does it fetch the desired value?

Before a query (or DML) is executed by Oracle, your program will create a cursor. Your program issues the SQL to be parsed for that cursor, then it must bind values for each bind variable referred to by the SQL. How this is done depends on the language.

What is that sign called?

A colon.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More