Input

[AndreasAuer]

Is it possible to create a input-dialog with SQL? In Access I can use the PARAMETERS clause. Is there something comparable in Oracle?

 

[stevecal]

You can do this in a sql script with the accept n prompt method like this:

accept 1 prompt "Enter First Name:"
accept 2 prompt "Enter Last Name:"

You can then reference the variable values with &&1, &&2 etc.

 

[AndreasAuer]

Could you please write a full example, I tried it, but it didn't work

 

[stevecal]

This is a very simple example of how you would do this (it'll select the first value from the table/field you input):

set serveroutput on
accept 1 prompt "Enter a table name: "
accept 2 prompt "Enter a field name: "
define dot = '.'
declare
tmp &&1&&dot&&2%type;
begin
select &&2 INTO tmp from &&1 where rownum < 2;
dbms_output.put_line(tmp);
end;
/
undefine 1
undefine 2
set serveroutput off

 

[raygg]

The O'Reilly Associates book 'Oracle SQL*Plus' has great info on this - including the solution to swapping the values of bound and unbound variables between sql*plus and PL/SQL, which is a little bit tricky. See

http://oracle.oreilly.com/

 

[AndreasAuer]

I pasted your complete example into sqlworksheet and got these results (at end of post). Are you sure, that this work? I didn't find &quot;ACCEPT&quot; in the reserved words-list of the Oracle HTML-Manual. Maybe it don't work in my version 8.0.5 or not on NT 4.0?


SQLWKS> set serveroutput on
Server Output ON
SQLWKS> accept 1 prompt &quot;Enter a table name: &quot;
2> accept 2 prompt &quot;Enter a field name: &quot;
3> define dot = '.'
4> declare
5> tmp &&1&&dot&&2%type;
accept 1 prompt &quot;Enter a table name: &quot;
*
ORA-00900: invalid SQL statement
SQLWKS> begin
2> select &&2 INTO tmp from &&1 where rownum < 2;
3> dbms_output.put_line(tmp);
4> end;
5> /
select &&2 INTO tmp from &&1 where rownum < 2;
*
ORA-06550: line 2, column 8:
PLS-00103: Encountered the symbol &quot;&&quot; when expecting one of the following:

( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current distinct max min prior sql stddev sum unique
variance cast the
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
SQLWKS> undefine 1
2> undefine 2
3> set serveroutput off
4>
undefine 1
*
ORA-00900: invalid SQL statement

 

[stevecal]

Andreas,

The closest I can get is with SQL*PLUS on 8.06 and it's fine. You can't just paste it in, you have to save and then execute it as a sql file.... This would explain your output...

e.g.
sqlplus> @test.sql;

 

[sem]

The script provided by stevecal is for sql*plus, not for sqlworksheet.