prompt prompt Creating package YAHOO prompt ====================== prompt create or replace package yahoo is g_sStaticUrl constant varchar2(100) := 'http://download.finance.yahoo.com/d/quotes.csv?s=[SYM]&f=[FIELDS]'; g_sHistUrl constant varchar2(150) := 'http://ichart.finance.yahoo.com/table.csv?s=[SYM]&a=[STM]&b=[STD]&c=[STY]&d=[ENM]&e=[END]&f=[ENY]&g=d&ignore=.csv'; function Query(sRequest in varchar2, outClob in out nocopy clob) return boolean; function ParseRequest(sRequest in varchar2) return varchar2; end yahoo; / prompt prompt Creating package body YAHOO prompt =========================== prompt create or replace package body yahoo is function Query(sRequest in varchar2, outClob in out nocopy clob) return boolean is req utl_http.req; resp utl_http.resp; sTmp varchar2(4000); sUrl varchar2(4000); begin sUrl := ParseRequest(sRequest); if sUrl is null then raise_application_error(-20000,'Request not valid!'); end if; req := utl_http.begin_request(sUrl); resp := utl_http.get_response(req); LOOP utl_http.read_line(resp, sTmp, false); if dbms_lob.getlength(outClob) = 0 then dbms_lob.write(outClob, length(sTmp), 1, sTmp); else dbms_lob.writeappend(outClob, length(sTmp), sTmp); end if; END LOOP; utl_http.end_response(resp); return true; exception when utl_http.end_of_body then utl_http.end_response(resp); -- trim last two characters CRLF dbms_lob.trim(outClob,dbms_lob.getlength(outClob)-2); return true; end; function ParseRequest(sRequest in varchar2) return varchar2 is sUrl varchar2(4000) := null; aRq str_array; begin aRq := Str.Split(sRequest,'|'); if upper(sRequest) like 'GET%' then sUrl := g_sStaticUrl; sUrl := replace(sUrl,'[SYM]',aRq(2)); sUrl := replace(sUrl,'[FIELDS]',aRq(3)); elsif upper(sRequest) like 'HISTORY%' then sUrl := g_sHistUrl; sUrl := replace(sUrl,'[SYM]',aRq(2)); sUrl := replace(sUrl,'[STY]',substr(aRq(3),1,4)); sUrl := replace(sUrl,'[STM]',lpad(to_char(to_number(substr(aRq(3),5,2))-1),2,'0')); sUrl := replace(sUrl,'[STD]',substr(aRq(3),7,2)); sUrl := replace(sUrl,'[ENY]',substr(aRq(4),1,4)); sUrl := replace(sUrl,'[ENM]',lpad(to_char(to_number(substr(aRq(4),5,2))-1),2,'0')); sUrl := replace(sUrl,'[END]',substr(aRq(4),7,2)); end if; return sUrl; exception when others then return null; end; end yahoo; / prompt prompt Creating type YHO_IMPL prompt ====================== prompt create or replace type YHO_IMPL as object ( g_sRequest varchar2(4000), g_oRet sys.anytype, g_nStop number(1), STATIC FUNCTION ODCITableStart(sctx IN OUT YHO_IMPL, sRequest in varchar2) RETURN PLS_INTEGER, STATIC FUNCTION ODCITablePrepare(sctx OUT YHO_IMPL, tf_info in SYS.ODCITabFuncInfo, sRequest in varchar2) RETURN PLS_INTEGER, MEMBER FUNCTION ODCITableFetch(self IN OUT YHO_IMPL, nrows IN NUMBER, rws OUT sys.anydataset) RETURN PLS_INTEGER, MEMBER FUNCTION ODCITableClose(self IN YHO_IMPL) RETURN PLS_INTEGER, STATIC FUNCTION ODCITableDescribe(rtype OUT sys.ANYTYPE, sRequest in varchar2) RETURN PLS_INTEGER ) / prompt prompt Creating type body YHO_IMPL prompt =========================== prompt CREATE OR REPLACE TYPE BODY YHO_IMPL is STATIC FUNCTION ODCITableStart(sctx IN OUT YHO_IMPL, sRequest in varchar2) RETURN PLS_INTEGER IS begin return ODCICONST.SUCCESS; end ODCITableStart; STATIC FUNCTION ODCITablePrepare(sctx OUT YHO_IMPL, tf_info in SYS.ODCITabFuncInfo, sRequest in varchar2) RETURN PLS_INTEGER IS dtype sys.anytype; rtn number; pr pls_integer; sc pls_integer; ln pls_integer; cs pls_integer; cf pls_integer; sName varchar2(100); begin rtn:=tf_info.RetType.GetAttreleminfo(1,pr,sc,ln,cs,cf,dtype,sName); sctx := YHO_IMPL(sRequest, dtype, 0); return ODCICONST.SUCCESS; end ODCITablePrepare; MEMBER FUNCTION ODCITableFetch(self IN OUT YHO_IMPL, nrows IN NUMBER, rws OUT sys.anydataset) RETURN PLS_INTEGER IS oData clob; aRowData str_array; aColData str_array; begin if g_nStop = 1 then -- we are done - exit rws := NULL; return ODCICONST.SUCCESS; end if; -- create temporary clob dbms_lob.createtemporary(oData,true); dbms_lob.open(oData, dbms_lob.lob_readwrite); -- query yahoo if YAHOO.Query(g_sRequest, oData) then if dbms_lob.getlength(oData) > 0 then -- get rows if upper(g_sRequest) like 'GET%' then aRowData := Str.Split(oData, chr(13)||chr(10)); else aRowData := Str.Split(oData, chr(10)); end if; -- start creating the resultset sys.anydataset.BeginCreate(sys.dbms_types.TYPECODE_OBJECT,g_oRet,rws); for nRowIdx in aRowData.first .. aRowData.last loop -- skip csv header (history request) if nRowIdx = aRowData.first and upper(g_sRequest) like 'HISTORY%' then continue; end if; -- add one row after another rws.AddInstance; rws.PieceWise; -- get column data aColData := Str.Split(aRowData(nRowIdx), ','); -- populate recordset column data for nColIdx in aColData.first .. aColData.last loop rws.SetVarchar2(trim(both '"' from aColData(nColIdx)),(case when nColIdx=(aColData.last) then true else false end)); end loop; end loop; -- creation of recordset done rws.EndCreate; end if; end if; -- destroy temporary clob dbms_lob.close(oData); dbms_lob.freetemporary(oData); -- set the exit flag g_nStop := 1; return ODCICONST.SUCCESS; end ODCITableFetch; MEMBER FUNCTION ODCITableClose(self IN YHO_IMPL) RETURN PLS_INTEGER IS begin return ODCICONST.SUCCESS; end ODCITableClose; STATIC FUNCTION ODCITableDescribe(rtype OUT sys.ANYTYPE, sRequest in varchar2) RETURN PLS_INTEGER IS aFields str_array := str_array(); nIdx pls_integer; i pls_integer; oDef sys.anytype; oDefRS sys.anytype; sFields varchar2(1000) := null; aRq str_array; begin if upper(sRequest) like 'GET%' then -- parse the fields value and define the type of the resultset aRq := Str.Split(sRequest,'|'); sFields := aRq(3); if sFields is not null then i := 2; nIdx := 1; loop if i > length(sFields) then aFields.extend(); aFields(aFields.last) := substr(sFields,nIdx); exit; end if; if ascii(substr(sFields,i,1)) not between 48 and 57 then aFields.extend(); aFields(aFields.last) := substr(sFields,nIdx,i-nIdx); nIdx := i; end if; i := i + 1; end loop; end if; else -- history request is easy because it is a fixed/static resultset -- DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ CLOSE aFields.Extend; aFields(aFields.Last) := 'Date'; aFields.Extend; aFields(aFields.Last) := 'Open'; aFields.Extend; aFields(aFields.Last) := 'High'; aFields.Extend; aFields(aFields.Last) := 'Low'; aFields.Extend; aFields(aFields.Last) := 'Close'; aFields.Extend; aFields(aFields.Last) := 'Volume'; aFields.Extend; aFields(aFields.Last) := 'AdjClose'; end if; -- create the type (row) sys.anytype.BeginCreate(sys.DBMS_TYPES.TYPECODE_OBJECT,oDef); for nIdx in 1 .. aFields.count loop oDef.AddAttr(upper(aFields(nIdx)), sys.DBMS_TYPES.TYPECODE_VARCHAR2, null, null, 300, null, null); end loop; oDef.EndCreate; -- create the resultset sys.anytype.BeginCreate(DBMS_TYPES.TYPECODE_NAMEDCOLLECTION, oDefRS); oDefRS.SetInfo(null,null,null,null,null,oDef,DBMS_TYPES.TYPECODE_OBJECT,0); oDefRS.EndCreate; -- pass it back rtype := oDefRS; return ODCICONST.SUCCESS; end ODCITableDescribe; end; / prompt prompt Creating function FINYHO prompt ======================== prompt create or replace function finyho(sRequest in varchar2) return anydataset pipelined using YHO_IMPL; /