Hi all,
I have created a function which is parsed a "Bed Space Code" i.e. ARA-09A.
This bed_space_code is a primary key in a view, which has attributed about the each bed_Space like its campus, building, floor e.t.c.
What I am attempting is to create a function, which can have optional flags.
For example:
If just a bed_space is parsed ( func('ARA-09A') ) then the function will return: Aragon Court 09B
If the letter C is parsed as a second parameter then the Campus is returned also ( func('ARA-09A', 'C') ): Avery Hill Aragon Court 09B
There are a few more such as a floor flag which will return the floor as well
I wanted to try and make this as efficient and dynamic as possible. At the moment I have create a package with an Overloaded function.
Issues:
The second function is ran when other parameter is parsed :/ ?? but this works. I was really looking to see if anyone can give me some tips as to how this can be done better and more dynamically, or if the was I have done it is correct.
CREATE OR REPLACE PACKAGE BedSpaceDetails_MEM IS
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2) RETURN VARCHAR2;
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2, in_flag CHAR) RETURN VARCHAR2;
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2, in_flag CHAR, in_flag2 CHAR) RETURN VARCHAR2;
END BedSpaceDetails_MEM;
/
CREATE OR REPLACE PACKAGE BODY BedSpaceDetails_MEM AS
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2) RETURN VARCHAR2
IS var_result VARCHAR2(50);
BEGIN
select buildings_name||' '||substr(replace(substr(pk_bed_space,instr(pk_bed_space, '-'),10),'_',' '),2,10) into var_result
from RMGT_V_BED_SPACE_TO_COMMUNITY
where pk_bed_space = IN_PK_BED_SPACE;
RETURN var_result;
EXCEPTION
WHEN others
THEN dbms_output.put_line('Others'||' '||SQLCODE||' '||SQLERRM);
END get_roomDetails;
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2, in_flag CHAR) RETURN VARCHAR2
-- Function to Get Campus aswell
IS var_result VARCHAR2(100);
BEGIN
select fk_community||' '||buildings_name||' '||substr(replace(substr(pk_bed_space,instr(pk_bed_space, '-'),10),'_',' '),2,10) into var_result
from RMGT_V_BED_SPACE_TO_COMMUNITY
where pk_bed_space = IN_PK_BED_SPACE;
RETURN var_result;
EXCEPTION
WHEN others
THEN dbms_output.put_line('Others'||' '||SQLCODE||' '||SQLERRM);
END get_roomDetails;
FUNCTION get_roomDetails (in_pk_bed_space IN VARCHAR2, in_flag CHAR, in_flag2 CHAR) RETURN VARCHAR2
-- Function to Get Campus aswell
IS var_result VARCHAR2(100);
BEGIN
select ROOM_TYPE_DESCRIPTION||' '||buildings_name||' '||substr(replace(substr(pk_bed_space,instr(pk_bed_space, '-'),10),'_',' '),2,10) into var_result
from RMGT_V_BED_SPACE_TO_COMMUNITY
where pk_bed_space = IN_PK_BED_SPACE;
RETURN var_result;
EXCEPTION
WHEN others
THEN dbms_output.put_line('Others'||' '||SQLCODE||' '||SQLERRM);
END get_roomDetails;
END BedSpaceDetails_MEM;