how to change the delimiter in sqlplus in oracle 11g

Question

I want to change the delimiter:

Can someone help me to change the delimiter in sqlplus in Oracle 11g

CREATE OR REPLACE TRIGGER test_trigger 
BEFORE INSERT ON test 
REFERENCING NEW AS NEW FOR EACH ROW 
BEGIN 
SELECT test_sequence.nextval INTO :NEW.ID FROM dual; 
END; 
/ 

this is the trigger I want to create. but after Select statement it stops because of ; is there. that is why I want to change the delimiter. I hope everyone gets the idea on this now..

Solution 1

There is nothing wrong with the syntax of your trigger. I can run it in my (vanilla) SQL*Plus environment:

SQL> CREATE OR REPLACE TRIGGER test_trigger
  2  BEFORE INSERT ON test
  3  REFERENCING NEW AS NEW FOR EACH ROW
  4  BEGIN
  5  SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
  6  END;
  7  /

Trigger created.

SQL>

And lo! the trigger works:

SQL> insert into test (col1) values ('Simples!')
  2  /

1 row created.

SQL> select * from test
  2  /

        ID COL1
---------- ------------
         1 Simples!

SQL>

All I can think is that you have some blank lines in the code which you are typing. If this is the situation you can override the default behaviour with this SQL*Plus command:

SQL>  set sqlblanklines on

Solution 2

Is this you are looking for

select q'#Oracle's quote operator#' from dual;

Q'#ORACLE'SQUOTEOPERATO
-----------------------
Oracle's quote operator

Edit 1:

CMDS[EP] {;|c|OFF|ON}

   Change or enable command separator - default is a semicolon (;)

Solution 3

If you're using SQLPlus and not some other tool I'm not sure what the root issue is, but here's the answer to your workaround question:
To change the command delimiter from the default value of ; use

SQL> set cmdsep /
SQL> show cmdsep
cmdsep "/" (hex 2f)

To restore the default value after you've created your trigger:

SQL> set cmds off
SQL> show cmds
cmdsep OFF