oracle - PLSQL / DBMS_Out - 如何返回特定值？

Question

BD：甲骨文 11g r2

对我来说是一个巨大的挑战。

如何在查询（html）中返回特定值，输出位于：dbms_output

DBMS_输出

...
<td colspan = "5"> <b> <h3> <center> Basic Consultation of the ICMS Registry of Bahia </center> </h3> </b> </td>
...

我只想退回以下金额。

预期结果：

Consulta Básica ao Cadastro do ICMS da Bahia

Como retornar somete este valor na consulta?

---

查询PLSQL/Mateus Ribeiro.sql

declare
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'http://www.sefaz.ba.gov.br/scripts/cadastro/cadastroBa/result.asp';
name varchar2(4000);
DADOS varchar2(4000); 
content varchar2(4000) := 'CGC=13504675000110&B1=CNPJ++-%3E&CPF=&IE=';

begin

req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
--utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
utl_http.set_header(req, 'content-type', 'application/x-www-form-urlencoded'); 
utl_http.set_header(req, 'Content-Length', length(content));

utl_http.write_text(req, content);

res := utl_http.get_response(req);

    begin
    loop
    utl_http.read_text(res, DADOS);
    dbms_output.put_line(DADOS);

    end loop;
    utl_http.end_response(res); 
    exception
    when utl_http.end_of_body then
    utl_http.end_response(res);
    end;
end;

标签： oracleplsql

Answer 1

如果您设法提取该字符串，则可以对其应用SUBSTR+INSTR组合，例如

SQL> with test (col) as
  2    (select '<td colspan = "5"> <b> <h3> <center> Basic Consultation of the ICMS Registry of Bahia </center> </h3> </b> </td>' from dual)
  3  select substr(col, instr(col, '<center>') + 9,
  4                     instr(col, '</center>') - instr(col, '<center>') - 10
  5               ) result
  6  from test;

RESULT
------------------------------------------------
Basic Consultation of the ICMS Registry of Bahia

SQL>