iSQL*Plus logo

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index

Creating Dynamic Reports

You can create dynamic reports, and pass variables to scripts by sending iSQL*Plus a request to run a script from a URL. The script must be available through HTTP, HTTPS or FTP. iSQL*Plus executes the script, using any HTML form field values as parameters, and returns the results in a new web browser window.

You are not prompted for undefined variables. You should take care that there are no variables that have not been defined in your script, or explicitly passed as parameters.

You can also include username and password information in the request. You should carefully consider the security implications of including usernames and passwords in HTML files. If you do not include a username or password, iSQL*Plus prompts you to enter login information when you run the script.

The following examples use the EMP_DETAILS_VIEW view of the Human Resources (HR) sample schema. This schema contains personnel records for a fictitious company. It may be installed as part of the default Oracle9i installation using the Oracle Database Configuration Assistant.

For further information about the sample schemas included with Oracle9i, see the Oracle9i Sample Schemas guide.

Creating a Dynamic Report

Create and save the following script to a file called script.sql on your Oracle HTTP Server.

SET PAGESIZE 200
SELECT *
FROM EMP_DETAILS_VIEW
ORDER BY LAST_NAME, EMPLOYEE_ID
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<A HREF="http://machine_name.domain/isqlplus?script=http://machine_
name.domain/script.sql">
Run Employee Report</A> </BODY> </HTML>

Replace machine_name.domain with the host and domain names of your Oracle HTTP Server. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser and click on "Run Employee Report". iSQL*Plus requests your username and password. Log in to iSQL*Plus. iSQL*Plus executes the script and displays the results in your web browser.

Creating a Dynamic Report with Parameters

Create and save the following script to a file called employee_name.sql on your Oracle HTTP Server.

SET VERIFY OFF
SET PAGESIZE 200
SET FEEDBACK OFF
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee(s) with Last Name like &last_name%</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE UPPER(last_name) LIKE UPPER('&last_name%')
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE><em>i</em>SQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<H2>Query by Last Name</H2>
<FORM METHOD=get ACTION="http://machine_name.domain/isqlplus">
<INPUT TYPE="hidden" NAME="script" VALUE="http://machine_name.domain/employee_
name.sql">
Enter last name of employee: <INPUT TYPE="text" NAME="last_name" SIZE="20">
<INPUT TYPE="submit" VALUE="Run Report">
</FORM>
</BODY>
</HTML>

The name of the INPUT TYPE should be the same as either a column or substitution variable in your script, for example

<INPUT TYPE="text" NAME="last_name" SIZE="20">

maps to the substitution variable &last_name in the employee_name.sql script.

Replace machine_name.domain with the host and domain names of your Oracle HTTP Server. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. iSQL*Plus executes the script and displays the results in your web browser.

Creating a Dynamic Report with Parameters and Login Details

Create and save the following script to a file called employee_id.sql on your Oracle HTTP Server.

SET VERIFY OFF
SET PAGESIZE 200
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee Number &eid</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = &eid
/

Create an HTML file which contains:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><em>i</em>SQL*Plus Report</H1>
<H2>Query by Employee ID</H2>
<FORM METHOD=get ACTION="http://machine_name.domain/isqlplus">
<INPUT TYPE="hidden" NAME="userid" VALUE="hr/your_secret_password">
<INPUT TYPE="hidden" NAME="script" VALUE="http://machine_name.domain/employee_
id.sql">
Enter employee identification number: <INPUT TYPE="text" NAME="eid" SIZE="10">
<INPUT TYPE="submit" VALUE="Run Report">
</FORM>
</BODY>
</HTML>

Replace machine_name.domain with the host and domain names of your Oracle HTTP Server, and hr/your_secret_password with a valid userid and password. Save the HTML file on your Oracle HTTP Server.

Load the HTML file in your web browser. Enter an employee identification number in the text field, for example, "105". Click the Run Report button. iSQL*Plus executes the script and displays the results in your web browser.

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index