Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Baffling carriage returns.

    I have created a function in Oracle9i that basically accepts a VARCHAR2
    value and returns a VARCHAR2 value. For some reason, when I include this
    function in an SQL statement in SQL Plus Worksheet, the Oracle insists
    upon adding carriage returns immediately prior to and immediately
    following the function results. Also, the column title in the resultset
    for this function is not underlined as the other titles are. The examples
    in my text books do not illustrate this behavior.

    Why is this happening, and will it hinder my ability to use this function
    in INSERT statements?

    The function code and some sample results are below.

    -----------------------------------------------------------------
    Function definition
    -----------------------------------------------------------------
    CREATE OR REPLACE FUNCTION "ISGCOSDEV"."PARSEPROJECTNUMBER"
    (PROJECTSTRING VARCHAR2)
    RETURN VARCHAR2 IS
    --This function returns the first alphanumeric substring that begins with
    eight or more numeric characters.
    PROJECTNUMBER VARCHAR2(20);

    BEGIN
    PROJECTNUMBER := '';
    IF (LENGTH(PROJECTSTRING) < 8) OR (PROJECTSTRING IS NULL) THEN
    RETURN NULL;
    END IF;

    FOR CHARNUM IN 1..LENGTH(PROJECTSTRING) LOOP
    IF SUBSTR(PROJECTSTRING, CHARNUM, 1) IN ('0', '1', '2', '3', '4', '5',
    '6', '7', '8', '9') THEN
    PROJECTNUMBER := PROJECTNUMBER || SUBSTR(PROJECTSTRING, CHARNUM, 1);
    ELSE
    IF LENGTH(PROJECTNUMBER) >= 8 THEN
    IF SUBSTR(PROJECTSTRING, CHARNUM, 1) = ' ' THEN
    RETURN PROJECTNUMBER;
    ELSE
    PROJECTNUMBER := PROJECTNUMBER || SUBSTR(PROJECTSTRING, CHARNUM,
    1);
    END IF;
    ELSE
    PROJECTNUMBER := '';
    END IF;
    END IF;
    END LOOP;

    IF LENGTH(PROJECTNUMBER) >= 8 THEN
    RETURN PROJECTNUMBER;
    ELSE
    RETURN NULL;
    END IF;

    END;

    -----------------------------------------------------------------
    Sample SQL statement
    -----------------------------------------------------------------
    SELECT
    'VALUE1' AS COL1,
    'VALUE2' AS COL2,
    PARSEPROJECTNUMBER(FUNCTIONALGROUP) AS COL3,
    'VALUE4' AS COL4,
    CAST(FUNCTIONALGROUP AS VARCHAR(20)) AS COL5
    FROM STG_CPRO_STATUS_REPORT
    WHERE ROWNUM <= 5;

    -----------------------------------------------------------------
    Sample Output
    -----------------------------------------------------------------
    COL1 COL2
    ------ ------
    COL3

    COL4 COL5
    ------ --------------------
    VALUE1 VALUE2
    04005177
    VALUE4 04005177 - Cash Serv

    VALUE1 VALUE2
    04005177
    VALUE4 04005177 - Cash Serv

    VALUE1 VALUE2
    04008621
    VALUE4 04008621 - Research

    VALUE1 VALUE2
    04008661
    VALUE4 04008661 - Image Pro

    VALUE1 VALUE2
    04008664
    VALUE4 04008664 - Day 1 Tex


    5 rows selected.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't think your function has a problem, I think it is a "feature" of SQL Plus Worksheet. Basically, it doesn't have a clue how long the return value from your function may be, so it allows A LOT.

    I don't use Worksheet, but I can get a similar result in SQL Plus:
    Code:
    SQL> create or replace function f return varchar2 is
      2  begin
      3    return 'aaa';
      4  end;
      5  /
    
    Function created.
    
    SQL> select ename, f, job from emp;
    
    ENAME
    ----------
    F
    --------------------------------------------------------------------------------
    JOB
    ---------
    SMITH
    aaa
    CLERK
    
    ALLEN
    aaa
    SALESMAN
    
    WARD
    aaa
    SALESMAN
    
    JONES
    aaa
    MANAGER
    
    MARTIN
    aaa
    SALESMAN
    
    BLAKE
    aaa
    MANAGER
    ...
    What I can then do is tell SQL Plus how much space to allow for the function return value:
    Code:
    SQL> column f format a10
    tandrews@IDEV
    SQL> /
    
    ENAME      F          JOB
    ---------- ---------- ---------
    SMITH      aaa        CLERK
    ALLEN      aaa        SALESMAN
    WARD       aaa        SALESMAN
    JONES      aaa        MANAGER
    MARTIN     aaa        SALESMAN
    BLAKE      aaa        MANAGER
    CLARK      aaa        MANAGER
    SCOTT      aaa        ANALYST
    TURNER     aaa        SALESMAN
    ADAMS      aaa        CLERK
    JAMES      aaa        CLERK
    FORD       aaa        ANALYST
    MILLER     aaa        CLERK
    
    13 rows selected.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks Tony. I'll try that. I was able to create my INSERT proc today, and the odd-looking result set didn't seem to affect it.

    I was surprised to find out that I can't specify the size of the return value for a function. When I specifed RETURN VARCHAR2(20), I got errors, but RETURN VARCHAR2 worked fine. It seemed like an odd restriction on functionality to me, but I guess I can live with it.

    blindman
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The reason that you can't specify the size of a return is because the size of the variable in the calling program is the controlling factor. for examble in the following code snippet the size to return is 23. However if the function returned a string of 24 charachers an error would be generated that would indicate the returned value was too big to be stored in the variable.

    declare
    my_string varchar2(23);
    begin
    my_string := my_function;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •