Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: ora-01460: unimplemented or unreasonable conversion requested

    Hi All,

    I have this function and am trying to return a 7500 character strnig back... I used varchar2(32767) first and it did not work so I converted to long but it still gives me the same error after the length becomes 1017...does anybody have a clue ??? Appreciate the help ...

    the line

    CommentList := CommentList || CommentRowType

    gives the error as CommentList is not able to take anything more than 1017 characters !!!!

    here is the function

    CREATE OR REPLACE FUNCTION RQST_VIEW_COMMENTS (REQ_ID NUMBER, DELIMITER_VALUE VARCHAR2)

    -- Purpose: This function returns the comments from the RQST_COMMENT.
    --
    -- MODIFICATION HISTORY
    -- Person Date Comments
    -- --------- ----------- ------------------------------------------
    -- AMERIANI 13-JAN-2004 Creation.
    -- AMERIANI 15-JAN-2004 If CommentList has a value > 32767 characters,truncate the value and add ... at the end of the string.
    -- kwilbank 15-jan-2004 Modify by adding created_dtm to select statement in cursor; replace single quotes in comment_txt to double quotes.
    -- AMeriani 18-FEB-2004 Modify query to union RQST_COMMENT and RQST_ROLE_AUDIT_LOG.
    -- AMERIANI 20-FEB-2004 MODIFY QUERY.

    RETURN LONG
    IS

    CommentList LONG ;
    CommentRowType LONG;
    CommentLength number;
    i number;

    CURSOR CommentView_Cursor IS
    SELECT DISTINCT ('Created by ' ||CREATED_BY_USER_ID||' on '||TO_CHAR(CREATED_DTM, 'MM/DD/YYYY HH:MIS AM')||DELIMITER_VALUE
    ||DECODE(COMMENT_TYPE_CD, 'REQUEST ACTIVITY','Activity update:'||DELIMITER_VALUE||REPLACE(COMMENT_TXT,''' ','"'),
    COMMENT_TYPE_CD||': '||COMMENT_DESC||DELIMITER_VALUE||REPLACE(COMMENT_ TXT,'''','"'))||DELIMITER_VALUE) ALL_COMMENTS,
    CREATED_DTM
    FROM RQST_COMMENT WHERE REQUEST_ID = REQ_ID AND COMMENT_TYPE_CD != 'REQUEST DESCRIPTION'
    UNION ALL
    SELECT DISTINCT (DECODE(GROUP_NAME, 'ASSIGNEE', ('Change - Assigned to '||LOGIN_ID||DELIMITER_VALUE||COMMENT_TXT),
    (COMMENT_TXT))) ALL_COMMENTS, AUDIT_DTM as CREATED_DTM
    FROM RQST_ROLE_AUDIT_LOG WHERE REQUEST_ID = REQ_ID
    ORDER BY CREATED_DTM ASC;

    commentsummary CommentView_Cursor%rowtype;

    BEGIN


    i := 0;
    CommentLength := 0;

    OPEN CommentView_Cursor;
    LOOP
    i := i + 1;
    FETCH CommentView_Cursor INTO commentsummary;
    EXIT WHEN CommentView_Cursor%NOTFOUND;
    CommentRowType := commentsummary.ALL_COMMENTS;
    CommentLength := length(CommentRowType) + CommentLength;
    CommentList := CommentList || CommentRowType;

    END LOOP;

    CLOSE CommentView_Cursor;

    If Length(CommentList) > 32767 Then
    SELECT SUBSTR(CommentList,1,32764)||'...'
    INTO CommentList
    FROM DUAL;
    Else

    SELECT SUBSTR(CommentList,1,LENGTH(CommentList) - LENGTH(DELIMITER_VALUE))
    INTO CommentList
    FROM DUAL;
    End If;

    return(CommentList);

    EXCEPTION

    WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error (-20005,'RQST_VIEW_COMMENTS: '|| sqlerrm);

    END;
    /
    Last edited by lamyak; 04-16-04 at 13:19.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My crystal ball in in the shop for repair.
    Do you expect us to guess which of the lines is actually getting the error?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Re: ora-01460: unimplemented or unreasonable conversion requested

    the line

    CommentList := CommentList || CommentRowType

    gives the error as CommentList is not able to take anything more than 1017 characters !!!!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The Concatenation Operator ('||') only withs with strings; not LONGs
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    Hi,

    No it works with long ....it takes upto 1000 characters and then suddenly it becomes null........

Posting Permissions

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