Bind Variable Parameterization in Prepared Statements (Dynamic SQL)

Go Up to Valid Content in the SQL Editor

You can provide bind variable values when executing scripts containing prepared statements. Bind variables, or parameterized literals, can help optimize explain/execution plan usage by minimizing the requirement to “hard parse” queries or statements that differ by one or more literal values.

In scripts, DBArtisan recognizes named or unnamed variables that use the following notation:

DBMS Platform/Driver Type	Notation
Oracle with native driver	:<name> or :<number>
Oracle With JDBC driver	?
All other platforms/drivers	?

For example:

 SELECT * FROM MYTABLE WHERE MYCOLUMN = ?

OR

 CALL MYPROC ( :a, :b, :c )

Note: Support for bind variables is enabled on a editor window-by-editor window basis. Bind variable parameterization is enabled by setting the Prepare Batch Query Option, available on all DBMS platforms. For details, see Setting up the Execution Environment with Query options.

For an enabled SQL Editor window, when using execution options, whenever a statement containing bind variable notation is encountered, a Bind Variables dialog opens.

For each bind variable in the current statement, the dialog lets you specify:

• A type
• An IN, OUT, or INOUT designation for elements such as function or procedure parameters or arguments, variables used in INTO clauses of an INSERT statements and variables used in RETURNING clauses of an Update statements.
• A value

You can then execute, skip the statement, or abort execution for the currently running script.