ACCEPT/PROMPT Not Displayed during Execution!
I have a small sql script, that I want user's to be able to run; it's a lookup table for "valid values". The table has about 1000 rows, but the users only need to see the rows relevant to them. I've written a script, which works, but my ACCEPT/PROMPT statement does NOT display; though the script pauses. I do some "SET" commands beforhand, so I wonder if they are preventing the display?
Here's the basic gist of my script:
~~~~~~~~~~~~~~~~~~~~
alter session set current_schema=test_table;
set pagesize 100;
COLUMN FIRST_NAME FORMAT A15
COLUMN LAST_NAME FORMAT A25
SET LINESIZE 120
SET TRIMOUT ON
SET TAB OFF
SET VERIFY OFF
ACCEPT ID_CODE PROMPT 'Enter ID CODE: '
SPOOL /local/dir/Output.txt
select ID, FIRST_NAME, LAST_NAME, DESCRIPTION, CODE
FROM TEST
WHERE CODE LIKE '&ID_CODE
ORDER BY CODE, LAST_NAME, FIRST_NAME
/
SPOOL OFF
exit
I have a Unix Csh Script that basically does:
/usr/dt/bin/dtterm -bg black -fg green -e /local/dir/Code.bat
/usr/dt/bin/dtpad /local/dir/Output.txt &
The Code.bat looks like:
$ORACLE_HOME/bin/sqlplus $USER@TEST_SCHEMA @/local/dir/Code_Lookup.sql
When the Unix script runs, it opens a window that runs sqlplus. It then prompts for Password: and stops (An empty prompt)
if I enter the value, the script works, but I never see the actual prompt from the ACCEPT/PROMPT.
If I move the ACCEPT/PROMPT command to AFTER the SPOOL command, it doesn't change the input but does put the prompt and the value in the spool file (which is handy!)
How can I get the Prompt to display ?
Thanks,
Kevin