0

I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back from my select, what am I doing wrong?

SQL> select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';

ACTIVITY_MESSAGE
--------------------------------------------------------------------------------
REGEXP_REPLACE(ACTIVITY_MESSAGE,'NULL',NEW_VALUE)
--------------------------------------------------------------------------------
Added: null
Added: 1234567891

Added: null
Added: 1234567890 Expires: 12/31/2016

SQL> begin;
 2  select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';
 3  commit;
 4  
1

Oracle's transactions are implicit in sqlplus you're already in one and don't have to start it. This means you can just do

select ... update .. rollback ... select...

if you do an exit without doing exit rollback it will commit on exit. begin is not necessary here.

1

In Oracle BEGIN starts a PL/SQL block. In other words, after BEGIN you ought to provide a text of a program that is written in a procedural language that is somewhat different than SQL (although it bears some similarities). Your sqlplus had read everything but it was not yet parsing or executing anything, it was waiting for an END and a line containing only a slash / like this:

set serveroutput on
BEGIN
  dbms_output.put_line('Hello world');
END;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.