Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter