Can't use FUNCTION in SQL?

From: Howard Lee Harkness <hlh_nospam_at_excite.com>
Date: Sat, 28 Aug 1999 16:31:03 GMT
Message-ID: <6B6B359078E695AF.3C885A1E97F9B42F.902ABE4FFD61A8C7@lp.airnews.net>

In Owens' book on PL/SQL, he states that a function "evaluates to the value of some datatype which can be substituted in any place where a variable of the same type can be placed."

However, I tried to use a function in an insert statement in place of a variable, and the resulting package would not compile. A simplified example:

CREATE TABLE hlh_psf
(

PSF_ASSY                                 VARCHAR2(20),
PSF_PART                                 VARCHAR2(20),

<snip>

);
CREATE INDEX hlh_psf_part_index on hlh_psf(psf_part); CREATE INDEX hlh_psf_assy_index on hlh_psf(psf_assy); ...
<in package body>

ASSEMBLY_PART_SEPARATOR CONSTANT VARCHAR2(2) := '>>'; ...
PROCEDURE ExtractParts
IS
--
CURSOR part_id_cur
  IS
<snip cursor declaration and other variables>

part            hlh_psf.psf_part%TYPE;
assembly        hlh_psf.psf_assy%TYPE;

--
FUNCTION SplitAssy(token IN VARCHAR2) RETURN hlh_psf.psf_assy%TYPE IS
sep_pos NUMBER;
BEGIN
   sep_pos := INSTR(token, ASSEMBLY_PART_SEPARATOR);    RETURN SUBSTR(token, 1, sep_pos-1);
END SplitAssy;
--
FUNCTION SplitPart(token IN VARCHAR2) RETURN hlh_psf.psf_part%TYPE IS
sep_pos NUMBER;
BEGIN
   sep_pos := INSTR(token, ASSEMBLY_PART_SEPARATOR);    RETURN SUBSTR(token, sep_pos+2);
END SplitPart;
--
BEGIN
  FOR part_id_rec IN part_id_cur
  LOOP
    assembly := SplitAssy(part_id_rec.title);     part := SplitPart(part_id_rec.title);
<snip code>

--

    INSERT INTO hlh_psf
    VALUES
    (
    assembly,
    part,
<snip code to end of procedure>

...
The above works fine, but

    INSERT INTO hlh_psf
    VALUES
    (

     SplitAssy(part_id_rec.title),
     SplitPart(part_id_rec.title),

...

gives a compile error stating that the function may not be used in SQL statement. Is Owens wrong about this, or have I left something important out?

hlh_NOSPAM_at_excite.com is a valid, unmunged address! It is also so full of spam(!) that I don't read it. Received on Sat Aug 28 1999 - 11:31:03 CDT