SQL: ORA-01502

 Estoy tratando de insertar 40800 registros en una tabla Oracle a partir de un archivo con sentencias INSERT, y el resultado que obtengo es el error ORA-01502.

¿Porque se produce este error?
De acuerdo al sitio dba-oracle.com, este error se produce porque se esta tratando de insertar 2 o mas veces el mismo registro.

La solución es borrar el índice  indicado en el mensaje del error
ALTER INDEX PRUEBAS.PK_FAKETABLE REBUILD;

Pero también, se debe borrar los registros repetidos del script de carga. Esto lo hice usando GVIM (las instrucciones aquí)

Error SQL: ORA-01502: index 'PRUEBAS.PK_FAKETABLE' or partition of such index is in unusable state
01502. 00000 -  "index '%s.%s' or partition of such index is in unusable state"
*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

Despues de seguir las instrucciones finalmente puedo insertar 23000 registros ya que elimine los repetidos.

Cuando SUBSTR no sirve buenas son las expresiones regulares

[Oracle / SQL] SUBSTR es una función muy útil para recortar cadenas de datos. Pero hay ciertos casos en que se queda corta. Por ejemplo, si necesitas solamente obtener el usuario de un correo electrónico dejando fuera el dominio y la arroba.

El largo de los nombres de usuario varia entonces se debe pensar en algo mas elaborado o ir al grano con las expresiones regulares.

REGEXP_SUBSTR permite extraer una cadena que cumple con una expresión regular.

Ejemplo de uso:
select REGEXP_SUBSTR('m-donoso_2007@dominio.cl','[_a-z0-9-]*') from dual;

Donde

• m-donoso_2007@dominio.cl es el correo (fake por cierto).
• [_a-z0-9-]* Es la expresión regular que toma solo los caracteres indicados símbolos -, _, letras minúsculas y números tantas veces como aparezcan en la cadena. Por lo que deja fuera todo lo que esta después de la arroba.

El resultado de arriba da:
m-donoso_2007

Mas información
http://j.gs/1aEh

ORA-00904: invalid identifier

SQL*PLUS tiene dos tipos de variables (en algunas partes se lee que son tres), una de ellas son las Bind Variables. Las cuales uso (en este caso particular del post), para asignar una fecha del sistema tras un calculo y hacer una consulta de registros que contengan esa fecha. Ejemplo:

VARIABLE DATE_CDR_MES VARCHAR2(9); exec :DATE_CDR_MES := LAST_DAY(ADD_MONTHS(sysdate,-1)); select * from GG_NUM where DATE_CDR=TO_DATE(DATE_CDR_MES,'DD-MM-AA');

Pero, el código del ejemplo me retorna el siguiente error:

Error SQL: ORA-00904: "DATE_CDR_MES": invalid identifier
00904. 00000 -  "%s: invalid identifier"


¿Cual es el error que tiene el código? Es que la variable no esta antecedida por los dos puntos, lo cual la identifica como variable. Lo correcto es que quede de esta forma (:DATE_CDR_MES):

VARIABLE DATE_CDR_MES VARCHAR2(9); exec :DATE_CDR_MES := LAST_DAY(ADD_MONTHS(sysdate,-1)); select * from GG_NUM where DATE_CDR=TO_DATE(:DATE_CDR_MES,'DD-MM-AA');

Cerrando este tema, podemos decir que el error se produce porque se encuentra un valor que no parece ni variable ni sentencia del lenguaje, es un identificador no valido (invalid identifier).

Fuente:
http://j.gs/1Dw2

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Estoy trabajando con SQL*PLUS. Y lo que hago básicamente son unos Scripts que  dejo como archivos .sql y luego los ejecuto desde una Shell. Al momento de ejecutar uno de estos procesos, me apareció el siguiente error:

BEGIN :MES_3030_DESDE := TRUNC(ADD_MONTHS(SYSDATE,-4),'MM'); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


Mi código consiste en la declaración de la variable:
VARIABLE  MES_DESDE VARCHAR2(7);

Asignación de un valor a esa variable, que es el punto donde estaba fallando
exec :MES_DESDE := TRUNC(ADD_MONTHS(SYSDATE,-4),'MM');

Si veo que retorna lo que estoy asignando encuentro cual es el motivo
SELECT TRUNC(ADD_MONTHS(SYSDATE,-4),'MM') FROM dual;


El resultado de la consulta es:
01/03/12
Ocho caracteres, los cuales estoy tratando de poner en un espacio para siete.

Entonces este error ocurre porque estoy tratando de poner mas caracteres en una variable donde no caben. Osea , lo correcto es que la definición sea:
VARIABLE  MES_DESDE VARCHAR2(8);

Pasar parametros a un SQL en SQL PLUS Oracle

