2

What is the meaning of this code and what do you call this method? How will I know what is the value for :1, :2, :3 and so on?

(PL/SQL Procedure)

UPDATE tablename
SET column = :1, column = :2, column = :3, column = :4, column= :5....
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
mysticfalls
  • 445
  • 5
  • 17
  • 28

2 Answers2

2

Is this in SQL*Plus?

If so, they are parameter placeholders. SQL*Plus will prompt you for values upon execution.

If you're coming from a SQL client / programming language (Java, PHP, C#, etc) these would usually represent parameters in a prepared statement though I'm not sure if digit only placeholders are valid.

Update

This can also appear in dynamic SQL executed using an OPEN-FOR-USING statement. Without seeing more of your code, I'm only guessing.

Phil
  • 157,677
  • 23
  • 242
  • 245
  • +1. You might add that this is called a "parametrized query" and that it helps to avoid SQL injection and keep code cleaner. – Ken White Oct 27 '11 at 02:58
  • @Phil oh sorry I missed that part.. It is a PL/SQl package – mysticfalls Oct 27 '11 at 03:01
  • @KenWhite That's where I was going with "prepared statement". Some good reading [here](http://en.wikipedia.org/wiki/Prepared_statement) and [here](http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements) – Phil Oct 27 '11 at 03:01
  • @Phil Where is the value coming from if it is a pl/sql procedure? Was there supposed to be a query before that or was it defined as a variable? I checked some of this but the number of variables doesn't seem to match. – mysticfalls Oct 27 '11 at 03:10
  • @mysticfalls I've remembered another use for that syntax and added it to my answer – Phil Oct 27 '11 at 03:33
  • 1
    @Phil: I know. :) But the question asked for the specific name for that statement type, which is `parameterized query`. I upvoted your answer as it was, and merely suggested an addition. :) – Ken White Oct 27 '11 at 11:06
1

Those are bind variables. Oracle substitutes them for actual values which are passed. These are generally found when you're using Dynamic SQL, EXECUTE-IMMEDIATE, OR OPEN-FOR-USING as mentioned by Phil.

If you want to know what values are being held there, you probably would wnat to look up where the UPDATE statements are being issued & log them to a logging/debugging table just before the UPDATE statement is issued

Community
  • 1
  • 1
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
  • I think I see it now.. Its just kind of weird that the update statement where executed first before the execute immediate. Do you happen to know any reason why it will be done that way? Thank you. – mysticfalls Oct 27 '11 at 06:36
  • 1
    Execute immediate prepares the update statement - which is why you see the bind variables. Once prepared, the parameters are submitted and then the update statement is run. @mysticfalls – Sathyajith Bhat Oct 27 '11 at 06:57