Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: PLS-00382: expression is of wrong type

    Hi,

    I am trying to insert data in a table using cursor record and am getting the error

    PLS-00382: expression is of wrong type

    Please find the part of the code below.

    Cursor definition -

    CURSOR cur_err_messg (p_request_id IN NUMBER) IS
    SELECT air.parent_table error_code,
    air.reject_lookup_code error_messg,
    aii.org_id org_id,
    air.creation_date creation_date,
    air.created_by created_by ,
    air.last_update_date last_update_date ,
    air.last_updated_by last_updated_by
    FROM
    ap_interface_rejections air,
    ap_invoices_interface aii
    WHERE
    air.parent_table = 'AP_INVOICES_INTERFACE' AND
    air.parent_id = aii.invoice_id AND
    aii.request_id = p_request_id
    UNION ALL
    SELECT air.parent_table error_code,
    air.reject_lookup_code error_messg,
    aii.org_id org_id,
    air.creation_date creation_date,
    air.created_by created_by ,
    air.last_update_date last_update_date ,
    air.last_updated_by last_updated_by
    FROM
    ap_interface_rejections air,
    ap_invoices_interface aii,
    ap_invoice_lines_interface aili
    WHERE
    air.parent_table = 'AP_INVOICE_LINES_INTERFACE' AND
    air.parent_id = aili.invoice_line_id AND
    aili.invoice_id = aii.invoice_id AND
    aii.request_id = p_request_id ;

    Code where getting the error :-

    FOR rec_error IN cur_err_messg(p_request_id)
    LOOP


    INSERT INTO GEPS_FND_ALLINTFERROR(
    report_seq_id,
    request_id,
    request_set_id,
    interface_name,
    request_date,
    org_id,
    organization_id,
    error_code,
    error_message,
    error_type,
    distribution_list,
    error_language,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
    )
    VALUES (
    GEPS_FND_COMNERROR_S1.nextval,
    p_request_id,
    p_request_set_id,
    p_interface_name,
    p_request_date,
    rec_error.org_id,
    rec_error.error_code,
    rec_error.error_messg,
    p_error_type,
    p_email_dist,
    v_language,
    rec_error.creation_date,
    rec_error.created_by,
    rec_error.last_update_date,
    rec_error.last_updated_by
    ) ;
    END LOOP;


    I am getting the error at the point "rec_error.created_by".
    I have checked the data types for created_by for the two tables -

    For ap_interface_rejections, it is NUMBER(15) ;
    For GEPS_FND_ALLINTFERROR, it is NUMBER;

    Any help would be most welcome.

    Thanks,
    Mayuri.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >For ap_interface_rejections, it is NUMBER(15) ;
    >For GEPS_FND_ALLINTFERROR, it is NUMBER;
    Necessary, but not sufficient.
    What is GEPS_FND_ALLINTFERROR.CREATED_BY type?
    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
    Dec 2004
    Posts
    3
    Hi anacedent,

    I didn't understand what you mean by type here..

    I had given the datatypes as -

    ap_interface_rejections.created_by -> NUMBER(15) ;
    GEPS_FND_ALLINTFERROR.CREATED_BY -> NUMBER ;

    May be the table script would help :-

    CREATE TABLE GEPS_FND_ALLINTFERROR (
    REPORT_SEQ_ID NUMBER NOT NULL,
    REQUEST_ID NUMBER NOT NULL,
    REQUEST_SET_ID NUMBER NOT NULL,
    INTERFACE_NAME VARCHAR2 (100),
    REQUEST_DATE DATE,
    ERROR_TYPE VARCHAR2 (1) NOT NULL,
    ERROR_CODE VARCHAR2 (50) NOT NULL,
    ERROR_MESSAGE VARCHAR2 (4000) NOT NULL,
    ERROR_LANGUAGE VARCHAR2 (4),
    ORG_ID NUMBER,
    ORGANIZATION_ID NUMBER,
    DISTRIBUTION_LIST VARCHAR2 (240),
    CREATION_DATE DATE NOT NULL,
    CREATED_BY NUMBER NOT NULL,
    LAST_UPDATE_DATE DATE NOT NULL,
    LAST_UPDATED_BY NUMBER NOT NULL)

    Another thing that I noticed was that if I do the following -

    INSERT INTO GEPS_FND_ALLINTFERROR(
    report_seq_id,
    request_id,
    request_set_id,
    interface_name,
    request_date,
    org_id,
    organization_id,
    error_code,
    error_message,
    error_type,
    distribution_list,
    error_language,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by
    )
    VALUES (
    GEPS_FND_COMNERROR_S1.nextval,
    p_request_id,
    p_request_set_id,
    p_interface_name,
    p_request_date,
    rec_error.org_id,
    rec_error.error_code,
    rec_error.error_messg,
    p_error_type,
    p_email_dist,
    v_language,
    rec_error.creation_date,
    to_char(rec_error.created_by),
    rec_error.last_update_date,
    rec_error.last_updated_by
    ) ;

    In this case, I get the error on the next line which is "rec_error.last_update_date" where as again the datatypes for last_update_date in both the tables is date.

    Please let me know if you can detect the reason for the error here.

    Thanks,
    Mayuri.

Posting Permissions

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