iSQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88826-01 |
|
Using iSQL*Plus , 6 of 9
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.
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.
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.
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|