PL/SQL code using :1, :2, :3 and so on

sql oracle stored-procedures plsql bind-variables
mysticfalls picture mysticfalls · Oct 27, 2011 · Viewed 9k times · Source

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....

Answer

Phil picture Phil · Oct 27, 2011

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.

Related questions

How do I find out when a stored procedure was last modified or compiled in Oracle?

I'm preferably looking for a SQL query to accomplish this, but other options might be useful too.

Read More
Solution to "cannot perform a DML operation inside a query"?

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this create …

Read More
How to use Alias in Where clause?

I have this procedure: PROCEDURE P_LOAD_EXPIRED_ACCOUNT ( pDayDiff IN NUMBER, ExpiredCur OUT MEGAGREEN_CUR ) IS BEGIN OPEN ExpiredCur FOR SELECT ACCOUNT_NAME, SERVICE_TYPE, CASE WHEN SERVICE_TYPE = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3) WHEN SERVICE_TYPE = 2 THEN ADD_…

Read More