He usado bastante SQLPLUS en el último proyecto en que he estado. Ejecuto scripts SQL desde unas Shell en Linux. Y una de las cosas que llegue a necesitar es, indicarle al script SQL la ruta donde quiero que me deje el log. El log se genera a partir de la orden spool, y la Shell puede ser ejecutada desde cualquier lugar. Si no indico donde quiero que queden los logs, la Shell dejara los logs dispersos por todo el sistema si es que el administrador la ejecuta desde donde se le ocurra.


Esta problemática la resolví pasandole desde la Shell la ruta donde quiero que deje el log.
sqlplus -S $user_pass @$x $DIR_LOG

Donde:
$user_pass =usuario_bd/pass_user_bd@base_datos
$script_sql=archivo sql
$DIR_LOG =ruta_archivo_log

$script_sql

Para rescatar la variable desde el PLSQL, es similar a Bash donde se rescatan de acuerdo al orden en que se pasan. En este caso paso una variable por lo que el valor desde el SQL lo rescatare de &1, y lo asigno a una variable local con la palabra define (variable de sustitución). Esto ultimo lo hago para que el código quede mas fácil de leer.

Fuente
http://j.gs/1Brc

Cambiar el nombre a un campo de tabla Oracle

Para cambiar el nombre de un campo en una tabla Oracle, se debe hacer con la siguiente orden:

alter table Tabla rename column columna_actual to columna_nueva;

 Donde:

• Tabla : Nombre de la tabla 
• columna_actual : Nombre de la columna que se va a cambiar 
• columna_nueva : Nombre por el que se va a reemplazar.

ORA-28000: the account is locked

Este error (ORA-28000), ocurre cuando se ingresa un password consecutivamente de forma errónea. Por lo que la solución para eso es ejecutar las siguientes ordenes:

sqlplus "/ as sysdba" alter user HR account unlock; grant connect, resource to HR;

Link
http://j.gs/16aP

SQL*Loader-410: Number to skip must be load-level, not table-level

El contexto en que me encontré con este error fue cargando un archivo de texto en una tabla de Oracle. Esta tarea comúnmente la hago a través de sqlldr y un archivo .ctl que mapea una tabla existente con el contenido del archivo de texto. Esto porque estos procesos deben quedar automatizados.
El script esta vez lo obtuve desde Toad que genera en alguna medida estos archivos.

 
El archivo de texto contiene títulos, por lo que en el archivo .ctl le indico que se salte los títulos (SKIP=1), pero Toad dejo esta instrucción en donde me da error (ver la imagen):

Donde la sintaxis correcta es la que aparece en la imagen a continuación:
OPTIONS (SKIP=1), la configuración al inicio del archivo.

Quitar ceros a una cadena en Oracle

Normalmente uso trim sin parámetros para quitar espacios a las cadenas de texto. Pero en Oracle/PLSQL, esta función tiene mas opciones. La sintaxis es la siguiente:

trim( [ leading | trailing | both [ trim_parametro ] ] cadena )

Donde:

• trim_parametro Es la cadena que queremos quitar, espacio, ceros, etc.
• cadena La cadena con strings o espacios de mas

• leading - quita el trim_parametro desde el inicio de la cadena
• trailing - quita el trim_parametro desde el final de la cadena
• both - quita el trim_parametro desde el inicio y el fin de la cadena
• sin parámetros - quita el trim_parametro desde el inicio y el fin de la cadena

Una aplicación practica para esta función es quitar los ceros de la izquierda de una cadena.

select trim(leading '0' from '000000015737') from dual;

Fuente:
http://j.gs/vgC

Si te ha sido útil o te ha gustado el artículo, ayúdame compartiéndolo con tus amigos. Haz clic en el botón de la red social de tu preferencia. Gracias.

ORA-01481 - Si es texto trátalo como texto.

El siguiente campo TIME_SEND  es un VARCHAR2(20) y guarda hora minutos segundos. Ejemplo: 122040. Ante este tipo de casos siempre aclaro que no es mi letra, osea, algún otro hizo ese campo con ese tipo de datos y yo no lo puedo cambiar así como así.

El punto es que para un reporte en Microstrategy, necesito formatear ese valor que quede: 12:20:40. Para lo cual utilizo esta consulta:

SELECT TO_CHAR(TIME_SEND,'hh:mi:ss') as callTime
FROM Tabla;



Y aparece el error ORA-01481:

ORA-01481: modelo de formato numérico no válido
01481. 00000 -  "invalid number format model"
*Cause:    The user is attempting to either convert a number to a string
           via TO_CHAR or a string to a number via TO_NUMBER and has
           supplied an invalid number format model parameter.
*Action:   Consult your manual.

Para este error mi solución es sencilla. Si es texto trátalo como texto.

SELECT SUBSTR(TIME_SEND,1,2)||':'||SUBSTR(TIME_SEND,3,2)||':'||SUBSTR(TIME_SEND,5,2) AS CALLTIME
FROM Tabla;

ORA-01843: not a valid month

