how to run a sql function in pl sql developer

Question

I am new to oracle. I created one function in oracle database using pl sql developer, and I executed successfully. Now I want to run that function from same pl sql developer by query. but it is not working properly. Below is my function.

create or replace function com.my_first_test_function(module_code out varchar2,                         
bpm_process_name out varchar2,module_name out varchar2,input in number)return number is
y_return number(1)
N_return varchar(200)
begin
if input='yes' then
Y_return :=select module_code ,bpm_process_name,module_name from com_tm_bpm_process_details;

if input='no' then nested_procedure_exception exception

but when I try to run this function using below query it is throwing some error message. can any one tell me how to call the function from pl sql developer.

select * from  com.my_first_test_function(java.sql.Types.VARCHAR,java.sql.Types.VARCHAR,java.sql.Types.VARCHAR,'yes')

here I tried without out parameter values also but still no use.

Answer 1

To call a function in a SQL statement is intended to compute some return value, reading informations from parameters; so, functions with OUT parameters make not sense in SQL. If you need something that could handle OUT parameters, you can use procedures, within a PL/SQL block, passing variables as OUT parameters.

About calling a FUNCTION, you can use a PL/SQL block, with variables:

SQL> create or replace function f( n IN number) return number is
  2  begin
  3      return n * 2;
  4  end;
  5  /

Function created.

SQL> declare
  2      outNumber number;
  3  begin
  4      select f(10)
  5      into outNumber
  6      from dual;
  7      --
  8      dbms_output.put_line('outNumber: "' || outNumber || '"');
  9  end;
 10  /
outNumber: "20"

PL/SQL procedure successfully completed.

or even call it directly in a SQL query:

SQL> select f(10) from dual;

     F(10)
----------
        20