SQL*Loader-704: Internal error: ulmtsyn and ORA-01480

load.ctl  parameter file content :

LOAD DATA

INFILE dummy_table.txt

BADFILE dummy_table.bad

DISCARDFILE dummy_table.dsc

TRUNCATE 

INTO TABLE dummy_table_length_is_more_than_30

FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS(  msisdn )

Execution

D:mp>sqlldr mennan/mennan@ora11gr2 control=load.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Fri Aug 2 11:52:37 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulmtsyn: OCIStmtExecute(tabhp) [1480]

ORA-01480:  trailing null missing from STR bind value

D:mp>

The error is because of table name(dummy_table_length_is_more_than_30) length is 34 which is not allowed in oracle(the internal error that is thrown, is not meaningful in my opinion). Please note that, every object shall have name  is less than 30 chars. ( I do not know why this restriction exists in Oracle in 21st centry :). I hope, Oracle will remove this restriction in object names).

Possible Causes of “ORA-06502: PL/SQL: numeric or value error”

Sometimes people ask me that what the reason of ORA-06502 is. Mostly, this is because of carelessness of developersJ. Official description of ORA-06502 is

[oracle@dhcppc5 ~]$ oerr ora 6502

06502, 00000, "PL/SQL: numeric or value error%s"

// *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.

[oracle@dhcppc5 ~]$

This exception also exists in the STANDART package of Oracle database:

  VALUE_ERROR exception;

    pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');

I show possible reasons of this error below:

DECLARE

  i  NUMBER;

  i2 NUMBER NOT NULL := 0;-- !is not a best practice...

  i3 NUMBER(1);

  i4 POSITIVE;

  s  VARCHAR2(3) DEFAULT 'aaa';-- !is not a best practice...

  s2 VARCHAR2(1);

BEGIN

  -- assign to a number data type variable a non-number value

  i := s; -- raises ORA-06502: PL/SQL: numeric or value error: character to number conversion error

  -- assign more values (in terms of length) to a variable with less size

  s2 := s; -- raises ORA-06502: PL/SQL: numeric or value error: character string buffer too small

  -- assign a not-null-constrained value to null value

  i2 := i; -- raises ORA-06502: PL/SQL: numeric or value error

  -- assign bigger numeric value to a smaller size numeric variable

  i3 := 10; -- raises  ORA-06502: PL/SQL: numeric or value error: number precision too large

  -- assign negative value to positive data type

  i4 := -1; -- raises  ORA-06502: PL/SQL: numeric or value error

END;

Solution of “ORA-06520: PL/SQL: Error loading external library” and “ORA-06522: … :only ET_DYN and ET_EXEC can be loaded”

Please read External C procedures inside oracle database post, first.

UNIX

=======

[oracle@dhcppc5 sample_c_ext]$ gcc -c  c_system_lib.c

[oracle@dhcppc5 sample_c_ext]$ ld -r -o  c_system_lib.so c_system_lib.o

[oracle@dhcppc5 sample_c_ext]$ ls -ltr  c_system_lib.so

-rw-rw-r-- 1 oracle oracle 1242 Nov 27 19:08 c_system_lib.so

[oracle@dhcppc5 sample_c_ext]$ chmod 755 c_system_lib.so

ORACLE

=======

SQL> select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;

select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual

                                                            *

ERROR at line 1:

ORA-06520: PL/SQL: Error loading external library

ORA-06522: /home/oracle/app/oracle/product/11.2.0/dbhome_2/lib/c_system_lib.so:

cannot open shared object file: No such file or directory

UNIX

=======

[oracle@dhcppc5 sample_c_ext]$ cp c_system_lib.so $ORACLE_HOME/lib

ORACLE

=======

SQL> select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;

select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual

                                                            *

ERROR at line 1:

ORA-06520: PL/SQL: Error loading external library

ORA-06522: /home/oracle/app/oracle/product/11.2.0/dbhome_2/lib/c_system_lib.so:

only ET_DYN and ET_EXEC can be loaded

UNIX

=======

[oracle@dhcppc5 sample_c_ext]$  gcc -c  c_system_lib.c

