Querying Twitter

A slightly modified code basing on the API.

create or replace
FUNCTION get_twitter_timeline(in_user IN VARCHAR2,   in_since_id in varchar2)
RETURN XMLTYPE
IS
http_req utl_http.req;
http_resp utl_http.resp;
t_update_send VARCHAR2(200);
res_value VARCHAR2(4000);
XML_RETURN CLOB;
BEGIN
  t_update_send := '--head';
  --utl_http.set_proxy('http://www-yourproxy.com:80'); --If you need to specify a proxy use this.
  http_req := utl_http.begin_request( 'http://api.twitter.com/1/statuses/user_timeline.xml?screen_name='||in_user||
                                       case when in_since_id is not null then '&since_id='||in_since_id else '' end,
                                       'GET',utl_http.http_version_1_1);
  utl_http.set_response_error_check(TRUE);
  utl_http.set_detailed_excp_support(TRUE);
  utl_http.set_body_charset(http_req,   'UTF-8');
  utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
  utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
  utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
  utl_http.set_transfer_timeout(to_char('60'));
  --utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
  utl_http.write_text(http_req,   t_update_send);
  http_resp := utl_http.get_response(http_req);
  BEGIN
    WHILE 1 = 1 LOOP
          utl_http.read_line(http_resp,   res_value,   TRUE);
          XML_RETURN := XML_RETURN || res_value;
    END LOOP;
 EXCEPTION
 WHEN utl_http.end_of_body THEN
      NULL;
 END;
 utl_http.end_response(http_resp);
 return XMLTYPE(XML_RETURN);
EXCEPTION
WHEN others THEN
XML_RETURN := sqlerrm;
RAISE;
END get_twitter_timeline;



SELECT  EXTRACTVALUE(VALUE(twitter), '/status/id')  AS ID
      , EXTRACTVALUE(VALUE(twitter), '/status/user/name')  AS NAME
      , EXTRACTVALUE(VALUE(twitter), '/status/text') AS TEXT
      /*
      , EXTRACTVALUE(VALUE(twitter), '/status/created_at') AS CREATED_AT
      , EXTRACTVALUE(VALUE(twitter), '/status/source') AS SOURCE
      , EXTRACTVALUE(VALUE(twitter), '/status/user/screen_name') AS SCREEN_NAME
      , EXTRACTVALUE(VALUE(twitter), '/status/user/profile_image_url') AS PROFILE_IMAGE_URL
      */
FROM
TABLE(XMLSEQUENCE(EXTRACT(get_twitter_timeline('sanya_s',null), '/statuses/*'))) twitter

ID                NAME     TEXT
----------------- -------- --------------------------------------------------------------------------------
7742984103661568  sanya_s  @achernomorov свой hosts уже проверил :))) бггг...
7732539288457216  sanya_s  @achernomorov а это у 1 чела если 150, то скоко всего - хз...
7723591521013760  sanya_s  Возникла мысль задействовать либу twitter4j.org из plsql for fun, забавно, оказы
7713741139345408  sanya_s  http://habrahabr.ru/blogs/eCommerce/108720/ c:\WINDOWS\system32\drivers\etc\host
7540085780119552  sanya_s  @achernomorov а я в разжумьях еще тоже...
7481829594501120  sanya_s  @achernomorov А странно, что тебя еще не. 25 декабря.
7463922474942464  sanya_s  @achernomorov а я вылез пару раз седня, посмотрел, тебя нет, но и залез обратно
7463408383303682  sanya_s  @achernomorov а к коню на игого, как, собираешься?
7396953021222912  sanya_s  Дощщ http://twitpic.com/39pnvx
7368795572150272  sanya_s  @achernomorov да лабаю код plsql понемногу :)
7358791406325760  sanya_s  @achernomorov работы валом? что лабаешь? :)
7353706211180544  sanya_s  Моя система целей и средств нуждается в рефакторинге =) меня такое положение под
7167182450458624  sanya_s  @achernomorov Махмеде, кстати, хотел спросить, так кленовый сироп на новый год б
7065158480302080  sanya_s  @sanya_s @achernomorov Мехмеде, смотри, гарные вакансии, не хошь? http://www.i-g
6771922201214976  sanya_s  Угадайте, от кого :) http://twitpic.com/397x84
6742195512020993  sanya_s  @achernomorov да, я второй уже тоже увидел :) а вот эти с собакой не отображаютс
6741863260233728  sanya_s  @achernomorov да хз, вроде собираюсь, да по вечерам спать охота становится :)
6731226702413824  sanya_s  Пока не сработало, проверка еще раз...
6730983298564097  sanya_s  Жду воспроизведения бага на боевом стенде от тестеров :) пока прицепил твитерок
5605022209417216  sanya_s  @achernomorov офигеть, дайте 2 :) а че, круто, а на скоко?

