oracle - Parameterizing adhoc scripts -

in sql server had set of diagnostic scripts , made sure declare variables identifiers other selects , updates leverage them. i'm having trouble adopting pattern in oracle.

i might have 4 or 5 select queries, , updates may uncomment once i've verified results. want see results of select queries in output.

i using sql developer.

first tried using define block, seems must paired begin/end block, , once query inside block, seems becomes cumbersome view results. examples i've seen either involve setting cursor iterating on cursor print results, or must print individual values more cumbersome.

so instead tried using variable's since can reference them without declare/begin/end, having trouble setting value of variable:

variable customerid number; customerid := 1234; 

but error:

error starting @ line : 5 in command - customerid := 1234 error report - unknown command

i tried

select t.customerid :customerid customer t t.customerid = 1234 

and get:

sql error: ora-01006: bind variable not exist 01006. 00000 - "bind variable not exist"

my goal have id declarations @ top set values, , able run script , adhoc selects appear in output.

you need set bind variable in pl/sql context, either execute syntactic wrapper:

variable customerid number; exec :customerid := 1234; 

or more explicitly:

variable customerid number; begin   :customerid := 1234; end; / 

which (almost) equivalent, more convenient if want set multiple variables. can populate bind variabke query too, attempted, needs in pl/sql context:

begin   select t.customerid :customerid   customer t   t.customerid = 1234; end; / 

notice colon before customerid, indicating bind variable, in of those. need when reference later, e.g. in sql query (which doesn't need in pl/sql block):

 select * customer customerid = :customerid; 

you can use same mechanism in updates later. exception using colon if want see value of variable; select :customerid dual, there ability to

print customerid 

that's more useful if variable refcursor.

---

define different mechanism, substitution variables rather bind variables. don't need use pl/sql blocks either:

define customerid=1234 select * customer customerid = &customerid; 

notice there no colon time. , note if variable string, need enclose in quotes when use it:

define name=aaron select * users first_name = '&name'; 

you can use result of query populate substitution variable, using the new_value syntax.