iSQL*Plus User's Guide and Reference
Release 9.0.1

Part Number A88826-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Using iSQL*Plus , 6 of 9


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 Uniform Resource Identifier (URI). The script must be available through HTTP. iSQL*Plus executes the script, using any HTML form field values as parameters, and returns the results in a new browser window.

You will not be 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.

Example 4-1 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>iSQL*Plus Report</H1>
<A HREF="http://host.domain/isqlplus?script=http://host.domain/script.sql">
Run Employee Report</A> </BODY> </HTML>

Replace host.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.

Example 4-2 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>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1>iSQL*Plus Report</H1>
<H2>Query by Last Name</H2>
<FORM METHOD=get ACTION="http://host.domain/isqlplus">
<INPUT TYPE="hidden" NAME="script" VALUE="http://host.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 host.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.

Example 4-3 Creating a Dynamic Script 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>iSQL*Plus Report</H1>
<H2>Query by Employee ID</H2>
<FORM METHOD=get ACTION="http://host.domain/isqlplus">
<INPUT TYPE="hidden" NAME="userid" VALUE="hr/your_secret_password">
<INPUT TYPE="hidden" NAME="script" VALUE="http://host.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 host.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.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback