Re: Stored Procedure Woes

James,

Can you explain what it is you are trying to do with the statement "SELECT TO_CHAR(A.VALUE+1)" Since you did not post the DDL for the TEST.MISC_INFO I have assumed from the parameter type for sDefaultValue that TEST.MISC_INFO.VALUE must also be a VARCHAR2. If this is true then you are adding 1 to a VARCHAR2 value then trying to convert it TO_CHAR.

The ORA-6502 may also be getting raised due to improperly typed data passed to the procedure or if the size of the VARCHAR2 parameter size is exceeded. If this is not being called from a procedure and instead is being called from an application then you may be binding the incorrect datatype at the application level.

Let me know if any of this helps you. If not then post the DDL for the table and a sample of the calling procedure as well.

If you do not have a PL/SQL debugger or have never looked at one then I would suggest that you investigate what is available as this will help you IMMENSELY. I can give you my plug for a tool if you wish.

Hth,

Cliff

P.S. Please forgive this suggestion if you edited the code for brevity, but I would suggest some error handling within the procedure and move the commit to the caller unless you have a really good reason for placing the commit where it is.

"SWBT" <james.williams_at_xitc.com> wrote in message news:ECRZ5.169$3A6.47236_at_nnrp3.sbc.net...
> Greetings,
>
> I am trying to figure out what is wrong with the following stored
 procedure.
> It compiles without any errors; however, the procedure fails with the
> following
> error whenever VALUE has more that one character in it. Also, will SET
> TRANSACTION ISOLATION LEVEL SERIALIZABLE ensure concurrency is not
 violated
> during this stored procedure.
>
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "XIPAY.INC_MIT", line 9
>
> Here is the stored procedure. Thanks in advance to anyone that can assist
> me with this problem. Please send replies to : james.williams_at_xitc.com
>
> CREATE OR REPLACE PROCEDURE TEST.INC_MIT (sName in VARCHAR2,
 sDefaultValue
> in out VARCHAR2) is
> nTotalRows NUMBER;
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SELECT COUNT(NAME) INTO nTotalRows FROM TEST.MISC_INFO WHERE NAME =
 sName;
> IF nTotalRows = 0 THEN
> INSERT INTO TEST.MISC_INFO (MISC_INFO_ID, NAME, VALUE, MOD_DATE)
 VALUES
> (MISC_INFO_ID_SEQ.NEXTVAL, sName, sDefaultValue, SYSDATE);
> ELSE
> SELECT TO_CHAR(A.VALUE+1) INTO sDefaultValue FROM TEST.MISC_INFO A
 WHERE
> A.NAME = sName;
> UPDATE TEST.MISC_INFO SET VALUE = sDefaultValue where NAME = sName;
> END IF;
> COMMIT;
> END;
>
>
>
>
Received on Wed Dec 13 2000 - 23:05:58 CST