Error retrieving record with a null property in Oracle

Navigation
1

when trying to get a record in ORACLE and the value of the property which I then use to make a validation contains a null value query error 

Informe de error -
ORA-01403: No se ha encontrado ningún dato
ORA-06512: en línea 38
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

I share the query 

Tabla :[r5addetails]
Propiedades de la tabla:

[add_code = '3340.00.114.10.3';
ADD_TEXT = null;
add_rentity = 'TASK';
add_type='*';
add_lang='ES';]

    DECLARE
        codigo VARCHAR2(2000) :='3340.00.114.10.3';
        long_var LONG;
        vv   VARCHAR2 (2000);
        nuevasReferencias VARCHAR2(2000); 
        add_refes_in VARCHAR2(2000) :='MCG.26.10.2017';
    BEGIN
        Select ADD_TEXT INTO long_var FROM r5addetails where add_code=(codigo||'#0') and add_rentity='TASK' and add_type='*' and add_lang='ES'; 
        nuevasReferencias:= REPLACE (add_refes_in,'####', CHR(10));
        vv:=long_var;
        dbms_output.put_line( vv );
        if instr( vv, 'REF:' ) != 0 then
            DBMS_OUTPUT.put_line('NUEVA REFERENCIA: ' || SUBSTR(vv,0,INSTR(vv,'REF:',1,1)-1)||' REF:'|| nuevasReferencias);
          else
            DBMS_OUTPUT.put_line('AGREGAR NUEVA REFERENCIA: ' || vv ||' REF:'|| nuevasReferencias);
         end if;
    END;

Any ideas, how to avoid that error and try to return a blank value or something ?. Thanks

NOTE: Not to earn points like how to ask the question , I would appreciate the solution rather than criticize the way you write. Thanks

    
asked by Manux 26.10.2017 в 13:47
source

1 answer

0

Answer:

I wrap the query with a Begin / Exception to detect that the property which I use (ADD_TEXT) of the table is null. 

 DECLARE
        codigo VARCHAR2(2000) :='3340.00.114.10.3';
        long_var LONG;
        vv   VARCHAR2 (2000);
        nuevasReferencias VARCHAR2(2000); 
        add_refes_in VARCHAR2(2000) :='MCG.26.10.2017';

    BEGIN
        Select ADD_TEXT INTO long_var FROM r5addetails where add_code=(codigo||'#0') and add_rentity='TASK' and add_type='*' and add_lang='ES'; 
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            long_var:=NULL;
    END;

    nuevasReferencias:= REPLACE (add_refes_in,'####', CHR(10));
    vv:=long_var;
    dbms_output.put_line( vv );
    if instr( vv, 'REF:' ) != 0 then
        --UPDATE r5addetails SET ADD_TEXT = SUBSTR(vv,0,INSTR(vv,'REF:',1,1)-1)||' REF:'|| referencias where add_code=(codigo||'#0') and add_rentity='TASK' and add_type='*' and add_lang='ES';
        DBMS_OUTPUT.put_line('NUEVA REFERENCIA: ' || SUBSTR(vv,0,INSTR(vv,'REF:',1,1)-1)||' REF:'|| nuevasReferencias);
      else
        --UPDATE r5addetails SET ADD_TEXT = vv ||' REF:'|| referencias where add_code=(codigo||'#0') and add_rentity='TASK' and add_type='*' and add_lang='ES';
        DBMS_OUTPUT.put_line('AGREGAR NUEVA REFERENCIA: ' || vv ||' REF:'|| nuevasReferencias);
     end if;
END;

Greetings.

    
answered by 27.10.2017 в 08:14
Access element list VUE JS How to select an element of the DOM with ionic 2?