CODEWITHSUNDEEP
oracle-databaseplsql
user3946444
user3946444

Reputation:

calling a function from PL/SQL in a select statement in ORACLE

Here is my PL/SQL function portion. I am trying to use the function in a select statement. for example we can write a query like select count(column_name) from table_name. Here count is a function. I want to use my own functions like this. I've tried differently (using the function outside the PL/SQL function, inside the PL/SQL function). But it throws an error PLS-00231:function 'GET_ANNUAL_COMP' may not be used in SQL when used inside PL/SQL function and throws ORA-00904 invalid identifier when when used outside the PL/SQL function.

I'm using oracle 11g.

declare
em_sal number(20);
em_comm employees.commission_pct%type; 
annual_salary number(10,4);

function get_annual_comp(sal in number, comm in number)
return number is
begin
return (sal*12 + comm*12);
end;
begin
select salary into em_sal from employees where employee_id=149;
select commission_pct into em_comm from employees where employee_id=149;

annual_salary := get_annual_comp(em_sal,em_comm);

dbms_output.put_line('total salary '|| annual_salary);

select get_annual_comp(salary,commission_pct) from employees where department_id=90;
end;
/

Upvotes: 10

Views: 78092

Answers (2)

balaaagi
balaaagi

Reputation: 512

Create the function globally. Also ensure that the function is created in same schema also the logged in user has necessary privileges in the schema.

Upvotes: 1

Patrick Bacon
Patrick Bacon

Reputation: 4630

Compile the function in an appropriate schema (sames schema that will be running anonymous block) as follows:

CREATE OR REPLACE FUNCTION GET_ANNUAL_COMP(
    sal  IN NUMBER,
    comm IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  RETURN (sal*12 + comm*12);
END;

With same schema as the function, run the anonymous block: 

DECLARE
  em_sal NUMBER(20);
  em_comm employees.commission_pct%type;
  annual_salary NUMBER(10,4);
BEGIN
  SELECT salary INTO em_sal FROM employees WHERE employee_id=149;
  SELECT commission_pct INTO em_comm FROM employees WHERE employee_id=149;
  annual_salary := get_annual_comp(em_sal,em_comm);
  dbms_output.put_line('total salary '|| annual_salary);
  SELECT SUM(get_annual_comp(salary,commission_pct)) into annual_salary
  FROM employees
  WHERE department_id=90;
  dbms_output.put_line('department annual salary '|| annual_salary);
END;
/

Upvotes: 10

Related Questions