Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  
share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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;
/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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