[Manutenção] Prova Supero - Oracle, Oracle

[Manutenção] Prova Supero - Oracle

DECLARE

input_1 CLOB := '4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625
';
  input_2 CLOB := '4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625
554733251368
04733251368
0003
';
  input_3 CLOB := '4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625
554733251368
04733251368
0003
08007294568
';

vr_pos       PLS_INTEGER := 1;
  vr_linha     VARCHAR2(32767);
  vr_tam_arq   PLS_INTEGER;
  vr_tam_linha PLS_INTEGER;
  pr_nrtel     VARCHAR2(4000);
  pr_nrtelatu  VARCHAR2(4000);
  pr_dserro    VARCHAR2(4000);
  vr_tamnrtel  varchar2(4000);

BEGIN
  -- Manipula o arquivo input_1 
  vr_tam_arq := LENGTH(input_1);

WHILE vr_pos <= vr_tam_arq LOOP
    vr_tam_linha := INSTR(input_1, CHR(10), vr_pos) - vr_pos;
    IF vr_tam_linha < 0 THEN
      vr_tam_linha := vr_tam_arq + 1 - vr_pos;
    END IF;
  
    vr_linha := SUBSTR(input_1, vr_pos, vr_tam_linha);
    dbms_output.put('Entrada: ' || vr_linha);
  
    pr_nrtel    := vr_linha;
    vr_tamnrtel := LENGTH(trim(replace(pr_nrtel, chr(13), '')));
    CASE vr_tamnrtel
      WHEN 13 THEN
        -- 5547984461240    MOB: +55 (47) 9 8442-1245
        pr_nrtelatu := utl_lms.format_message('MOB: +%s (%s) %s %s-%s',
                                              SUBSTR(pr_nrtel, 1, 2),
                                              SUBSTR(pr_nrtel, 3, 2),
                                              SUBSTR(pr_nrtel, 5, 1),
                                              SUBSTR(pr_nrtel, 6, 4),
                                              SUBSTR(pr_nrtel, 10, 4));
      WHEN 12 THEN
        IF SUBSTR(pr_nrtel, 1, 1) = '0' THEN
          -- 047984461240     MOB: (47) 9 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: (%s) %s %s-%s',
                                                SUBSTR(pr_nrtel, 2, 2),
                                                SUBSTR(pr_nrtel, 4, 1),
                                                SUBSTR(pr_nrtel, 5, 4),
                                                SUBSTR(pr_nrtel, 9, 4));
        ELSE
          -- 554784461240     MOB: +55 (47) 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: +%s (%s) %s-%s',
                                                SUBSTR(pr_nrtel, 1, 2),
                                                SUBSTR(pr_nrtel, 3, 2),
                                                SUBSTR(pr_nrtel, 5, 4),
                                                SUBSTR(pr_nrtel, 9, 4));
        END IF;
      WHEN 11 THEN
        IF SUBSTR(pr_nrtel, 1, 1) = '0' THEN
          -- 04784461240      MOB: (47) 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: (%s) %s-%s',
                                                SUBSTR(pr_nrtel, 2, 2),
                                                SUBSTR(pr_nrtel, 4, 4),
                                                SUBSTR(pr_nrtel, 8, 4));
        ELSE
          -- 47984461240      MOB: (47) 9 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: (%s) %s %s-%s',
                                                SUBSTR(pr_nrtel, 1, 2),
                                                SUBSTR(pr_nrtel, 3, 1),
                                                SUBSTR(pr_nrtel, 4, 4),
                                                SUBSTR(pr_nrtel, 8, 4));
        END IF;
      WHEN 10 THEN
        IF SUBSTR(pr_nrtel, 3, 1) = '8' OR SUBSTR(pr_nrtel, 3, 1) = '9' THEN
          -- 4784461240   MOB: (47) 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: (%s) %s-%s',
                                                SUBSTR(pr_nrtel, 1, 2),
                                                SUBSTR(pr_nrtel, 3, 4),
                                                SUBSTR(pr_nrtel, 7, 4));
        ELSE
          -- 4733251368           RES: (47) 3325-1378
          pr_nrtelatu := utl_lms.format_message('RES: (%s) %s-%s',
                                                SUBSTR(pr_nrtel, 1, 2),
                                                SUBSTR(pr_nrtel, 3, 4),
                                                SUBSTR(pr_nrtel, 7, 4));
        END IF;
      WHEN 9 THEN
        -- 984461240        MOB: 9 8442-1245
        pr_nrtelatu := utl_lms.format_message('MOB: %s %s-%s',
                                              SUBSTR(pr_nrtel, 1, 1),
                                              SUBSTR(pr_nrtel, 2, 4),
                                              SUBSTR(pr_nrtel, 6, 4));
      WHEN 8 THEN
        IF SUBSTR(pr_nrtel, 1, 1) = '3' THEN
          -- 33251368     RES: 3325-1378
          pr_nrtelatu := utl_lms.format_message('RES: %s-%s',
                                                SUBSTR(pr_nrtel, 1, 4),
                                                SUBSTR(pr_nrtel, 5, 4));
        ELSE
          -- 84461240     MOB: 8442-1245
          pr_nrtelatu := utl_lms.format_message('MOB: %s-%s',
                                                SUBSTR(pr_nrtel, 1, 4),
                                                SUBSTR(pr_nrtel, 5, 4));
        END IF;
      WHEN 5 THEN
        IF SUBSTR(pr_nrtel, 1, 3) = '103' THEN
          -- 10321    ETF: 103+21
          pr_nrtelatu := utl_lms.format_message('ETF: %s+%s',
                                                SUBSTR(pr_nrtel, 1, 3),
                                                SUBSTR(pr_nrtel, 4, 2));
        END IF;
      
        IF SUBSTR(pr_nrtel, 1, 3) = '106' THEN
          -- 10625    ETV: 10625
          pr_nrtelatu := utl_lms.format_message('ETV: %s',
                                                SUBSTR(pr_nrtel, 1, 5));
        END IF;
      WHEN 4 THEN
        -- 1052     ETM: 1052
        pr_nrtelatu := utl_lms.format_message('ETM: %s',
                                              SUBSTR(pr_nrtel, 1, 4));
      
      WHEN 3 THEN
        -- 190  SUP: 190
        pr_nrtelatu := utl_lms.format_message('SUP: %s',
                                              SUBSTR(pr_nrtel, 1, 3));
      ELSE
        pr_nrtelatu := 'N/A ' || pr_nrtel;
    END CASE;
  
    dbms_output.put_line('Saída: ' || pr_nrtelatu);
    dbms_output.put_line(' ');
  
    vr_pos := vr_pos + vr_tam_linha + 1;
  end loop;
END;


run \| edit \| history \| help	0