SQL Fiddle - Oracle Stored Procedure not updating rows

133 views Asked by At

I am trying to learn Oracle stored procedures and I cannot make them run properly on SQL Fiddle or DBfiddle. I don't have access to an installed Oracle database. Here is my code from http://sqlfiddle.com/#!4/e53857/5

CREATE TABLE user_1 (password varchar(10))
/
create procedure updateUsers
   ( new_p in varchar2)
as
begin
SAVEPOINT before;
  update user_1 set password = 'in proc';
  dbms_output.put_line(SQL%RowCount);
commit;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM); 
end;
/

and the query pane

insert into user_1 values ('initial');
select password from user_1;
update user_1 set password = 'from query';
SELECT password from user_1;
exec (updateUsers );
select password from user_1;

When I look at the last output below the update in the query pane has worked, the stored procedure hasn't changed anything, the value of password is still from query and there is no error message.

The line ending in the left pane is / and ; in the right pane.
I would appreciate any help and pointers.
If another online tool is easier to use for Oracle than SQL fiddle I'm open to suggestions.

1

There are 1 answers

0
AudioBubble On

I've found the solution!
schema pane, line ending / both panes

CREATE TABLE user_1 (password varchar(10))
/
create procedure updateUsers
   ( new_p in varchar2)
as
begin
SAVEPOINT before;
  update user_1 set password = new_p;
  dbms_output.put_line(SQL%RowCount);
commit;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM); 
end;
/

query pane

insert into user_1 values ('initial')/
select password from user_1/
update user_1 set password = 'from query'/
SELECT password from user_1/
begin
  updateUsers('new_pass') ;
end;
/
select password from user_1/

output

PASSWORD
initial

PASSWORD
from query

PASSWORD
new_pass

see also extension
http://sqlfiddle.com/#!4/b942cc/3