Solved

Getting Stock details from web to Oracle

Posted on 2013-12-03
5
357 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
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
Thanks..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
UNIX SCP 5 43
oracle query help 36 65
Pfile and SPfile - Oracle 2 33
Need a replacement data type in Oracle 6 24
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now