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 Comments

  • 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

Leave a Reply

Your email address will not be published.