Blog do Marcio Portes

Autor: Vander Emiro Muniz
A Dúvida

Márcio,

problema, como eu faço pra converter um blob em uma tabela ?

tenho um arquivo que foi inserido lá ... ele está delimitado o registro com
\n e o os campos com ';'

eu sei que precisa de uma procedure, mas não sei como implentar. Dá um help
...

valeu.

Já conversei em off com o Vander para entender a necessidade e o porque deles não usarem o sql loader ou external table, que seriam mais apropriados para o caso.

Ele também me enviou um export da tabela que continha o blob.
Após o import do exemplo, fiz uma procedure onde primeiro popula um array com registros delimitados por \n (chr10) e depois faz um looping varrendo esse array e quebrando mais uma vez, porém agora o delimitador é o ';', ficou mais ou menos assim.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace type array as table of varchar2 (1000);
  2  /

Type created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> drop table customer purge;

Table dropped.

ops$marcio@ORA10G> create table customer
  2  (
  3   year          number(4),
  4   month         number(2),
  5   country_code  varchar2(2),
  6   customer_code number,
  7   customer_name varchar2(60)
  8  )
  9  /

Table created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure p
  2  as
  3      l_string        clob;
  4      l_data          array := array();
  5      l_data_parsed   array := array();
  6      n               number;
  7
  8      procedure f ( p_string in varchar2 )
  9      as
 10         l_str      varchar2(1000);
 11      begin
 12         l_str := p_string || ';';
 13         loop
 14            exit when l_str is null;
 15            n := instr( l_str, ';' );
 16            l_data_parsed.extend;
 17            l_data_parsed(l_data_parsed.count) :=
 18                ltrim( rtrim (substr( l_str, 1, n-1 ) ) ) ;
 19            l_str := substr( l_str, n+1 );
 20         end loop;
 21      end;
 22  begin
 23    select utl_raw.cast_to_varchar2(blobcolumn) || chr(10) into l_string
 24      from blobtable;
 25    loop
 26        exit when dbms_lob.getlength(l_string) = 0;
 27        n := instr( l_string, chr(10) );
 28        l_data.extend;
 29        l_data(l_data.count) := substr( l_string, 1, n-1 ) ;
 30        l_string := substr( l_string, n+1 );
 31    end loop;
 32    for i in 2 .. l_data.count
 33    loop
 34        f( l_data(i) );
 35        insert into customer
 36        values ( l_data_parsed(1), l_data_parsed(2),
 37                 l_data_parsed(3), l_data_parsed(4), l_data_parsed(5) );
 38        l_data_parsed := array();
 39    end loop;
 40  end;
 41  /

Procedure created.

ops$marcio@ORA10G> show error
No errors.

O que veio no campo blob.

ops$marcio@ORA10G> select utl_raw.cast_to_varchar2(blobcolumn) from blobtable;

UTL_RAW.CAST_TO_VARCHAR2(BLOBCOLUMN)
-------------------------------------------------------------------------------------------------------------
year;month;country_code;customer_code;customer_name;customer_type;adress1;adress2;adress3;state_code;zip_code
ntry_name;distribution_channel;GDS_web;Countries_V2_1.country_name;resinter_code;hotel_name;SommeDenbr_rn;Som
2004;9;AR;55011283;PASSERINI VIAJES S.R.L.;AGV;AV. RAFAEL NUNEZ 4731;P. BAJA;;AR;5009;CORDOBA;ARGENTINA;Autom
;IBIS PORTO ALEGRE AEROPORTO;1;22,29119639
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;BRAZIL;2992;NOVOTEL SAO PAULO CENTER NORTE;3;187,0767494
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;GERMANY;1306;DORINT SOFITEL AM DOM CGN;3;693
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;GERMANY;5360;DORINT SOFITEL PALLAS UWE;6;1218
2004;9;AR;55500174;SECONTUR S.R.L.;AGV;SUIPACHA NO.207;PISO 3-OFICINA 306;;AR;1008;BUENOS AIRES/BUENOS AIRES;
AMA;SPAIN;1320;SOFITEL MADRID PLAZA DE ESPA╤A;6;795
2004;9;AR;55500222;FIRENZE VIAJES S.R.L.;AGV;CORRIENTES NO.3247;PISO 2-LOCAL 3030;;AR;1004;BUENOS AIRES/BUENO
mations;AMA;SPAIN;1606;SOFITEL MADRID AIRPORT;1;159
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
BRAZIL;5021;IBIS GUARULHOS;1;14,95485327
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
ECUADOR;5111;GRAND HOTEL MERCURE QUITO;1;37,87573535
2004;9;AR;55500432;GUSTAVO ESUSY-D.TERZE;AGV;CORDOBA NO. 657;PISO 5-C;;AR;1054;BUENOS AIRES/BUENOS AIRES;ARGE
FRANCE;1561;IBIS BORDEAUX SAINT-JEAN;3;172
2004;9;AR;55500620;VIAJES CLEVELAND S.A.;AGV;CORRIENTES NO.456;PISO 6-OFICINA 65-EDIFIC.SAFICO;;AR;1366;BUENO

1 row selected.

ops$marcio@ORA10G>
ops$marcio@ORA10G> select * from customer;

no rows selected

ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p

PL/SQL procedure successfully completed.

E como ficou a tabela customer depois do processo.

ops$marcio@ORA10G> select * from customer;

         YEAR         MONTH CO CUSTOMER_CODE CUSTOMER_NAME
------------- ------------- -- ------------- ------------------------------------------------------------
         2004             9 AR      55011283 PASSERINI VIAJES S.R.L.
         2004             9 AR      55500174 SECONTUR S.R.L.
         2004             9 AR      55500174 SECONTUR S.R.L.
         2004             9 AR      55500174 SECONTUR S.R.L.
         2004             9 AR      55500174 SECONTUR S.R.L.
         2004             9 AR      55500222 FIRENZE VIAJES S.R.L.
         2004             9 AR      55500432 GUSTAVO ESUSY-D.TERZE
         2004             9 AR      55500432 GUSTAVO ESUSY-D.TERZE
         2004             9 AR      55500432 GUSTAVO ESUSY-D.TERZE
         2004             9 AR      55500620 VIAJES CLEVELAND S.A.

10 rows selected.

ops$marcio@ORA10G>
ops$marcio@ORA10G> set echo off
ops$marcio@ORA10G>

Claro que no final das contas eu não fiz para a tabela customer toda, o intento aqui é dar uma idéia de como isso pode ser feito.

Porém Vander, a melhor dica, no seu caso, é: faça o programa ASP popular diretamente uma tabela gtt já parseando como fiz no exemplo, depois voce faz uma agenda (dbms_job) para o insert definitivo, caso necessite tratar algo.

Abraços,