Normally Oracle will scan SQL for variables and prompt for the value at runtime, to disable this feature use the “set scan off” command:

   1  set scan off;
   2  
   3  select * from links where href = 'http://...&....';

   1  GRANT DELETE,INSERT,SELECT,UPDATE ON charlie_production.* TO 'munger'@'localhost' IDENTIFIED BY 'xxx';

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

   1  COLUMN your_column new_value your_variable;
   2  
   3  SELECT DISTINCT (your_column)
   4                                   FROM ...
   5                                  WHERE ...;
   6  
   7  SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

A PL /SQL example of a for loop that prints out the primary key for all rows returned by a query:

   1  DECLARE
   2    ID VARCHAR2(50);
   3  BEGIN
   4      DBMS_OUTPUT.ENABLE (1000000);
   5      FOR current_row IN (SELECT id FROM your_table)
   6  	LOOP
   7  		ID := current_row.id;    -- Assign value to variable
   8  		DBMS_OUTPUT.PUT_LINE( '' || ID);
   9  		DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);
  10  	END LOOP;
  11  END;
  12  /