Solved

Getting Stock details from web to Oracle

Posted on 2013-12-03
5
374 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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 87
Oracle dataguard 5 47
Oracle create type table from existing table%rowtype ? 6 38
Help with Oracle IF statment 5 26
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

820 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