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;
See Question&Answers more detail:os