Thanks for the question, Khizer.

Asked: January 16, 2016 - 5:57 pm UTC

Last updated: January 18, 2016 - 11:55 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have created a function in an anonymous block which I am trying to use in a SELECT. I get an error saying -

function 'F_TWO' may not be used in SQL.

PLSQL I used for testing is -

DECLARE
v_ret varchar2(5);

function f_one
return varchar2
AS
begin
return 'one';
end f_one;

function f_two
return varchar2
AS
v_two varchar2(5) := f_one;
begin
v_two := f_one;
--select f_one into v_two from dual;
return v_two;
end f_two;

begin
--v_ret := f_two;
select f_two into v_ret from dual;
dbms_output.put_line(v_ret);
end;
/

Is there any way/workaround to make it work. I can use assignments but it makes my code long and cumbersome.

Thanks

and Connor said...

They need to be database objects in their own right so they sql engine can see them,

SQL> create or replace
  2  function f_one
  3  return varchar2
  4  AS
  5  begin
  6  return 'one';
  7  end f_one;
  8  /

Function created.

SQL>
SQL>
SQL> create or replace
  2  function f_two
  3  return varchar2
  4  AS
  5  v_two varchar2(5) := f_one;
  6  begin
  7  v_two := f_one;
  8  --select f_one into v_two from dual;
  9  return v_two;
 10  end f_two;
 11  /

Function created.

SQL>
SQL>
SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_ret varchar2(5);
  3  begin
  4  --v_ret := f_two;
  5    select f_two into v_ret from dual;
  6    dbms_output.put_line(v_ret);
  7  end;
  8  /
one

PL/SQL procedure successfully completed.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Khizer, January 18, 2016 - 7:08 am UTC

Thanks for the reply.

I am working on a environment where we cannot create any db objects and hence the use of anonymous blocks.

Is there any way to keep the functions embedded in anonymous blocks and still use in a sql.

Thanks

January 18, 2016 - 9:51 am UTC

You're on 12c, so you can define functions in SQL using the with clause:

with function f_one return varchar2 as 
begin 
  return 'one';
end f_one;
function f_two
  return varchar2
as
  v_two varchar2 ( 5 ) := f_one;
begin
  v_two := f_one;
  return v_two;
end f_two;
  select f_two from dual;

F_TWO                                                                                                                                                          
-----
one

Mark, January 18, 2016 - 9:45 am UTC

If you are running Oracle 12 you can include the functions in the query - so a "SQL anonymous block" rather than a "PL/SQL anonymous block"

e.g.

WITH
  FUNCTION f_function(i_num IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN i_num;
  END;
SELECT f_function(column_a) as value_a,column_b
FROM   t_table
/

Note that you will need to use the block terminator (in this case / ) to terminate the query as the function will contain semi-colons.

January 18, 2016 - 9:52 am UTC

Yep, thanks Mark.

PL/SQL Function in the WITH Clause

Rob van den Berg, January 18, 2016 - 9:56 am UTC

This might be a perfect opportunity to consider using a PL/SQL Function in the WITH Clause. You need database version 12c in that case.

WITH FUNCTION f_one
   RETURN VARCHAR2
   AS
   BEGIN
     RETURN 'one';
   END f_one;
FUNCTION f_two
   RETURN VARCHAR2
   AS
   v_two VARCHAR2(5) := f_one;
   BEGIN
      v_two := f_one;
   RETURN v_two;
   END f_two;
SELECT f_two FROM DUAL
/

F_TWO
---------------------
one

SQL>

January 18, 2016 - 11:55 am UTC

Excellent point.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL Function in the WITH Clause

More to Explore

PL/SQL demos

PL/SQL docs

functions in anonymous block cannot be used in SQL

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL Function in the WITH Clause

More to Explore

PL/SQL demos

PL/SQL docs