[oracle@dhcppc5 sample_c_ext]$ ld -shared -melf_i386 -o c_system_lib.so c_system_lib.o

[oracle@dhcppc5 sample_c_ext]$ chmod 755 c_system_lib.so

[oracle@dhcppc5 sample_c_ext]$ cp c_system_lib.so $ORACLE_HOME/lib

[oracle@dhcppc5 sample_c_ext]$

ORACLE

=======

SQL> select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;

EXECUTESHELLCOMMANDVIAC('/BIN/LS/HOME/ORACLE')

--------------------------------------------------------------------------------

apex

apexlistener.sh

Solution of "ORA-02303: cannot drop or replace a type with type or table dependents" and "ORA-14452: attempt to create, alter or drop an index on temporary table already in use"

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

Connected as mennan

SQL> drop type ACTIVATION_TYP;

drop type ACTIVATION_TYP

ORA-02303: cannot drop or replace a type with type or table dependents

SQL> set serveroutput on;

SQL>

SQL> BEGIN

  2    dbms_utility.get_dependency(TYPE   => 'TYPE',

  3                                SCHEMA => 'MENNAN',

  4                                NAME   => 'ACTIVATION_TYP');

  5  END;

  6  /

-

DEPENDENCIES ON MENNAN.ACTIVATION_TYP

------------------------------------------------------------------

*TYPE MENNAN.ACTIVATION_TYP()

*   TYPE BODY MENNAN.ACTIVATION_TYP()

*   TABLE MENNAN.RLM$SESSRSLTTTAB_97272()

PL/SQL procedure successfully completed

SQL> drop table RLM$SESSRSLTTTAB_97272;

drop table RLM$SESSRSLTTTAB_97272

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL>

SQL> select sid, serial# from v$session where sid in ( select sid from v$lock where id1 =

 ( select object_id from user_objects where object_name='RLM$SESSRSLTTTAB_97272'));---- find  sessions to be killed

SQL> alter system kill session '122,22';--kill sessions sid,serial

SQL> drop table RLM$SESSRSLTTTAB_97272;

Table dropped

SQL>

SQL> drop type ACTIVATION_TYP;

Type dropped

SQL>

Solution of “ORA-38473: cannot drop a type used for Expression Filter attribute set”

CREATE TYPE ACTIVATION_TYP AS OBJECT

(

    MSISDN              VARCHAR2(32),

    RATEPLAN_NAME       VARCHAR2(32),

    ACTIVATION_REASON   VARCHAR2(32)

);

/

BEGIN

  dbms_rlmgr.create_rule_class(rule_class   => 'WELCOME_SMS',  -- rule name also generated table name

                               event_struct => 'ACTIVATION_TYP', -- type for event parameters

                               action_cbk   => 'SEND_SMS',  -- function to be fired when rules matched

                               actprf_spec  => 'SMS_TEXT  VARCHAR2(160)' -- additional info when rules are matched

                               );

END;

/

BEGIN

  dbms_rlmgr.drop_rule_class(rule_class => 'WELCOME_SMS');

END;

/

DROP TYPE ACTIVATION_TYP;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-38473: cannot drop a type used for Expression Filter attribute set

ORA-06512: at line 62

SQL> SELECT ATTRIBUTE_SET_NAME FROM USER_EXPFIL_ATTRIBUTE_SETS WHERE ATTRIBUTE_SET_NAME = 'ACTIVATION_TYP';

ATTRIBUTE_SET_NAME

--------------------------------

ACTIVATION_TYP

SQL> SELECT ATTRIBUTE FROM USER_EXPFIL_ATTRIBUTES WHERE ATTRIBUTE_SET_NAME = 'ACTIVATION_TYP';

ATTRIBUTE

--------------------------------

MSISDN

RATEPLAN_NAME

ACTIVATION_REASON

RLM$CRTTIME

BEGIN

  dbms_expfil.DROP_ATTRIBUTE_SET(attr_set => 'ACTIVATION_TYP');

END;

/

DROP TYPE ACTIVATION_TYP;

Type dropped