 |
|
The PL/SQL Function Body
Oracle Tips by
Burleson
|
The PL/SQL statements that follow the BEGIN statement and
precede the EXCEPTION and/or END statement make up the
body of a function. The highlighted portion of Listing 5.18 is the
function’s body.
Listing 5.18 The body of a function.
FUNCTION Parse_String (vStringToParse IN varchar2)
RETURN Global_Types.VARCHAR2_TABTYPE
IS
iStringPos integer;
biIndex binary_integer := 0;
DELIMITER CONSTANT char (1) := '^';
vString varchar2 (2000);
Return_tab VARCHAR2_TABTYPE;
FUNCTION DelimiterPosition (vString IN varchar2)
RETURN integer;
FUNCTION NextWord (vCheckString IN varchar2)
RETURN varchar2
IS
BEGIN
iStringPos := DelimiterPosition (vString => vCheckString);
IF (iStringPos > 0) THEN
RETURN (substr (vCheckString, 1, iStringPos));
END IF;
RETURN NULL;
END NextWord;
FUNCTION DelimiterPosition (vString IN varchar2)
RETURN integer
IS
BEGIN
RETURN (instr (vString, DELIMITER));
END DelimiterPosition;
BEGIN
vString := vStringToParse;
LOOP
Return_tab (biIndex) := NextWord;
—
— If there are no more elements in the string, return
— the table.
—
IF (iStringPos = 0) THEN
RETURN Return_tab;
END IF;
Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));
biIndex := biIndex + 1;
—
— Chop off the first portion of the string.
—
vString := substr (vString, (iStringPos + 1));
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN
Log_System_Error (vErrorLocation => 'Parse_String',
vErrorText => SQLERRM);
END;
Exception Handling
Exception handlers are defined within the function to handle error
conditions that could reasonably be expected to occur while the
function is executing. In Listing 5.19, the developer feels that the
VALUE_ERROR exception might be encountered while processing the
string if a delimited portion of the string exceeds the defined length
of the PL/SQL table row.
Listing 5.19 The exception handling portion of a function.
FUNCTION Parse_String (vStringToParse IN varchar2)
RETURN Global_Types.VARCHAR2_TABTYPE
IS
iStringPos integer;
biIndex binary_integer := 0;
DELIMITER CONSTANT char (1) := '^';
vString varchar2 (2000);
Return_tab VARCHAR2_TABTYPE;
FUNCTION DelimiterPosition (vString IN varchar2)
RETURN integer;
FUNCTION NextWord (vCheckString IN varchar2)
RETURN varchar2
IS
BEGIN
iStringPos := DelimiterPosition (vString => vCheckString);
IF (iStringPos > 0) THEN
RETURN (substr (vCheckString, 1, iStringPos));
END IF;
RETURN NULL;
END NextWord;
FUNCTION DelimiterPosition (vString IN varchar2)
RETURN integer
IS
BEGIN
RETURN (instr (vString, DELIMITER));
END DelimiterPosition;
BEGIN
vString := vStringToParse;
LOOP
Return_tab (biIndex) := NextWord;
--
-- If there are no more elements in the string, return
-- the table.
--
IF (iStringPos = 0) THEN
RETURN Return_tab;
END IF;
Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));
biIndex := biIndex + 1;
--
-- Chop off the first portion of the string.
--
vString := substr (vString, (iStringPos + 1));
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN
Log_System_Error (vErrorLocation => 'Parse_String',
vErrorText => SQLERRM);
END;
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
