Weblog Eric Kurzhals

Using chunked transfer with pl/sql utl_http.write_text

21. März 2012 · Keine Kommentare

Currently I’m working on an syncing project between an oracle database and an webservice (ms dynamics nav). For that, I run into trouble with the max length of data utl_http.write_text sends to the webservice.

To go around, you can chunk your postdata with that little snipped:

CREATE OR REPLACE PROCEDURE CHUNKED AS http_req utl_http.req; http_resp utl_http.resp; l_response_env varchar2(32767); l_data clob;


l_chunkStart number := 1;

l_chunkData varchar2(200);

l_chunkLength number := 200;
BEGIN
l_data := 'fill with some sample piece of text';

http_req := utl_http.begin_request(url => 'http://example.com', method => 'POST');

utl_http.set_header (http_req, 'Content-Length', length(l_data));

utl_http.set_header (http_req, 'Content-Type', 'text/xml;charset=UTF-8');

utl_http.set_header (http_req, 'Transfer-Encoding', 'chunked');
loop

l_chunkData := null;

l_chunkData := substr(l_data, l_chunkStart, l_chunkLength);

utl_http.write_text(http_req, l_chunkData);

if (length(l_chunkData) < l_chunkLength) then exit; end if;

l_chunkStart := l_chunkStart + l_chunkLength;

end loop;

NULL; END CHUNKED;

→ Keine KommentareTags: code snippets · Oracle · pl/sql

Get column name and value from select query in pl/sql

21. März 2012 · Keine Kommentare

In some situations its very important to get the column name with the column value. For example I need the name to generate dynamicly an XML Document from my pl/sql script.

For Example:
<xml><column_name>column_value</column_name> </xml>

For those situations Oracle provides the very powerfull package ‘dbms_sql‘.

CREATE OR REPLACE PROCEDURE getcolumn_and_value AS v_cursor integer default dbms_sql.open_cursor; v_cvalue varchar2(4000); v_status integer; v_cname dbms_sql.desc_tab; v_ccount number; v_xmldoc clob; BEGIN

v_xmldoc := ‘<xml>’;
dbms_sql.parse(v_cursor, p_query, dbms_sql.native);

dbms_sql.describe_columns( v_cursor, v_ccount, v_cname);

for i in 1 .. v_ccount loop
dbms_sql.define_column(v_cursor, i, v_cvalue, 4000);
end loop;

v_status := dbms_sql.execute(v_cursor);
while ( dbms_sql.fetch_rows(v_cursor) > 0 ) loop
for i in 1 .. v_ccount loop
dbms_sql.column_value( v_cursor, i, v_cvalue );
v_xmldoc := v_xmldoc || ‘<’||lower(v_cname(i).col_name)||’>’||v_cvalue||’</’||lower(v_cname(i).col_name)||’>’;
end loop;
end loop;

v_xmldoc := v_xmldoc || ‘</xml>’;
END getcolumn_and_value;

With this snipped you get also the columns names of empty columns.

→ Keine KommentareTags: code snippets · Oracle · pl/sql