read user-input in plsql

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

4 thoughts on “read user-input in plsql

  1. Pete Finnigan

    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

  2. Laurent Schneider Post author

    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

Comments are closed.