Interactive PL/SQL to accept input Oracle Tips by Burleson Consulting

 

Question:  I want to write interactive PL/SQL that accepts user input for my SQL.  How do I write PL/SQL to accept user input?  I want to collect parameters at runtime and pass them to a PL/SQL stored procedure.

 

Answer:  In SQL*Plus we have the prompt and accept syntax, but there is no direct way to make PL/SQL interactive without using PHP, Apex, Java, etc.

 

For making PL/SQL interactive with SQL*Plus, Laurent Schneider (author of the book Advanced SQL Programming) has this code example for interactive PL/SQL:

 

set feedb off ver off
col i nopri new_v i
col script nopri new_v script
select 0 i from dual where 1=0;
select to_char(0&i+1,'FM0') i,decode(0&i,5,null,'x.sql') script from dual;
undef col1
undef col2
insert into t(col1,col2) values (&col1,'&col2');
@&script

 

Laurent Schneider also has this Linux shell script technique for accepting user input into PL/SQL:

 

set feedb off

create or replace directory tmp as '/tmp';

declare
inFile utl_file.file_type;
outFile utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile := utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x : ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you entered '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/

quit

$ mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger @interactiveplsql &
);

 

cat>/tmp/in
 

Enter a value for x :
ABC123
you entered ABC123