Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ACCEPT/PROMPT Not Displayed during Execution!

leonhardtkAug 5 2010 — edited Aug 5 2010
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
This post has been answered by Boneist on Aug 5 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 2 2010
Added on Aug 5 2010
4 comments
1,346 views