Select with inline function fails in SQL and Command Window

[Odd Harry Ophaug]

In Oracle 12c we can write inline functions within a select statement. When running such a select in SQL or Command Window, the statement is broken into several pieces. A simple example:

with function IC (a_ in varchar2) return varchar2 is
begin
return initcap(a_);
end;
select IC(username) from all_users;

This is broken into three pieces beacuse of the ;s, and each part of course fails.
This works in SQL*Plus if I define the SQLTERMINATOR to something different from ;. Oracle SQL Developer executed the statement without problems.

Is there a way to make PL/SQL Developer run such a command?

 

[mike]

I suspect that if you uncheck the "AutoSelect statement" preference under the SQL Window preferences, it would work. You might have to manually select the whole statement. The AutoSelect statement preference assumes that the semi-colon is the end of a select statement.

It's not ideal, but it would get you by.

 

[Odd Harry Ophaug]

I have tried both check and uncheck AutoSelect in my prefs. Marking the whole statement does not work either.

 

[Gustavo]

Doesn't it work in a Test Window?

 

[Odd Harry Ophaug]

Sorry for the late reply: Yes, the inline function works fine in a test window.
/Odd Harry

 

[Kevin McCabe]

Was having the same problem as OP.

with function IC (a_ in varchar2) return varchar2 is
begin
return initcap(a_);
end;
select IC(username) from all_users;

 

[Stephan B]

To have autoselect work you must put a / on the line after the statement:

with function IC (a_ in varchar2) return varchar2 is
begin
return initcap(a_);
end;
select IC(username) from all_users
/

This currently works for me.