How to store the input entered by user and then use it as a SQL input in DCL -- OpenVMS

Question

My requirement is -- I need to take input from the user (will take two fields as Input) and then i will store the input in a symbol.. But the problem is -- Am not able use the symbol in SQL Query...

$ start:
$ INQUIRE REF "Enter Emp_ID"
$ DEFINE/NOLOG Report_file 'REF'
$       if f$type (REF) .nes. "INTEGER"
$       then
$                       call error_menu "Error: Integer value Expected"
$               wait 0:0:0.15
$               set term/width=132
$               goto START
$       endif
$       if f$length (REF) .lt. 5 .and. f$length (REF) .gt. 5
$       then

$                       call error_menu "Error: emp_id Should be 5 digits"
$               wait 0:0:0.15
$               set term/width=132
$               goto START
$ INQUIRE FILE "Enter year"
$ DEFINE/NOLOG Report_file 'FILE'
$       if f$type (FILE) .nes. "INTEGER"
$       then
$                       call error_menu "Error: NON Integer Value..Please enter numeric value"
$               wait 0:0:0.15
$               set term/width=132
$               goto START
$       endif
$ call error_menu "Checking whether emp_id is present in Database !!!"
$ sqlplus/
   set feedback on
    define IEMP_ID = '&REF';
    define IYEAR = '&FILE'; 
select emp_id from employee where emp_id=&IEMP_ID and year = &IYEAR;
exit;

Can any one please help me..In simple words, I need to take input only one time and then I need to use it for various purposes (Mainly in many sql queries). Any possibility for doing that ?

Answer 1

Perhaps sqlplus cannot access DCL symbols. (Digital Command Language, not to be confused with Database Control Language.) An alternative is to write the commands to a temporary file and then use that as input to sqlplus. Something like:

$ ! Your code to get user input...
$ !
$ ! Generate a temporary file with the SQLplus commands.
$ open/write SQL Sys$Login:SQLCommands.tmp
$ write SQL "set feedback on"
$ write SQL "define IEMP_ID = '" + Ref + "';"
$ write SQL "define IYEAR = '" + File + "';"
$ write SQL "select emp_id from employee where emp_id=&IEMP_ID and year = &IYEAR;"
$ write SQL "exit;"
$ close SQL
$ !
$ ! Run the SQLplus commands.
$ assign/user_mode Sys$Login:SQLCommands.tmp Sys$Input
$ sqlplus
$ !
$ ! Houseclean the temporary file.
$ delete Sys$Login:SQLCommands.tmp;0
$ !
$ ! That is all.
$ exit