How can I read user input in plsql?
kind of
begin
write('Enter a value for x : ');
read(x);
write('you enterred '||x);
end;
/
the short answer is : you cannot do that.
Ok, let’s try to do it in Linux !
$ cat interactiveplsql.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 enterred '||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 enterred ABC123
Nice trick!
Patrick
Very nice Laurent but can you also do it in Windows? – possibly I guess with cygwin but the I/O I know doesnt work as well as Unix. Also you do realise that this is a security risk opening a channel to the database server like this, therefore i wouldnt advise anyone to do this on a production server. There are also other possibilities in this theme that would work that would also present security risks.
cheers
Pete
The security issue is to have physical access to your database server… This you usually do not have in production.
It must possible to do it with DBMS_PIPE (in Windows and Unix), but I have not tried yet
wow nice trick
helpful