Search code examples
oraclesqlplussqlcl

sqlcl vs sqlplus pl/sql compatability

I apologize up-front for this super-lightweight question, but I'm missing something when starting to work with sqlcl as a potential replacement for sqlplus.

sqlcl is compelling, but I'm troubled in that I'm missing how to run anonymous-blocks interactively. The below example works fine when saved as Little-Anonymous-Block.sql and run in sqlcl via @Little-Anonymous-Block.sql, but the raw pl/sql fails with the the below PLS-00103.

Little-Anonymous-Block.sql:

BEGIN
  DBMS_OUTPUT.PUT_LINE('This anonymous-block ran in sqlcl!');
END;
/

Running as a Script:

SQL> SET SERVEROUTPUT ON;

SQL> @Little-Anonymous-Block.sql;

This anonymous-block ran in sqlcl!

PL/SQL procedure successfully completed.

But running ad-hoc:

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE('This anonymous-block ran in sqlcl!');

3 END;

4 /

gives:

Error starting at line : 1 in command -

BEGIN

DBMS_OUTPUT.PUT_LINE('This anonymous-block ran in sqlcl!');

END;/

PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.

sqlcl appears to be conjoining the "/" with the block-terminating END; The same command works fine in sqlplus.

Can you tell me, how do I interactively run anonymous blocks in sqlcl? I've got the early-adopter release from 20160513. java 8.0_77. Apologies for this question if its in the sqlcl manual, I didn't find much to go by on the oracle sqlcl-page.

Solution

  • I've found that you can run an anonymous block with exec but that has its limitations (e.g. all code on one line). SQLcl session

    As far as I can tell what you've found is a bug. A workaround would be to end your block with a . then execute the buffer with a / as shown below:

    enter image description here