Scripting Issues with iSQL*Plus

Using i SQL*Plus, you can execute your scripts via its browser interface. However, there are differences between command-line SQL*Plus and browser-based SQL*Plus, and not all scripts can be made to execute in both environments.

One issue that you encounter is spool files. Browser-based SQL*Plus runs on an application server. For reasons of security, i SQL*Plus doesn't allow you to write files to that server. Thus, you cannot spool output. After all, i SQL*Plus can hardly spool output through your browser to a file on your PC. The only way to capture output is to copy and paste it from your browser window to a file, and that's only feasible for small amounts of data.

Copying i SQL*Plus output from a browser window and pasting it into another application works best after you issue SET MARKUP HTML ON PREFORMAT ON. Otherwise, you'll be trying to copy an HTML table rather than the output that you desire .

Another problem you'll encounter when executing scripts from i SQL*Plus is that many SQL*Plus commands are not supported by i SQL*Plus. These include commands such as HOST that would present a security risk by letting you access the application server at the operating system level, and commands such as EDIT that don't apply in a browser-based environment.

The EDIT command isn't implemented in i SQL*Plus because your script is sitting in an easily edited text box on your browser page.

Figure 8-4 shows the prompt that you'll receive when you execute ex8-4b.sql from i SQL*Plus (by issuing the command @http:// gennick .com/sqlplus/ex8-4b ). The prompt is the result of the same ACCEPT command that you saw in Example 8-4. Each ACCEPT command, and each prompt that SQL*Plus generates automatically in response to a script's use of a substitution variable, results in a separate HTML form to which you must respond by typing in a value and clicking Continue.

Figure 8-4. An iSQL*Plus prompt in response to an ACCEPT command


Figure 8-5 shows a portion of the output after I supplied " employee " as the table name and clicked Continue. Even this relatively benign script has a problem with a command not supported by i SQL*Plus. You can see the error message resulting from my use of SET NEWPAGE 1. Fortunately, the script continues to execute despite the one, invalid command, and you can see the description of the employee table, formatted into an HTML table, following the error message.

Figure 8-5. An error from executing a command not supported by iSQL*Plus


The command reference in Appendix A indicates which commands are not allowed in i SQL*Plus.

i SQL*Plus is a wonderful thing, but its browser-based interface seems better suited to running interactive commands. If you need to run scripts, and especially when you wish to schedule those scripts using cron or some other scheduling software, stick to command-line SQL*Plus.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: