CODEWITHSUNDEEP
sqlplsqloracle10gsqlplus
jn1kk
jn1kk

Reputation: 5102

Prompt user in PLSQL

My assignment is to write a PLSQL module to insert data into database. Upon a certain condition, it may need additional information and should prompt the user for one more detail. This should be done directly in PLSQL and wording is straight from the assignment.

I've researched the topic and found some people said this cannot be done in PLSQL? But the ACCEPT PROMPT function does exist.

ACCEPT v_string PROMPT 'Enter your age: ';

While this works directly from SQLPlus, it does not work in PLSQL as it gives me this error:

PLS-00103: Encountered the symbol "V_STRING" when expecting one of the following: := . ( @ % ;

Can anyone provide some insight as to how I'm supposed to ask the user from PLSQL, only upon when a certain condition is true (the condition is checked when you get something else from the DB). To clarify, I only need help on how to accept input.

Thanks for your time.

Upvotes: 1

Views: 1615

Answers (4)

Tilen
Tilen

Reputation: 531

Well, since it's not really a part of SQL, but rather the developer tools you use, here's the ones I know:

  • SQL*Plus: &Variable
  • SQL Developer: You can make a procedure or you can use :Variable to be prompted to insert a parameter

Upvotes: 1

Luke Woodward
Luke Woodward

Reputation: 64959

There is a trick that will allow you to do something like this, but (a) it's a bit of a hack, (b) you need to be logged into the database server itself, and (c) it only works on Linux (and perhaps other flavours of Unix).

Generally, it's not possible to ask for user input in PL/SQL, especially if you're connecting to a database on a remote machine. Either your assignment is wrong or you've misunderstood it.

PL/SQL programs are designed to run on the database server, so it doesn't make sense to ask the user for input during them. Using the ACCEPT command, SQL*Plus can ask the user for input while running a script client-side. SQL*Plus will then substitute in the value entered before sending SQL or PL/SQL to the database.

Upvotes: 2

Saad Ahmad
Saad Ahmad

Reputation: 403

You cannot get user input in pure PL/SQL. It is a server-side language to express business/database logic.

The use of "&" to get input is a "sqlplus" feature and not a PL/SQL strategy. If it was an "assignment" then you need to tell the teacher that it is an invalid assignment - or maybe it was a trick question!

Upvotes: 0

harshkumar satapara
harshkumar satapara

Reputation: 75

I checked and tried following things which might helpful to you. It's just an example which I tried. You can implement your own logic.

declare
c1 varchar2(50);
begin
c1:='&enter_value';
dbms_output.put_line(c1);
exception
when others then
dbms_output.put_line(sqlerrm);
end;

'&' will help you to prompt and ask input to user. Once user feeds some input, it will assign to variable and you can use variable wherever you want.

Hopefully this helps you.

Upvotes: 0

Related Questions