?
Solved

Getting Stock details from web to Oracle

Posted on 2013-12-03
5
Medium Priority
?
403 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

762 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