Re: Oracle Error 1502

From: <markp7832_at_my-deja.com>
Date: Fri, 10 Dec 1999 01:52:42 GMT
Message-ID: <82pmd9$vv6$1@nnrp1.deja.com>

In article <82p803$lfs$1_at_nnrp1.deja.com>,   fatwen_at_my-deja.com wrote:
> Help!
>
> Does anyone there knows what error 1502 means?
>
> And is there any web site allow you to look up
> all the ORACLE errors codes?
>
> Thanks
>
>

It would appear that you have an invalid index on a partitioned table. Here is the response from the oerr utility available on most Unix Oracle platforms from the command line: seqdev 7 % oerr ora 01502
01502, 00000, "index '%s.%s' or partition of such index is in unusable state"
// MERGE: 1489 RENUMBERED TO 1502
// *Cause: An attempt has been made to access an index or index partition

//         that has been marked unusable by a direct load or by a DDL
//         operation
// *Action: DROP the specified index, or REBUILD the specified index, or
//         REBUILD the unusable index

partition

Here is the short error message available from the instance via SQL OPS2> @ora_error
Enter Oracle Error Number, i.e, ORA-00100 = 100 ==> 01502 ORA-01502: index '.' or partition of such index is in unusable state

Here is some code I use to get the short error message, above, from the Oracle instance
set echo off
rem
rem Pl/sql script to create procedure to report Oracle error messages rem in sqlplus
rem
rem 1996 07 11 m d powell New script. rem 1998 06 23 m d powell Modify to be in-stream procedure from stored
rem
set feedback off
set serveroutput on
set verify off
accept err_code prompt "Enter Oracle Error Number, i.e, ORA-00100 = 100 ==> "
declare
procedure ora_error (
  v_err_no in number
  )
is

v_rc       number              ;
v_msg      varchar2(100)       ;

--
begin
if v_err_no > 0 then
  v_rc := v_err_no * -1 ;
else

  v_rc  :=  v_err_no           ;
end if                         ;
v_msg  :=  sqlerrm(v_rc)       ;
dbms_output.put_line(v_msg)    ;
end ora_error                  ;

--
begin
 ora_error(&err_code);
end;
/
set feedback on
undefine err_code

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 09 1999 - 19:52:42 CST