1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl/Sql Code for to fetch Live cricket Scores

Discussion in 'SQL PL/SQL' started by sambuduk, Dec 8, 2014.

  1. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,


    Code (SQL):
     

    DECLARE
     l_http_request   utl_http.req;
     l_http_response  utl_http.resp;
     l_clob           CLOB;
     l_text           VARCHAR2(32767);
     v_count          NUMBER          := 1;
     v_score          VARCHAR2(100);
     v_score_my_team  VARCHAR2(1000)  := NULL;
     v_my_team        VARCHAR2(100)   := 'India'; --'ALL' --The team whose score you want or 'ALL' for all ongoing matches
    BEGIN
     -- Initialize the CLOB.
     dbms_lob.createtemporary(l_clob, FALSE);
     
     -- Make a HTTP request and get the response.
     -- We are using the RSS feed from Cricinfo
     l_http_request  := utl_http.begin_request('http://rss.cricinfo.com/rss/livescores.xml');
     l_http_response := utl_http.get_response(l_http_request);
     
     -- Copy the response into the CLOB.
     BEGIN
       LOOP
         utl_http.read_text(l_http_response, l_text, 32767);
         dbms_lob.writeappend (l_clob, LENGTH(l_text), l_text);
       END LOOP;
     EXCEPTION
       WHEN utl_http.end_of_body THEN
         utl_http.end_response(l_http_response);
     END;
     
      -- Convert the CLOB to XMLType and extract scores the from the XML
      WHILE XMLType(l_clob).existsNode('/rss/channel/item[' || v_count || ']') = 1
      LOOP
        v_score := XMLType(l_clob).EXTRACT('/rss/channel/item[' || v_count || ']/title/text()').getStringVal();
        --Store all scores, if 'ALL' is specified
        IF UPPER(v_my_team) = 'ALL' THEN
          --If this is the first value, do not use a comma
          IF v_score_my_team IS NULL THEN
            v_score_my_team := v_score;
          ELSE
            v_score_my_team := v_score_my_team||', '||v_score;
          END IF;
        ELSE
          -- If the score is for your team, store it in a variable
          -- Providing a value of 'Australia' to the variable v_my_team would fetch scores involving both Australia and Australia A.
          -- We will display all matching scores for such cases.
          IF UPPER(v_score) LIKE '%'||UPPER(v_my_team)||'%' THEN
            --If this is the first value, do not use a comma
            IF v_score_my_team IS NULL THEN
              v_score_my_team := v_score;
            ELSE
              v_score_my_team := v_score_my_team||', '||v_score;
            END IF;
          END IF;
        END IF;
     
          v_count := v_count + 1;
      END LOOP;
      -- Display comma-separated scores
      dbms_output.put_line(NVL(v_score_my_team, 'Sorry, there are no ongoing matches involving '||INITCAP(v_my_team)));
     
    EXCEPTION
     WHEN OTHERS THEN
       utl_http.end_response(l_http_response);
       -- Release the resources associated with the temporary LOB.
       dbms_lob.freetemporary(l_clob);
       dbms_output.put_line (utl_http.get_detailed_sqlerrm);
       dbms_output.put_line (SQLERRM);
    END;


     

    got it from http://oracleappsnotes.wordpress.co...tabase-to-fetch-cricket-scores-through-plsql/
     
    jagadekara likes this.
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oh!

    Thanks Samba. I am able to see the score now at office