Select where X = variable

Question

Im trying to write an Oracle SQL statement where I want to update a lot of rows connected to one id. I've managed to solve the update query, but now when I'm refactoring I would like to change so that I get a prompt where I write in this ID instead of changing it on every update statement (which is what I'm doing currently)

I found a snippet

set serveroutput on;
declare
  sn   varchar2(80);
begin
  sn := '&SerialNumber';
  dbms_output.put_line(sn);
end;

which works perfectly, however, I'm not sure how to use this variable now. Ideally, I would like to use it like this:

  set serveroutput on;
declare
  sn   varchar2(80);
begin
  sn := '&SerialNumber';
  dbms_output.put_line(sn);
end;
/
SELECT * FROM stupidtable where stupidcolumn = sn;

but this yields

ORA-00904: "SN": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I'm very new to Oracle and would like some help with this. Thank you :)

Answer 1

If you want to use a substitution variable in a query, then you don't need to populate it in PL/SQL and can use it directly in the query:

SELECT *
FROM   table_name
WHERE  column_name = '&substitutionVariable';

You could also use a bind variable:

SELECT *
FROM   table_name
WHERE  column_name = :bindVariable;

I want to use the same substitionVariable in a lot of UPDATE statements

Use && rather than & if you want SQL*Plus to implicitly define a variable to store the substitution variable:

SELECT * FROM table1 WHERE column_name = '&&substitutionVariable';

SELECT * FROM table2 WHERE column_name = '&&substitutionVariable';

-- Once the variable is defined you can use either single or double ampersands.
SELECT * FROM table3 WHERE column_name = '&substitutionVariable';

or, use DEFINE to explicitly define the variable:

DEFINE substitutionVariable = Something

SELECT * FROM table1 WHERE column_name = '&substitutionVariable';

SELECT * FROM table2 WHERE column_name = '&substitutionVariable';

-- Once the variable is defined you can use either single or double ampersands.
SELECT * FROM table3 WHERE column_name = '&&substitutionVariable';

Or you can use bind variables and define a variable:

VARIABLE bindVariable VARCHAR2(20);

BEGIN
  :bindVariable := 'Something';
END;
/ 

SELECT * FROM table1 WHERE column_name = :bindVariable;

SELECT * FROM table2 WHERE column_name = :bindVariable;

-- Once the variable is defined you can use either single or double ampersands.
SELECT * FROM table3 WHERE column_name = :bindVariable;