CODEWITHSUNDEEP
sqloracle-databaseoracle-apex-5.1
akkka
akkka

Reputation: 13

Testing a function error ORA-00923

I am trying to create an FUNCTION that gets the location of the parameter entered which is id entered when i test the function it doesn't work it giving the error ORA-00923

create or replace FUNCTION GET_LOCATION (l_con_id in NUMBER)
    RETURN VARCHAR2 
  AS 
    LOCATION VARCHAR2(30);
  BEGIN
    SELECT LOCATION 
      INTO LOCATION 
      FROM LDS_CONSULTANT 
      WHERE CONSULTANT_ID = l_con_id;

    RETURN LOCATION;
  END;

Upvotes: 1

Views: 122

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

Here's my suggestion.

First, a simple test case:

SQL> create table lds_consultant
  2  (consultant_id number,
  3   location      varchar2(30));

Table created.

SQL> insert into lds_consultant
  2    select 1, 'New York' from dual union
  3    select 2, 'London'   from dual;

2 rows created.

A function:

  • name parameter so that its name clearly differs from column name. Let it inherit column datatype
  • the same goes for the return value. Preceding its name with, for example, "L_" shows that it is a local variable
  • using the MAX function in SELECT statement is a simple way of avoiding TOO_MANY_ROWS or NO_DATA_FOUND errors. I'd, though, suggest you properly handle those if necessary
SQL> create or replace function get_location
  2    (par_consultant_id in lds_consultant.consultant_id%type)
  3  return lds_consultant.location%type
  4  is
  5    l_location lds_consultant.location%type;
  6  begin
  7    select max(l.location)
  8      into l_location
  9      from lds_consultant l
 10      where l.consultant_id = par_consultant_id;
 11    return l_location;
 12  end;
 13  /

Function created.

Finally, the way you call it: the standard way by selecting ... from dual:

SQL> select get_location(2)  result_1,
  2         get_location(-1) result_2
  3  from dual;

RESULT_1                       RESULT_2
------------------------------ ------------------------------
London

SQL>

[EDIT]

As Apex uses PL/SQL, you'd have to declare a local variable and put the result of that function into it. Something like this:

declare
  l_result lds_consultant.location%type;
begin
  l_result := get_location(:P1_CONSULTANT_ID);
end;

Or, if it were a report, you'd include it into the SELECT statement as

select l.consultant_id, 
       l.consultant_name,
       get_location(l.consultant_id) location
from lds_consultant l
order by l.consultant_name;

By the way, selecting from dual works anywhere in Oracle (Apex included).

Upvotes: 1

Related Questions