In unix, I set the SQLPATH environment variable to
include the path to my  master script and the paths to the subdirectories as well.  I find that the @@./subdirectory syntax works when I do this.

I do something like this - 

setenv SQLPATH /auto/bcf/edi/data:/auto/bcf/edi/data/ins

I then run sqlplus from some other directory 

SQL> @crorder 

This executes crorder.sql, which happens to live in /auto/bcf/edi/data.  The script contains several calls like  this

@./ins/insbulklineitem
@./ins/prepacklineitem

which work fine.  If I remove the /auto/bcf/tst/edi/tst/data/ins subdirectory from the SQLPATH, sqlplus reports that it is unable to find insbulklineitem and insprepacklineitem.  It doesn't make a lot of sense to me, but it works which is all I am looking for.  Perhaps you can do something similar in Windows. 

I decided to do it this way:

SET TERM ON
Update batch
set status='P'
where batchno between substr(&batch1,1,8) and substr(&batch2,1,8);
prompt Please type COMMIT or ROLLBACK;
--commit;

SQL> select substr(&batch1,1,8) from dual;
Enter value for batch1: 200124450000

SUBSTR(2
--------
20012445

this way whatever digits are added after the first 8 are
truncated..they just need to make sure the first 8 digits
are correct...if not then ROLLBACK;

 

This is just a mechanism for users to validate or update
batches before posting data to tables. Before I created
the batch menu screen(see below)...they used to cut
and paste all the sql statements that were used for
verification. In the case I presented here users would actually type out the entire update statement.

When I came on board I asked them if they would like
me to create a SQLPLus Menu were they could select
a number, answer prompts and run the same sql scripts..
they were all..."you can do that???".

so I created this:

set serveroutput on size 1000000 
set echo off verify off trimspool on term on linesize 1000
cl screen
prompt ***************************************************
prompt * Select Batch Verification Processing Option:
prompt *  1.  Verify Client
prompt *  2.  Verify Appealcode
prompt *  3.  Verify Dollars and Count
prompt *  4.  Update Batch Status
prompt *  5.  Verify Batch Flags
prompt *  6.  Verify NULL Acknowledgement
prompt *  7.  Verify NOT LIKE Appealcode
prompt *  8.  Verify NULL GiftType
prompt *  9.  Verify T_Date
prompt * 10.  Verify Year
prompt * 11.  Verify IntCode ATL NATL DECD
prompt * 12.  Verify IntCode 998 UDAD
prompt * 13.  Exit
prompt ***************************************************
accept process prompt ">"
SET TERM OFF
STORE SET saved_settings REPLACE 
SET TERM ON
SET FEEDBACK OFF HEADING OFF 
SPOOL query.sql
select decode(&process,1,'START checkClient',2,'START checkAppealcode',
3,'START checkDollars_Count',4,'START upd_BatchStatus',5,'START check_BatchFlags',
6,'START NullAcknowledgement',7,'START checkAppealcodeNL',
8,'START checkGifttype_Null',9,'START checkT_Date', 10, 'START checkYear_Null',
11,'START checkIntcode',12,'START checkIntcode2',13,'prompt','Please Enter Correct Selection')
from dual;
SPOOL OFF
START saved_settings
START query


in the case I presented here:

**************************************************
* Select Batch Verification Processing Option:
*  1.  Verify Client
*  2.  Verify Appealcode
*  3.  Verify Dollars and Count
*  4.  Update Batch Status
*  5.  Verify Batch Flags
*  6.  Verify NULL Acknowledgement
*  7.  Verify NOT LIKE Appealcode
*  8.  Verify NULL GiftType
*  9.  Verify T_Date
* 10.  Verify Year
* 11.  Verify IntCode ATL NATL DECD
* 12.  Verify IntCode 998 UDAD
* 13.  Exit
**************************************************
>4

START upd_BatchStatus
Enter value for batch1: 20012445
Enter value for batch2: 20012445

1 rows updated.

Please type COMMIT or ROLLBACK
SQL> rollback;

Rollback complete.

SQL> 
 
users are happy...when they're happy, I'm happy!

This is not a situation where a more complex solution
is needed. 







 

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> declare
i NUMBER := 1;
begin
   loop 
    insert into dept values('&deptno','&dname','&loc');
    i := i+1;
    EXIT WHEN i > 2; 
   end loop;
end;
/
Enter value for deptno: 50
Enter value for dname: MANAGEMENT
Enter value for loc: VIRGINIA
old   5:     insert into dept values('&deptno','&dname','&loc');
new   5:     insert into dept values('50','MANAGEMENT','VIRGINIA');

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MANAGEMENT     VIRGINIA
        50 MANAGEMENT     VIRGINIA

6 rows selected.

My doubt is why is that inputs are not prompted second time in the loop inspite of using &?

thanks