RE: ORA-6502: numeric or value error. 8.1.5, NT

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 04 Sep 2001 11:24:21 -0700
Message-ID: <F001.00382378.20010904113110@fatcity.com>

Chris,

Your procedure passes the value of the study.title column back to a calling procedure. Since I don't see the INTO clause from the select, I am assuming that you are selecting the TITLE column into the study_title_out variable.

What size is the variable that you are returning the value back to?

In other words, you have a calling procedure that:

declare
study_title_out varchar2(???); <====
begin

procedure get_study_info (1,study_title_out,

                              etc., etc., etc.);

end;

Is the above declaration of the local variable large enough to handle the result?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, September 04, 2001 2:56 PM To: Multiple recipients of list ORACLE-L

We have this procedure...

procedure get_study_info (study_id_in IN study.study_id%TYPE,

                              study_title_out OUT study.title%TYPE,
<<<<<<<<<<<<<<<<<<<<
                              brief_title_out OUT study.brief_title%TYPE,
                              study_number_out OUT
study.client_study_no%TYPE,
                              protocol_number_out OUT
study.sponsor_protocol_no%TYPE,
                              sponsor_out OUT company.company_name%TYPE,
                              cro_out OUT company.company_name%TYPE,
                              therapeutic_agent_out OUT
therapeutic_agent.therapeutic_agent%TYPE,
                              clinical_state_out OUT
indication.indication%TYPE,
                              study_status_out OUT
current_status.label%TYPE,
                              target_number_of_sites_out OUT
study.target_num_of_sites%TYPE,
                              target_number_of_subjects_out OUT

study.target_num_of_subjects%TYPE)

    IS
    BEGIN
        select title, brief_title, client_study_no, sponsor_protocol_no, sponsor_company.company_name sponsor_name,

cro_company.company_name cro_name, ta.therapeutic_agent, i.indication indication,

cs.label current_study_status, nvl(target_num_of_sites, 0) target_num_of_sites, nvl(target_num_of_subjects, 0) target_num_of_subjects

into study_title_out, brief_title_out, study_number_out, protocol_number_out, sponsor_out,

            cro_out, therapeutic_agent_out, clinical_state_out,
            study_status_out, target_number_of_sites_out,
target_number_of_subjects_out
        from study s, company sponsor_company, company cro_company,
therapeutic_agent ta,
            (TRIMMED)

...that runs successfully, when the STUDY.TITLE value in the STUDY table is less then 255 characters. But once the value is over 255 characters then we get the ORA-6502. The TITLE column in the STUDY table is VARCHAR2(1000). The table is able to hold values over 255 characters, but the above procedure chokes. None of the other tables have a TITLE column.

To prove this, we played around with the title value length in the database, and using the application we were able to determine that when the value reaches 255 characters then the procedure chokes. Since the variables are %TYPE'd were scratching our heads trying to understand how it could have a 255 character limit.

Any "off the tops of your heads" feedback would be appreciated.

Chris Grabowy

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Grabowy, Chris
INET: cgrabowy_at_fcg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------