sqloraclesqlplus

sqlplus: how to execute a query spanning multiple lines

I am using Oracle 19c. I can get the following to work:

variable max_id number;

exec select 41 into :max_id from dual;
    
begin
dbms_output.put_line(:max_id);
dbms_output.put_line(:max_id+1);
end;

But splitting the select across multiple lines produces an invalid SQL statement error at the new line:

variable max_id number;

exec select 41
into :max_id from dual;  -- invalid SQL statement error points here
    
begin
dbms_output.put_line(:max_id);
dbms_output.put_line(:max_id+1);
end;

How would I get exec to work with a multiline query?

Solution

  • You can use the line continuation character:

    You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing Return. If you wish, you can type a space before typing the hyphen.

    So you can add a hyphen at the end of the first line:

    exec select 41 -
into :max_id from dual;

    Or just use a normal anonymous block instead of the exec shorthand:

    begin
select 41
into :max_id from dual;
end;
/

    ———-

    I realise this is probably a contrived example, but in this case you can also simplify to:

    exec :max_id := 41;