1

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
alexgibbs
  • 2,430
  • 2
  • 16
  • 18
  • Certainly, if you remove the `/`, it will then execute the annonymous block in sqlcl. Also, if you enter `show all`, you should see the `SQL*Plus mode off`. I suspect if you turn that on, you should get the behavior you are used to. – Patrick Bacon May 18 '16 at 22:06
  • Thanks @PatrickBacon about SQL\*Plus mode. I'll run with that. However, removing the terminator does not get anon blocks to execute. sqlcl does not detect the block end (behavior much like SQL\*Plus). Any thoughts on that? Is there a good sqlcl doc anywhere? "HELP /" doesn't have much. – alexgibbs May 18 '16 at 22:23

2 Answers2

4

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

tale852150
  • 1,618
  • 3
  • 17
  • 23
2

This was indeed a bug. It should be fixed in the latest release on OTN

BARRY@orcl☘ >BEGIN
  2  DBMS_OUTPUT.PUT_LINE('This anonymous-block ran in sqlcl!');
  3  END;
  4  /

PL/SQL procedure successfully completed.

BARRY@orcl☘ >l
  1  BEGIN
  2  DBMS_OUTPUT.PUT_LINE('This anonymous-block ran in sqlcl!');
  3* END;
BARRY@orcl☘ >
Barry McGillin
  • 465
  • 5
  • 11