Using XML-SQL Utility (XSU), 20 of 26

Binding Values in XSU (PL/SQL)

The PL/SQL API provides the ability to bind values to the SQL statement. The SQL statement can contain named bind variables. The variables have to start with a ':' in front of them to signal that they are bind variables. The steps involved in using the bind variable is as follows,

Initialize the query context with the query containing the bind variables. For example, the following statement registers a query to select the rows from the emp table with the where clause containing the bind variables :EMPNO and :ENAME which we will bind the values for employee number and employee name later.
```
queryCtx = DBMS_XMLQuery.getCtx('select * from emp where empno = :EMPNO and 
ename = :ENAME');
```
Set the list of bind values. The clearBindValues() clears all the bind variables set. The setBindValue() sets a single bind variable with a string value. For example, we will set the empno and ename values as shown below:-
```
DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',20);
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','John');
```
Fetch the results. This will apply the bind values to the statement and then get the result corresponding to the predicate empno = 20 and ename = 'John'.
```
DBMS_XMLQuery.getXMLClob(queryCtx);
```
Re-bind values if necessary, For example to change the ENAME alone to "scott" and re-execute the query,
```
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');
```

The rebinding of ENAME will now use Scott instead of John.

XSU Example 15a: Binding Values to the SQL Statement

The following example illustrates the use of bind variables in the SQL statement:

declare
  queryCtx DBMS_XMLquery.ctxType;
  result CLOB;
begin

queryCtx := DBMS_XMLQuery.newContext(
       'select * from emp where empno = :EMPNO and ename = :ENAME');

DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'EMPNO',7566);
DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','JONES');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result); 

DBMS_XMLQuery.setBindValue(queryCtx,'ENAME','Scott');

result := DBMS_XMLQuery.getXML(queryCtx);

--printClobOut(result);
end;
/