Re: ORA-06502: PL/SQL: numeric or value error

A copy of this was sent to mozkill_at_my-deja.com (if that email address didn't require changing) On Mon, 26 Jul 1999 16:31:44 GMT, you wrote:

>I get an error that I cannot figure out... in SQLPlus I compile the
>procedure with no problems and then execute it like so:
>
>begin
>WATT.SP_TWW_DA_PTH_LOC_ADD('Template', 'DIR', 'Testing Directory');
>end;
>/
>
>and I get the following error:
>
>SQL> start c:\tww\twwadmin\scrips-twwadmin\watt.sql
>
>Package created.
>
>
>Package body created.
>
>No errors.
>SQL> begin
> 2 WATT.SP_TWW_DA_PTH_LOC_ADD('Template', 'DIR', 'Testing Directory');
> 3 end;
> 4 /
>begin
>*
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "DMADMIN.WATT", line 631
>ORA-06512: at line 2
>
>The stored procedure is as follows:
>

I'll betcha line 631 is:

        dbms_output.put_line('creating new record for ' + p_pth_loc_nm);

You are trying to use addition on character strings. its getting the value error trying to convert the strings into numbers.

try:

        dbms_output.put_line('creating new record for ' || p_pth_loc_nm);

>/*******************************************************************/
>/* PROCEDURE SP_TWW_DA_PTH_LOC_ADD
> */
>/*******************************************************************/
>PROCEDURE SP_TWW_DA_PTH_LOC_ADD(
> p_pth_classn_cd IN varchar2,
> p_pth_ty_cd IN varchar2,
> p_pth_loc_nm IN varchar2)
>IS
>v_pth_loc_id NUMBER(15) Default NULL;
>BEGIN
> dbms_output.put_line('starting sp_tww_da_pth_loc_add');
>
> /* Check all the required parameters are supplied */
> IF p_pth_classn_cd IS NULL OR p_pth_ty_cd IS NULL OR p_pth_loc_nm
>IS NULL THEN
> RAISE req_param_not_sppld;
> END IF;
>
> UPDATE cmi.da_pth_loc a
> SET a.inact_ind = inact_ind_false
> WHERE a.pth_ty_cd = p_pth_ty_cd AND a.pth_classn_cd =
>p_pth_classn_cd AND a.pth_loc_nm = p_pth_loc_nm;
>
> IF SQL%NOTFOUND THEN
> /* Get the max id */
> SELECT MAX(pth_loc_id) INTO v_pth_loc_id FROM cmi.da_pth_loc;
> IF SQL%NOTFOUND THEN
> RAISE id_not_found;
> END IF;
>
> dbms_output.put_line('creating new record for ' + p_pth_loc_nm);
> INSERT INTO cmi.da_pth_loc
> (pth_loc_id, pth_ty_cd, pth_classn_cd, pth_loc_nm,
> inact_ind, cre_agt_id, cre_dtm, chg_agt_id, chg_dtm)
> VALUES (v_pth_loc_id, p_pth_ty_cd, p_pth_classn_cd,
>p_pth_loc_nm,
> inact_ind_false, agt_id, sysdate, agt_id, sysdate);
> IF SQL%NOTFOUND THEN
> RAISE failed_insert;
> END IF;
> END IF;
>
> EXCEPTION
> WHEN req_param_not_sppld THEN
> ROLLBACK;
> raise_application_error(err_req_param_not_supplied,
>msg_req_param_not_supplied, FALSE);
> WHEN id_not_found THEN
> ROLLBACK;
> raise_application_error(err_id_not_found, msg_id_not_found,
>FALSE);
> WHEN failed_insert THEN
> ROLLBACK;
> raise_application_error(err_failed_insert, msg_failed_insert,
>FALSE);
> WHEN OTHERS THEN
> ROLLBACK;
> RAISE;
>
>END SP_TWW_DA_PTH_LOC_ADD;
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jul 26 1999 - 12:01:02 CDT