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 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #655767 is a reply to message #655765] Mon, 12 September 2016 05:53 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
use exception handling of PL/SQL
Re: Problems converting String to data [message #655768 is a reply to message #655765] Mon, 12 September 2016 06:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Problems converting String to data [message #655769 is a reply to message #655768] Mon, 12 September 2016 06:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you can have rows where two of the dates are valid and one isn't then exception handling will cause none of them to be set.
Re: Problems converting String to data [message #655771 is a reply to message #655769] Mon, 12 September 2016 06:43 Go to previous message
RubenDC
Messages: 2
Registered: September 2016
Junior Member
thanks everyone for your replies.

@Littlefoot: Im trying to adapt, my code on your tip, im already running a fixed code and seems to be ok Smile Thanks so much

@sandeep_orafaq: Ill check this doc and I hadn't got things quite clear what the exception i must to use

@cookiemonster: The data mask was my error, i solved it. Thanks for advice me
Previous Topic: Unique Constraint With DEFERRABLE INITIALLY DEFERRED NOVALIDATE
Next Topic: Improve nested Query, if possible
Goto Forum:
  


Current Time: Thu Mar 28 13:57:19 CDT 2024