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.

executed successfully: I doubt that this function compiled. You are missing semicolons, select cannot be called like that and your if-statements are incomplete. — Peter Lang
– Peter Lang, Commented Mar 8, 2016 at 12:07

Aleksej · Accepted Answer · 2016-03-08 12:09:53Z

17

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

edited Mar 8, 2016 at 12:09

answered Mar 8, 2016 at 9:30

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bob Jarvis - Слава Україні Over a year ago

There's no reason why a function can't have one or more OUTPUT parameters, but doing so will make that function impossible to call from SQL.

Aleksej Over a year ago

You're absolutely right; I was thinking to SQL, but I wrote something else; I'll fix the answer, thanks

suri Over a year ago

thanks for your reply. finally i found the answer that how to call a sql function from pl sql developer. select * from table(name of the function(in and out parameters)). this will work.

Collectives™ on Stack Overflow

how to run a sql function in pl sql developer

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related