The Oracle XSQL Pages publishing framework is a free and simple, but powerful tool for DBA's and developers who know SQL to easily publish database information in any number of formats. It's something I'm the lead developer on at Oracle, in addition to working on the JDeveloper and BC4J products, so I'm a little biased, but I think it's keep-it-simple-stupid! design philosophy has made it popular with our users, which makes me happy. Among lots of more fundamental Oracle and XML technical stuff, my Building Oracle XML Applications book also includes extensive coverage of using XSQL pages to build applications that leverage SQL, XML, and XSLT. A third-party book called Oracle XSQL was also published on it this year. Since I work on all of XSQL Pages, BC4J, and JDeveloper, of course BC4J ships with XSQL action handlers that make it easy to publish XML-based information from BC4J view objects, and JDeveloper makes it easy to build, test, and deploy XSQL pages as well!
Today someone mailed me a question about XSQL that required a little thinking, but after getting the parameters passed correctly, it worked out-of-the box. They wanted to start with a general XSQL page template like this:
<!-- BasePage.xsql -->
<page connection="scott" xmlns:xsql="urn:oracle-xsql">
<xsql:query sql="dept" bind-params="{@binds}">
select * from {@sql}
</xsql:query>
</page>
and front this page by one or more JSP pages which passed a dynamic query fragment containing bind variable place holders in the sql parameter, along with the values of the bind variables to be bound into those place holders.
I was able to get the following JSP page examples to work, illustrating examples of dynamic queries with one and two bind variables:
<%-- oneBindVarExample.jsp --%>
<jsp:forward page="untitled1.xsql">
<jsp:param name="sql" value="emp where empno = ?"/>
<jsp:param name="empno" value="7839"/>
<jsp:param name="binds" value="empno"/>
</jsp:forward>
and:
<%-- twoBindVarExample.jsp --%>
<jsp:forward page="untitled1.xsql">
<jsp:param name="sql" value="emp where ename like '%'||?||'%' and sal > ?"/>
<jsp:param name="sal" value="2000"/>
<jsp:param name="ename" value="E"/>
<jsp:param name="binds" value="ename sal"/>
</jsp:forward>
The key to getting the parameters correct was renemembers that the value of the bind-params parameter on the XSQL action handler is evaluated as a space-or-comma-separated list of names of parameters.
9:51:16 AM 
|
