Solved

Getting Stock details from web to Oracle

Posted on 2013-12-03
5
388 Views
Last Modified: 2013-12-07
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
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39693096
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39693136
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39694558
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39695548
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39703925
Thanks..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question