SQL statements that cannot retrieve values from or send values to the database are not terribly programmable. That's where host variables come in. They are Java variables prefixed with ":", placed inside the SQL statement, that can retrieve and/or send data values.

String name = "SCOTT";

Double raise = new Double(1.08);

Double salary;

#sql { UPDATE emp SET sal = sal * :raise WHERE ename = :name };

#sql { SELECT sal INTO :salary FROM emp WHERE ename = :name };

But SQLJ is more flexible than that — you can use Java expressions instead of host variables. Just make sure that the host expression is enclosed between ":(" and ")".

String[] emps = new String[] { "Scott", "Miller", "King" };

double[] raises = new double[] { 8.0, 4.0, 0.0 };

for (int i=0; i<emps.length; i++)

#sql { UPDATE emp SET sal = sal * :(1.0 + raises[i] / 100.0)

WHERE ename = :(emps[i].toUpperCase()) };

int j=0; double[] s = new double[emps.length];

while (j<emps.length) {

#sql { SELECT sal INTO :(s[j]) FROM emp

WHERE ename = :(emps[j++].toUpperCase()) }; }

• All host expressions are evaluated once, and only once, from left to right (including side-effects) before any values are sent to the database.
• See also SQLJ Developer's Guide and Reference, Chapter 3, Section "Evaluation of Java Expressions at Runtime".
• An application with host expressions is [Oracle Home]/sqlj/demo/ExprDemo.sqlj.

1. (*) You can use host expressions where values are expected. Write a SQLJ statement with a host expression in an illegal place. Translate and run it. What happens?
2. (*) A host expression in an INTO list must be able to receive a data value. Write a host expression that is not legal in an INTO list. What happens when you compile and run your program?
3. (**) Come up with more SQLJ statements that demonstrate that SQLJ evaluates host expressions from left to right.
4. (*) Show that you can use SQL comments in SQL text (between { and }), and that you can use Java comments inside of Java host expressions in SQLJ statements.