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
0
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.
1