A multiline script can be passed as a series of lines. A slash creates a statement.
CREATE TABLE T
NAME CHAR(25) NULL
DROP TABLE T
Substitution Variables ( SUBSTNwhere N is 0-9) define strings that are simply 'pasted' into the statement. The database server is unaware of any Substitution Variables. There are no word boundaries defined, as this example shows. The server must recompile each row.
CREATE TABLE PARMTEST ( PARM VARCHAR(25) NULL)
Use Substitution Variables SUBST1 and SUBST2. statement? shows the string that is sent to the database server.
insert into PARMTEST values('SUBST1SUBST2')
Bind Variables (BINDNwhere N is 1-9) define input/output variables. The server sees both the statement and the Bind Variables.
Bind Variables are useful as parameters for stored procedures. The following fixture would call the stored procedure FINDNAME. FINDNAME would take one input parameter and one output parameter.
Oracle defines an alternative syntax to the standard jdbc syntax. BEGIN ... END; can include multiple calls to stored procedures. Oracle calls this an anonymous block. Think of them as stored procedures that aren't stored :). It's easy to forget to include a BEGIN ... END; in a script. They are required if you are using bind variables.