• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Getting Stock details from web to Oracle

Hello Experts,

I have a table as below:

create table  LIC_STOCK
(	Date	       date
,	 Open	number
,	 High	number
,	 Low	number
,	 Close	number
,	 Settle_Price	number
,	 NO_CONTRACTS	number
,	 Turnover_in_lacs)	number
,	 OI	number ) ;

Open in new window


Now there is a website i.e. http://www.nseindia.com
From there I need to navigate to "Equity Derivatives" and then type
"LIC Housing Finance Limited - LICHSGFIN"

Then check the Historical Data.  I can download the it history data as clicking on "Download this data" . The data need to be inserted into Oracle table i.e. as mentioned above.

So Is there a way I can access website from PLSQL and directly giving the parameter of the company , and the table name to store the data  ?
0
Swadhin Ray
Asked:
Swadhin Ray
  • 2
  • 2
1 Solution
 
awking00Commented:
Right off hand I don't know of a way to use pl/sql to access the website and retrieve the data. However, once the data is downloaded from the site, a csv file can be generated from which you can use sqlloader to load to your table.
0
 
slightwv (䄆 Netminder) Commented:
Try utl_http.request to grab the site.  Parsing it should be a simple XML call.

It can be called from pl/sql as well but see if this from sqlplus will work for you:
select utl_http.request(
'http://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=946&symbol=LICHSGFIN&symbol=LICHSGFIN&instrument=OPTSTK&date=-&segmentLink=17&segmentLink=17'
) from dual;

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here is the error what I am getting after running the SQL :

<HTML><HEAD>
<TITLE>Access Denied</TITLE>
</HEAD><BODY>
<H1>Access Denied</H1>
 
You don't have permission to access "http&#58;&#47;&#47;www&#46;nseindia&#46;com&#47;live&#95;market&#47;dynaContent&#47;live&#95;watch&#47;option&#95;chain&#47;optionKeys&#46;jsp&#63;" on this server.<P>
Reference&#32;&#35;18&#46;af264817&#46;1386132577&#46;3cde9c45
</BODY>
</HTML>

Open in new window



When I used like below:

select utl_http.request(
'http://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=946'||'&'||'symbol=LICHSGFIN'||'&'||'symbol=LICHSGFIN'||'&'||'instrument=OPTSTK'||'&'||'date=-'||'&'||'segmentLink=17'||'&'||'segmentLink=17'
) from dual;

Open in new window


Then I get the below ACL error:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

Open in new window



I am doing this on my local system i.e. has windows XP OS with below Oracle DB details:

Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I've not done a lot with UTL_HTTP.  Just simple calls with utl_http.request to see what it did.

The first error looks like it is coming form the website itself.  They may have something in place on their web servers to keep programs from just scraping their site.

You might play around with trying to simulate a different browser with UTL_HTTP.SET_HEADER.

There are examples in the online docs.

The errors about the ACLS should be easily fixed.  In 11g there are a lot of new security features that are automatically turned on.  You need to tell the database it is OK to talk directly to the outside by manually setting the ACLs.

The online docs talk about how to do this as well.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks..
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now