oracle pl/sql function name resolution error

Question

I am trying to perform a fairly simple function. I want to pass in an hostname to this function, i want it to take that hostname and use it to find the systemID associated with that hostname and return it.

below you will see that i have a pl/sql block that is calling a function called GET_SYSTEMID when i pass in a varchar to this function i get an error back. when i hard code the same string inside the function i get the correct results back. The column holding the hostnames has a unique constraint so if i am using the exact hostname of one of my servers (i am for sure) there should only be one matching row.

This is my calling block:

Declare
    sysid number;
Begin
    sysid := Server.GET_SYSTEMID('MyHost');
    DBMS_OUTPUT.PUT_LINE('SYSID is '||sysid);    
END;

If i use that block to call this function it does not work:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS
    SysID number;
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = Hostname;

    return SysID;
END GET_SYSTEMID;

When i run the above i get this error message:

Declare
    sysid number;
Begin
    sysid := Server.GET_SYSTEMID('MyHost');
    DBMS_OUTPUT.PUT_LINE('SYSID is '||sysid);    
END;
Error at line 3
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MySchema.Server", line 33
ORA-06512: at line 12

These next two do work but as they hard code the hostname they dont do what i need this function to do:

Number one:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS 
    SysID number;
    tmp varchar2(8) := 'MyHost';--should be identical to passed in value
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = tmp;

    return SysID;
END GET_SYSTEMID;

Number Two:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS
    SysID number;
BEGIN 

    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = 'MyHost';--should be identical to passed in value

    return SysID;
END GET_SYSTEMID;

Answer 1

The problem is one of name resolution.

When you have a parameter hostname and a hostname column in the table that you're referencing, the scope resolution rules cause most people confusion. That's why many people recommend using a naming convention for parameters and local variables that differentiates them from table names. In my code, for example, I use p_ to prefix parameter names and l_ to prefix local variables.

In your code, when you have

SELECT mySystems.SYSTEMID
INTO SysID
FROM mySystems
where mySystems.HOSTNAME = Hostname;

hostname is resolved as the column in the table, not the parameter. This causes the query to return every row in the table where hostname is not null which causes the error. You can explicitly prefix the parameter name with the function name to force hostname to resolve to the parameter

SELECT mySystems.SYSTEMID
INTO SysID
FROM mySystems
where mySystems.HOSTNAME = GET_SYSTEMID.Hostname;

That works. But adding the function name prefix generally gets annoying. If you adopt the convention of prefixing parameter names and local variable names, you'd get something like

FUNCTION GET_SYSTEMID(p_hostname varchar2)
RETURN NUMBER
IS
    l_sysID number;
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO l_sysID
    FROM mySystems
    where mySystems.HOSTNAME = p_hostname;

    return l_sysID;
END GET_SYSTEMID;

That also works and tends (in my mind) to be clearer than adding explicit function name prefixes all over the place.