| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL oddities
Hi Lee,
The official error message is as follows: ORA-06502 PL/SQL: numeric or value error
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Looking at the code, it could be one of a number of things, but my first guess is that your tablespace name is longer than 12 characters. Change the line from
tname := cur_tablespace;
to
tname := SUBSTR( cur_tablespace, 1, 12); -- Only take the
first 12 characters of the tablespace name
Tell me if this helps!
Regards
Oweson Flynn
> All,
>
> I have a procedure (inherited) that tots up various information on
> tablespaces (free space, used, pct free etc). This works fine on one DB
but
> craps out on another on the same box. The first DB brings back the
expected
> results (formatting is probably out in the mail)
>
> ======================================================================
> Table_Space Total_Size Space_Used Free_Space PCT_USED
> ======================================================================
> DATA 63000 30170 32830 47
> INDEX 9500 0 9500 0
> RBS 500 200 300 40
> SYSTEM 500 41 459 8
> TEMP 1000 998 2 99
>
> However on the other DB I get the following
>
> ======================================================================
> Table_Space Total_Size Space_Used Free_Space PCT_USED
> ======================================================================
> DATA 22000 17049 4951 77
> INDEX 6170 4170 2000 67
> DECLARE
> *
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at line 31
>
> The second DB has many more tablespaces. Could anyone please shed some
light
> on this, my PL/SQL isn't brilliant and I cannot fix it.
>
> Regards
>
> Lee
>
> Code is as follows
>
> spool &1
> set serveroutput on
> set verify off
> set feedback off
>
> DECLARE
> cursor C_TS is
> select tablespace_name,sum((bytes)/(1024*1024))
> from dba_data_files
> group by tablespace_name;
> cur_tablespace varchar2(30);
> tname char(12);
> size_str varchar2(132);
> cur_tot_size number;
> cur_used_size number;
> cur_free_size number;
> cur_pct_used number;
> BEGIN
> open C_TS;
>
>
dbms_output.put_line('======================================================
> ================');
> dbms_output.put_line('Table_Space Total_Size Space_Used
> Free_Space PCT_USED');
dbms_output.put_line('======================================================
> ================');
>
> LOOP
> fetch C_TS into cur_tablespace,cur_tot_size;
> exit when C_TS%notfound;
>
> select sum((bytes)/(1024*1024))
> into cur_free_size
> from dba_free_space
> where tablespace_name=cur_tablespace;
>
> cur_used_size := cur_tot_size - cur_free_size;
> cur_pct_used :=
> 100-(round(cur_free_size*100)/cur_tot_size+0.5);This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Received on Thu Nov 23 2000 - 06:51:35 CST
 |
 |