20 rows selected

oradebug skdsttpcs

oradebug skdsttpcs

Converting hex address from "input file" to function names in "output files".

**** input.txt********
0x4000001
**** input.txt********

SQL>oradebug skdsttpcs input.txt output.txt
Statement processed.

**** output.txt *******
0x4000001 _kidigen_signature()+789
**** output.txt *******

skip locked

select * from ... for update skip locked

Oracle Auto execute taks (KTSJ). Step 1.

i am finding the list of Oracle 11g auto-execute tasks. The tech using for scheduling jobs for SMCO (space management coordinator ).

sqlplus /nolog
SQL>connect / as sysdba
...
SQL>oradebug setospid ...
...
SQL>oradebug call ktsj_dump_tasks
...
looking in trace file ... Not much info, but something ;)

LOBs, NOLOGGING, control file and event 10359

Oracle 11.1.0.7 Std. Linux.

In my DB - LOBs are basicfile-s and NOLOGGING mode. During workload - too much time in
control file sequential read , control file parallel write and CF - enq contetion waits.

Let's ask BOM!

and Bom said...
Don't try to move them to securefile-s or ORA-00600 [ktslghb-1] began parting in your alert.log. Stay with basicfile-s and set event 10359.

From Metalink Note 1058851.6

event="10359 trace name context forever, level 1".

Explanation:
============

If event 10359 is set to level 1, update of the control file with invalidation
redo is stopped. This will not affect any recovery, but note that recovery
manager reports will be stale.

dbms_xmldom. Memory leak. createXMLCharacterInputStream, closeCharacterInputStream

Oracle 11.1.0.7 Std. Linux.

I have such a code.

procedure ...(a_node dbms_xmldom.domnode,a_text in out nocopy CLOB)
...
as
BEGIN
dt2 := dbms_xmldom.getChildNodes(a_node);
dt3 := dbms_xmldom.item(dt2,0);


stream_id := dbms_xmldom.createXMLCharacterInputStream(dt3.id);
...
LOOP
...
dbms_xmldom.readCharacterInputStream (stream_id,
buffer,
num_chars,
false);
...
END LOOP;
dbms_xmldom.closeCharacterInputStream(stream_id);
dbms_xmldom.freeNode(dt3);

END;

Under havy workload my db instance crashes, - being killed by Linux VM OOM killer. Yes, - Out of memory.

Let's Ask Bom!

Memory leaks under tags qmxdsjniAlloc(68 and 28 bytes) and qmxCreateMemCtx(20 bytes) - Yes ?

dbms_xmldom.createXMLCharacterInputStream (creation call)
 qmxdsplsnode_createXMLCIS (C)
  {
...
 qmxdsGetPullNodeAsCharacterStream(...)
 {
      ...
     OraStreamInit(...) ---> memory allocation inside!!!
     OraStreamOpen(...)
 }
  }

dbms_xmldom.closeCharacterInputStream (free call)
qmxdsplsnode_closeStream (C)
{
OraStreamClose(...)
}

Where is OraStreamTerm ?!!! ;))
i patched qmxdsplsnode_closeStream - add OraStreamTerm call.
Now it like watch ;)

dbms_xmldom. Possible Memory leak.LpxInitEncoded0

Oracle 11.1.0.7 Std Linux.

And Bom Said...
While processing XMLType code flows thr LpxInitEncoded0 func.
This function is extended wrapper for XmlCreateNew func with settupping MemManagerFuncs structure as task.

LpxInitEncoded0(...)
{

MemManagerFuncs* p1;
if(...)
 {
     p1 = OraMemInit(...,qmxsaxAllocMem, qmxSaxFreeMem);
 }
 else
 {
     //My DB now using this way
   p1 = 0;
 }
 XmlCreateNew(...,p1,...);
...
}

memory management funcs recived thr p1 would be used for all allocs and frees during parsing XMLType.
Let's detail it.

qmxsaxAllocMem()
{
allocs memory from Oracle heaps
}

qmxSaxFreeMem
{
empty! ;))
}

You saying: "Baby tt's simple - garbage collector". May be, but why Oracle LPX fanatically calls it ?
Using qmxsaxAllocMem and qmxSaxFreeMem funcs inside LPX have another limitation, Oracle could not initialize XMLType for documents with tags(nodes) above 32M(Limitation of Oracle heaps).

I solve both troubles by calling XmlCreateNew with zero as address of MemManagerFuncs struct. In such case XmlCreateNew initializing it by self using simple malloc and free.
Two rabbits killed - no leaks and welcome big nodes.