asked on

PL/SQL input Y/N

I have a piece of PL/SQL that I want to ask the user to confirm a commit, displaying an 'Is this ok?' message and then either committing or rolling back depending on whether they enter Y or N. This is the code so far. Can anyone help fill in the gaps?

Thanks.

-- Ask user if it is ok to proceed

dbms_output.put_line('Do you want to save these changes?');
      return;

proceed:= YorN;

if proceed = 'Y' then
      dbms_output.put_line('Saved');
      commit;
else
      dbms_output.put_line('Cancelled');
      rollback;
end if;

schwertner

PL/SQL can only display text on the screen.
It has no READ functionality.
To do what you want you have to use VB, C, Java.
There is another combination - to use SQL*Plus, but your user has to install Oracle client
on his machine.

paquicuba

<< dbms_output.put_line('Do you want to save these changes?'); >>

The above expression only prints a string of characters to the screen, it does not open a cursor for input, so you won't be able to prompt a user while implementing your subprogram. Either you prompt the user for input before executing your subprogram using a tool like SQL*Plus or use a Oracle Forms.

paquicuba

Since Oracle Forms has its own engine, it doesn't save new records to the database until you commit your form, so you can prompt your user for commit and depending on the answer issue a "commit_form" or rollback.

sujit_kumar

---test1.sql
declare
n number := &empno;
begin
update emp_1 set ename = 'SUJIT' where empno = n;
end;
/

---test2.sql
declare
comm varchar2(100) := '&COMMIT';
begin
if comm = 'Y' then
commit;
else
rollback;
end if;
end;
/

---test3.sql
@test1.sql;
@test2.sql;
/

RESULT
---------
SQL> @test3.sql
Enter value for empno: 1100
old 2: n number := &empno;
new 2: n number := 1100;

PL/SQL procedure successfully completed.

Enter value for commit: Y
old 2: comm varchar2(100) := '&COMMIT';
new 2: comm varchar2(100) := 'Y';

PL/SQL procedure successfully completed.

Sujit

ASKER CERTIFIED SOLUTION

sujit_kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial