what is Oracle command 'o;'

Question

I am using the oracle version.

Oracle Database 11g Release 11.2.0.1.0

I accidentally ran the command o; in oracle delveloper.

The result is as below.

The PL/SQL procedure completed successfully.

not spooling currently
The sqlcl_int_runme alias has been removed.

I don't know what I did....

First of all, there seems to be no problem with basic table CRUD.

Has anyone had this experience? I need an explanation of what happened...

I assume "oracle delveloper" means "SQL Developer", the database interface provided by Oracle. I will edit your tag to add SQL Developer; Jeff Smith, the product manager (yes, that Jeff Smith!), sometimes participates on this forum, and he may be the best person to answer your question. I will also add the `sqlcl` tag, since the question seems to have something to do with it too. (SQL Developer and SQLcl are maintained together, so this should not be surprising.) — , Aug 30 '22 at 05:35

score 4 · Answer 1 · answered Aug 30 '22 at 12:10

It's an alias.

We copied over some popular commands from postgresql to SQLcl, one of those was 'o'

From the post docs

\o or \out [ filename ] \o or \out [ |command ] Arranges to save future query results to the file filename or pipe future results to the shell command command. If no argument is specified, the query output is reset to the standard output.

If the argument begins with |, then the entire remainder of the line is taken to be the command to execute, and neither variable interpolation nor backquote expansion are performed in it. The rest of the line is simply passed literally to the shell.

“Query results” includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d); but not error messages.

SQL> alias
\!          \?          \c          \cd         \d          \dp         \dt         \dt+        \e          \echo       \encoding   \i
\o          \p          \prompt     \q          \qecho      \r          \save       \timing     \w          \z          clear       cls
cpu         fuzzy       gglag       locks       sessions    tables      tables2     topsql
SQL> alias list \o
\o NULLDEFAULTS psql - desc \o [FILE_NAME] - turn spool log file on (or off if no FILE_NAME given)
--------------------------------------------------------------------------------------------------


Declare
  maxpos number:=null;
BEGIN
  if (:sqlcl_int_first is null) then
    :sqlcl_int_runme:='spool off';
  else
    :sqlcl_int_runme:='spool '||:sqlcl_int_first||' ';
  end if;
end;
/
alias NULLDEFAULTS sqlcl_int_runme=:sqlcl_int_runme;
sqlcl_int_runme
alias drop sqlcl_int_runme

To see it in action...

SQL> set sqlformat csv
SQL> o stackoverflow.csv

PL/SQL procedure successfully completed.

Alias sqlcl_int_runme dropped
SQL> select * from regions;
"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"

SQL> o

PL/SQL procedure successfully completed.

Alias sqlcl_int_runme dropped
SQL> !dir stackoverflow.csv
 Volume in drive C is System
 Volume Serial Number is F897-6A6F

 Directory of c:\sqlcl\22.2.1\sqlcl\bin

08/30/2022  08:09 AM               170 stackoverflow.csv
               1 File(s)            170 bytes
               0 Dir(s)  190,156,173,312 bytes free

SQL> !type stackoverflow.csv
Alias sqlcl_int_runme dropped
"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"


PL/SQL procedure successfully completed.

Thanks so much for the reply!!!!!! May I ask if I understand the example well? Is it correct that all DB operations executed after changing sqlformat to csv format are output to a file called stackoverflow.csv????? Then ```o;``` After executing , all DB operations are output to the console, not to stackoverflow.csv! is it correct? — mjk, Sep 01 '22 at 04:37
pretty much except you'll see output at both console AND the file, the file will be closed once you say you're done with the follow-up call to /o --- this is the equivalent of saying spool file and spool off — thatjeffsmith, Sep 01 '22 at 12:26

what is Oracle command 'o;'

1 Answers1