Home » SQL & PL/SQL » SQL & PL/SQL » Problems converting String to data
Problems converting String to data [message #655765] |
Mon, 12 September 2016 05:19 |
|
RubenDC
Messages: 2 Registered: September 2016
|
Junior Member |
|
|
Hi everyone,
im having troubles to do a cursor:
CREATE OR REPLACE PROCEDURE PRUEBA_CURSOR_TEMP AS
BEGIN
DECLARE
CURSOR act_eci IS
select fe_emision_txt, fe_relacion_txt, fe_entrega_txt, importe_cobrado_txt from bi_Ext_eci_liquidaciones_ods
FOR UPDATE;
fe_emision_txt VARCHAR2(8);
fe_relacion_txt VARCHAR2(8);
fe_entrega_txt VARCHAR2(8);
importe_cobrado_txt VARCHAR2(20);
fe_emision DATE;
fe_relacion DATE;
fe_entrega DATE;
importe_cobrado NUMBER;
control_error EXCEPTION;
BEGIN
OPEN act_eci;
FETCH act_eci INTO fe_emision,fe_relacion,fe_entrega,importe_cobrado;
WHILE act_eci%found
LOOP
UPDATE bi_Ext_eci_liquidaciones_ods
SET fe_emision = TO_DATE(fe_emision_txt, 'DD/MM/YYYY'),
fe_relacion = TO_DATE(fe_relacion_txt, 'DD/MM/YYYY'),
fe_entrega = TO_DATE(fe_entrega_txt, 'DD/MM/YYYY'),
importe_cobrado = to_number(importe_cobrado_Txt)
WHERE CURRENT OF act_eci;
FETCH act_eci INTO fe_emision,fe_relacion,fe_entrega,importe_cobrado;
END LOOP;
CLOSE act_eci;
COMMIT;
END;
END PRUEBA_CURSOR_TEMP;
I get this error code when i try to run the script
ORA-01861: literal does not match format string
The problem its what i have some data what its dont possible to convert to date, but i need to keep this data in mi table.
I need some tip to skip the data what its impossible convert to date.
Some samples of data:
'DEO PROM' BAD,
'20151104' OK => 04/11/2015,
null BAD,
'60220001' BAD,
'20160906 ' OK => 06/09/2016
Thanks in advance
|
|
|
Re: Problems converting String to data [message #655766 is a reply to message #655765] |
Mon, 12 September 2016 05:52 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
The simplest option is to enclose UPDATE into its own BEGIN-END block, with an EXCEPTION section, so that the procedure continues even though an error is being raised, such as
WHILE act_eci%found
LOOP
BEGIN
UPDATE bi_Ext_eci_liquidaciones_ods
SET fe_emision = TO_DATE(fe_emision_txt, 'DD/MM/YYYY'),
fe_relacion = TO_DATE(fe_relacion_txt, 'DD/MM/YYYY'),
fe_entrega = TO_DATE(fe_entrega_txt, 'DD/MM/YYYY'),
importe_cobrado = to_number(importe_cobrado_Txt)
WHERE CURRENT OF act_eci;
EXCEPTION
WHEN ... THEN ...
END;
FETCH act_eci INTO fe_emision,fe_relacion,fe_entrega,importe_cobrado;
END LOOP;
The simplest EXCEPTION handler is WHEN OTHERS THEN NULL, but use it carefully! It says that you really don't matter what happened wrong, and you want to discard it. It will "hide" all errors, so you might get the "PL/SQL procedure successfully completed" message without a real success. Therefore, read about WHEN OTHERS.
Furthermore, I'd rather use a cursor FOR loop, as it is a lot simpler (doesn't require
CREATE OR REPLACE PROCEDURE PRUEBA_CURSOR_TEMP AS
BEGIN
FOR cur_r IN (select fe_emision_txt, fe_relacion_txt, fe_entrega_txt, importe_cobrado_txt
from bi_Ext_eci_liquidaciones_ods)
LOOP
BEGIN
UPDATE bi_Ext_eci_liquidaciones_ods SET
fe_emision = TO_DATE(cur_r.fe_emision_txt, 'DD/MM/YYYY'),
fe_relacion = TO_DATE(cur_r.fe_relacion_txt, 'DD/MM/YYYY'),
fe_entrega = TO_DATE(cur_r.fe_entrega_txt, 'DD/MM/YYYY'),
importe_cobrado = to_number(cur_r.importe_cobrado_Txt)
WHERE <condition goes here; I don't know it>;
EXCEPTION
WHEN ...
END;
END LOOP;
COMMIT;
END PRUEBA_CURSOR_TEMP;
|
|
|
|
Re: Problems converting String to data [message #655768 is a reply to message #655765] |
Mon, 12 September 2016 06:00 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
This:
'20151104' OK => 04/11/2015,
is only ok if you're using the format YYYYMMDD, which you're not.
With the format you're using it throws:
SQL> select to_date('20151104', 'DD/MM/YYYY') from dual;
select to_date('20151104', 'DD/MM/YYYY') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
because it expects the string to contain slashes. If you remove those from the format mask you get:
SQL> select to_date('20151104', 'DDMMYYYY') from dual;
select to_date('20151104', 'DDMMYYYY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Unsurprisingly since 15 is month in that format.
Here's an actual working example:
SQL> select to_date('20151104', 'YYYYMMDD') from dual;
TO_DATE('201511
---------------
20151104 000000
So the first thing you need to do is work out the correct format for the valid data. Is all of the date data in the same format, or is there multiple formats?
After that you need to come up with a way to ignore the invalid data. Can you have rows where one or two of columns has a valid date but not all three?
Some other observations:
1) Variables should be typed to the corresponding colum with %TYPE wherever possible.
2) A cursor for loop would make for cleaner code then the while loop you've got.
3) A loop shouldn't be necessary at all here - it can be done with a single update statement.
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:33:12 CDT 2024
|