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

On May 30, 4:07 pm, DA Morgan <damor..._at_psoug.org> wrote:
> nodge wrote:
> > I've got an PL/SQL Error:
>
> > SQL> declare
> > 2 v_tmp char(15);
> > 3 BEGIN
> > 4 v_tmp := '';
> > 5 v_tmp := v_tmp || 'thw';
> > 6 insert into thwtab values (42, v_tmp);
> > 7 commit;
> > 8 end;
> > 9 /
> > declare
> > *
> > ERROR at line 1:
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at line 5
>
> > Any Suggestions? Whats wrong?
>
> > thx!
> > nodge
>
> There is almost no reason to ever use CHAR. You can not add
> three characters to what is already a 15 character string.
>
> Also note that := '' has no meaning in Oracle as you are
> using it. v_tmp is already NULL and if you want to set it to
> NULL at some other point then just do so.
>
> v_tmp := NULL;
>
> In Oracle, unlike SQL Server, closed quotes is not null.

Except that assigning '' to a CHAR value is a special case:

DECLARE
        v_tmp CHAR(15);
BEGIN

        DBMS_OUTPUT.PUT_LINE('Length before assignment:');
        DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(LENGTH(v_tmp)),'NULL'));

        v_tmp := NULL;
        DBMS_OUTPUT.PUT_LINE('Length after assigning NULL:');
        DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(LENGTH(v_tmp)),'NULL'));

        v_tmp := '';
        DBMS_OUTPUT.PUT_LINE('Length after assigning '''':');
        DBMS_OUTPUT.PUT_LINE(LENGTH(v_tmp));

Length before assignment:
NULL
Length after assigning NULL:
NULL
Length after assigning '':
15

PL/SQL procedure successfully completed.

I agree that you should never use CHAR for anything, ever, unless you specifically need its blank-padding behaviour. Received on Thu May 31 2007 - 01:33:59 CDT