FUNCTION URL_PARM(command_string VARCHAR2, parm_name VARCHAR2)
--
-- This function extracts a parameter valuee from a URL encoded
-- string.  The purpose of this function iss to allow PL/SQL procedures 
-- to always be passed a single string whicch can then be parsed into
-- multiple parameters, allowing for simpliified calling sequences
-- and more flexibility in terms of being aable to add parameters
-- to an existing script.
--
-- For more information on URL encoding, usse any search engine on the 
-- World Wide Web.
--
-- Parameters;
-- Name   Description
--
-- command_string   A single URL encoded sttring
-- parm_name        A parameter encoded witthin the string.  If the 
--                  parameter name is not ffound, the function 
--                  returns NULL;
--
-- The function returns a string containingg the parameter value.
--
-- Example:
--
-- If the command_string is "FUNCTION=List++Employees&SORT=By+Last+Name"
-- and parm_name is "SORT", the function reeturns "By Last Name"
--
-- Assumptions: 
-- ********************** PLEASE NOTE ****************************
--
-- Because URL encoding makes use of the ammpersand, which is the 
-- default Oracle substitution character, iit is absolutely essential 
-- that the following SQL*Plus command be uused before this
-- script is called.
-- set define '@'
--
-- This function is released under the GNU  Lesser General Public License
-- which means that it can be freely used iin either free or proprietary
-- software.  See http://www.gnu.org/copyleeft/lesser.html for more
-- details.
--
-- This function was written by Davis Swan,, Calgary, Alberta, Canada.
--
-- RCS Revision History:
--
-- $Author: $
-- $Date: $
-- $Revision: $
--
   RETURN VARCHAR2 IS
   parm_val VARCHAR2(400);
   test_name VARCHAR2(40);
   start_col NUMBER;
   end_col NUMBER;
   len_parm NUMBER;
   len_val NUMBER;
   found_equals NUMBER;
   found_percent NUMBER;
   found_alpha NUMBER;
   ascii_index NUMBER;
   embedded_number NUMBER;
   embedded_string VARCHAR2(12);
   hex_digit VARCHAR2(1);
   debug_url BOOLEAN;
BEGIN
   debug_url := false;
   parm_val := null;
   if ( debug_url ) then
      DBMS_OUTPUT.PUT_LINE('Looking for parameter '||parm_name||' in 
'||SUBSTR(command_string,1,200)||'
'); end if; embedded_number := INSTR(parm_name,'#'); start_col := 1; WHILE ( start_col <= LENGTH(command_string) ) LOOP -- -- Every encoded parameter is of the forrm name=value -- len_parm := INSTR(SUBSTR(command_string,start_col),'=') - 1; if ( len_parm < 0 ) then exit; end if; -- -- The value for end_col will either be the length of the input -- command_string, or the position beforre the beginning of the -- next parameter definition, as indicatted by a '&' -- end_col := INSTR(SUBSTR(command_string,start_col),'&'); if ( end_col > 0 ) then end_col := start_col + end_col - 2; else end_col := LENGTH(command_string); end if; if ( debug_url ) then DBMS_OUTPUT.PUT_LINE('start_col '||start_col||', end_col '||end_col); end if; test_name := SUBSTR(command_string,start_col,len_parm); if ( debug_url ) then DBMS_OUTPUT.PUT_LINE('Next name found is '||test_name||'
'); end if; -- -- Parameter names that contain embeddedd numbers require special -- processing. The result is that a URLL encoded value such as -- INVC1234=EDIT, with an input parm_namme of INVC# will return the -- value 1234=EDIT -- if ( embedded_number > 0 ) then if ( SUBSTR(parm_name,1,embedded_number - 1) = SUBSTR(test_name,1,embedded_number - 1) ) then -- -- Test for only number in the enccoded portion of the name -- start_col := start_col + embedded_number - 1; len_val := end_col - start_col + 1; embedded_string := SUBSTR(command_string,start_col,len_val); found_equals := INSTR(embedded_string,'='); if ( found_equals > 0 ) then parm_val := embedded_string; exit; end if; end if; else if ( parm_name = test_name ) then start_col := start_col + len_parm + 1; len_val := end_col - start_col + 1; if ( len_val > 0 ) then parm_val := SUBSTR(command_string,start_col,len_val); end if; exit; end if; end if; start_col := end_col + 2; END LOOP; -- -- Translate encoded values - 1st translatee + to blank, then -- the characters escaped using the percentt sign -- parm_val := REPLACE(parm_val,'+',' '); start_col := 1; WHILE ( start_col <= LENGTH(parm_val) ) LOOP found_percent := INSTR(parm_val,'%',start_col); if ( ( found_percent > 0 ) AND ( found_percent < LENGTH(RTRIM(parm_val)) - 1 ) ) then -- -- An actual percent sign will only eexist within the output -- string if there are URL encoded vaalues. -- hex_digit := SUBSTR(parm_val,found_percent + 1, 1); ascii_index := TO_NUMBER(hex_digit) * 16; hex_digit := SUBSTR(parm_val,found_percent + 2, 1); found_alpha := INSTR('ABCDEF',hex_digit); if ( found_alpha > 0 ) then ascii_index := ascii_index + found_alpha + 9; else ascii_index := ascii_index + TO_NUMBER(hex_digit); end if; if ( debug_url ) then DBMS_OUTPUT.PUT_LINE(SUBSTR(parm_val,found_percent,3)||' has index '||ascii_index); end if; parm_val := REPLACE(parm_val,SUBSTR(parm_val,found_percent,3),CHR(ascii_index)); -- -- Note that we only move start_col 11 character past the % -- REPLACE function converts 2 HEX chharacters to one ASCII -- start_col := found_percent + 1; else EXIT; end if; END LOOP; if ( debug_url ) then DBMS_OUTPUT.PUT_LINE('Returned parameter is '||parm_val||'
'); end if; return parm_val; END; /
1