From cgrabowy@fcg.com Tue, 04 Sep 2001 10:43:29 -0700 From: "Grabowy, Chris" Date: Tue, 04 Sep 2001 10:43:29 -0700 Subject: ORA-6502: numeric or value error. 8.1.5, NT Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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).