El mensaje que aparecía en el log, después de ejecutar la shell es el siguiente:

 Record 26: Rejected - Error on table TARIFAS_CARGA, column "INICIO".ORA-01843: not a valid month

¿Cual fue la solución? Agregar formato en el archivo ctl para las fechas con la instrucción to_date, de la siguiente forma:
 Archivo CTL.


load data 
 Append into table PERIODOS 
 fields terminated by ";" optionally enclosed by '"'
 ( PERIODO , 
 INICIO "to_date(:INICIO, 'dd-mm-yy')" , 
 FIN "to_date(:FIN, 'dd-mm-yy')" 
 )

Tal vez sea necesario que conozcas primero el formato para las fechas en tu instalación de Oracle.

 select * from nls_session_parameters; 

En la imagen se encuentra marcada la opción NLS_DATE_FORMAT, esta indica el formato de fecha. RR quiere decir YY (año).

Buscar un campo en una Base Oracle

Creo que esta es una necesidad que no se da tan frecuentemente, pero en algún minuto puede ser necesario, como me paso a mi. Saber a que tabla o tablas corresponde un campo. Esta consulta me ha salvado varias veces , de modo que la comparto:

SELECT OWNER, TABLE_NAME , COLUMN_NAME , DATA_TYPE , DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE 'nombre_campo'
ORDER BY 2;

Donde:
nombre_campo es el nombre del campo buscado , el cual puede llevar comodines como %nombre_campo, o nombre_campo%, etc.

ORA-01858 - se ha encontrado un carácter no numérico (Solucionado)

Me encontré con este error: ORA-01858. Al hacer una comparación de fechas.

ORA-01858: se ha encontrado un carácter no numérico donde se esperaba uno numérico

01858. 00000 -  "a non-numeric character was found where a numeric was expected"

*Cause:    The input data to be converted using a date format model was

           incorrect.  The input data did not contain a number where a number was

           required by the format model.

*Action:   Fix the input data or the date format model to make sure the

           elements match in number and type.  Then retry the operation.

 En la consulta uso lo siguiente:

..

WHERE TO_DATE(DEACTIVATION_DATE,'DDMMYY')=TO_DATE(SYSDATE,'DDMMYY')-1;

La causa de este error, es que dado el formato,  no se encuentran los valores numéricos en el lugar que son requeridos.

Y la solución es formatear correctamente las fechas:

..

WHERE TO_DATE(DEACTIVATION_DATE,'dd-mm-yy')=TO_DATE(SYSDATE,'dd-mm-yy')-1;    

ISNULL EN ORACLE - Reemplazar valores nulos

Tengo mas experiencia trabajando en MS SQL y en MYSQL que en Oracle (soy novato) por lo mismo al momento de necesitar hacer algo se me viene a la mente como es en MS SQL / MYSQL y pienso que puede haber varios en una situación similar.

En MS SQL existe la función ISNULL para reemplazar valores nulos, ejemplo:
SELECT Campo1, ISNULL(Campo2, '0') FROM Tabla 


En MySQL la función es:
SELECT Campo1,  COALESCE (Campo2, '0') FROM Tabla 


En Oracle, la función es NVL, ejemplo:
SELECT Campo1, NVL(Campo2, '0') FROM Tabla 


Lo que hacen estas funciones es reemplazar el valor nulo si existe por el que se le indica.

Crear tabla desde un select en Oracle

Crear una tabla a partir de una consulta es muy sencillo en Oracle.
En el ejemplo, que agrego a continuación se ve claramente que primero se debe indicar que se crea una tabla:
CREATE TABLE AS y a continuación el SELECT entre paréntesis.


CREATE TABLE subtelefono AS
(SELECT TELEFONO, IMEI FROM TELEFONOS);

sp_helptext en Oracle o Ver código de un procedimiento

Para quienes están mas familiarizados con MS SQL, cuando trabajen en Oracle estarán buscando un función para obtener el código de algún procedimiento como es sp_helptext. Encontré esta sentencia que cumple con esta función:

SELECT TEXT FROM ALL_SOURCE WHERE NAME='MOSTRAR_DATOS' ORDER BY LINE;

donde MOSTRAR_DATOS , es el nombre del procedimiento que se quiere obtener el código.

En la imagen que adjunto, primero creo el procedimiento y luego obtengo el mismo código con la sentencia antes mencionada.

Oracle - Too many values ORA-00913

Seguir a @bequick

Estaba tratando de hacer una inserción a partir de un SELECT y me apareció este error: 

Error SQL: ORA-00913: demasiados valores 00913. 00000 - "too many values"
El error en mi caso consistía en la siguiente trivialidad:

INSERT INTO CopiaTELEFONOS (campo1, campo2, campo3)
SELECT campo1, campo2 FROM TELEFONOS;

Tenia mas campos en la primera sentencia que en la segunda donde seleccionaba los campos.
El select es de ejemplo, no es que use esos nombres para las tablas. El caso real se trataba de una tabla con decenas de campos.