Re: SQL*Plus Command File questions

From: Jonathan G Gennick <gennick_at_worldnet.att.net>
Date: 1997/02/24
Message-ID: <3312095a.16417576_at_netnews.worldnet.att.net>#1/1


l120bj_at_aol.com (L120bj) wrote:

>A quick way of introducing the conditional execution is to add the
>prompted variable to the where clause for the subseqent selects
>eg
>prompt &&Continue
>
>select value
>from table
>where .....
>and upper('&&Continue') = 'Y'
>/

This is fine, and I do it in a couple of places. But I really need to be able to execute a number of queries and abort the process if it fails. And if the process is aborted, it would be nice to display a message. I also need to ask the user halfway through if they want to do the remaining queries. For example:

	spool file.txt
	select from one_table.
	spool file2.txt
	select form another_table.

	Ask user if he wants to delete. 
	if user does want to delete then
	  delete from one_table
          delete from another_table
        end if

I've ended up doing something with nested SQL files, but it's a pain in the neck. The big problem with above is that do want to stop at the first error. But if I just do "whenever sqlerror exit fialure commit", then SQL*Plus just closes its window and shuts down before the poor user ever has a chance to read any error message.

Jonathan Received on Mon Feb 24 1997 - 00:00:00 CET

Original text of this message