PL/SQL function not accepting the parameter

Question

I am new to PL/SQL. My requirement is to create a function which returns a single value. It should not be difficult as it's a simple code. However, I am getting an error "identifier 'IN_VC_LOW_VALUE' must be declared". Please let me know where I am going wrong:

Below is my code:

FUNCTION F_GET_DOMAIN_MEANING (in_vc_domain    VARCHAR2,
                               in_vc_low_value VARCHAR2,
                               in_vc_option    VARCHAR2) 
                               RETURN VARCHAR2
as
   vc_result  blu_custom_refs.rv_meaning_l1%TYPE := NULL;

   CURSOR c_domain_meaning IS
      SELECT rv_meaning_l1
      FROM   blu_custom_refs
      WHERE  rv_domain = UPPER(in_vc_domain)
      AND    rv_low_value = UPPER(in_vc_low_value);

   CURSOR c_domain_abbrev IS
      SELECT rv_abbreviation_l1
      FROM   blu_custom_refs
      WHERE  rv_domain = UPPER(in_vc_domain)
      AND    rv_low_value = UPPER(in_vc_low_value);
BEGIN
   IF (in_vc_low_value IS NOT NULL) AND (in_vc_domain IS NOT NULL) THEN

      IF (in_vc_option = 'MEANING') THEN
         OPEN c_domain_meaning;
         FETCH c_domain_meaning INTO vc_result;
         IF (c_domain_meaning%NOTFOUND) THEN
            vc_result := NULL; 
         END IF;
         CLOSE c_domain_meaning;

      ELSIF (in_vc_option = 'ABBREVIATION') THEN
         OPEN c_domain_abbrev;
         FETCH c_domain_abbrev INTO vc_result;
         IF (c_domain_abbrev%NOTFOUND) THEN
            vc_result := NULL;
         END IF;
         CLOSE c_domain_abbrev;
      END IF; 
   END IF;

   RETURN (LTRIM(RTRIM(vc_result)));
END;

Screenshot of error :

It's not accepting the parameter. Thanks in advance

Answer 1

You attempted to execute a SQL statement beginning with FUNCTION. There is no such SQL statement in Oracle. So each group of text delimited by ; was sent to the database separately, most of which are invalid statements, and one of them threw the error about not recognizing the parameter name. That's because you weren't compiling the entire unit together.

What you need to do to compile your function is to use the CREATE SQL statement, which has a useful derivative CREATE OR REPLACE, like this:

CREATE OR REPLACE FUNCTION F_GET_DOMAIN_MEANING (in_vc_domain    VARCHAR2,
                               in_vc_low_value VARCHAR2,
                               in_vc_option    VARCHAR2) 
                               RETURN VARCHAR2
AS    . . .

etc.

That will compile the function and create the function object in the dictionary. Once that is done, you can execute the function by calling it from another PL/SQL program or anonymous block:

DECLARE
  vc_result  blu_custom_refs.rv_meaning_l1%TYPE := NULL;
BEGIN
  vc_result := f_get_domain_meaning(in_vc_domain => 'whatever',
                                    in_vc_low_value => 'whatever',
                                    in_vc_option => 'whatever');

  dbms_output.put_line('function returned value '||vc_result);
END;

By the way, you may have gotten the syntax you tried to use from examples on the internet, where you were seeing functions embedded in packages. In that context, the package is compiled with the CREATE statement but its individual component functions will simply start with FUNCTION. But if you want a standalone function that is not packaged, you'll need to use CREATE with it.