Web scrapping using Jsoup

Download latest jsoup jar file (Download Link).

Compile code with appropriate class path value, like

javac -cp "C:\jsoup-1.7.1.jar"  "TestClass.java"

java  -cp  "C:\jsoup-1.7.1.jar"  TestClass

Simple Example using Jsoup to connect to server using login credentials and then retrieving specific page.

[sourcecode language="java"]

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.Connection;
import java.io.IOException;
import org.jsoup.Connection.Method;
import java.util.HashMap;
import java.util.Map;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;

public class TestClass
{
public static void main(String args[]) throws IOException
{

Document doc = Jsoup.connect("<URL>").get();

Elements viewState = doc.select("input[name=__VIEWSTATE");
Elements eventValidation = doc.select("input[name=__EVENTVALIDATION]");

Map<String,String> allFields = new HashMap<String,String>();
allFields.put("__VIEWSTATE", viewState.val());
allFields.put("__EVENTVALIDATION", eventValidation.val());
allFields.put("txtLogin", "<USERNAME>");
allFields.put("txtPassword",   "<PASSWORD>");
allFields.put("butSubmit",   "Sign In");

System.out.println(allFields);

Connection.Response res = Jsoup.connect("<URL2>")
.userAgent("Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.21 (KHTML, like Gecko) Chrome/19.0.1042.0 Safari/535.21")
.data(allFields)
.method(Method.POST).
execute();

String sessionId = res.cookie("<COOKIENAME>");

System.out.println(sessionId);

Document doc2  = Jsoup.connect("URL3")
.cookie("ASP.NET_SessionId", sessionId)
.timeout(0)
.get();

System.out.println(doc2.html());

}
}

[/sourcecode]

HTML output in Oracle PL SQL

Requirement : Generating HTML output using Oracle PL SQL

Solution :
Thomas Kyte shared below piece of code on his blog for generating html output from refcursor.

[sourcecode language="sql"]

CREATE OR REPLACE FUNCTION apps.fncrefcursor2html (rf sys_refcursor)
RETURN CLOB
IS
lretval       CLOB;
lhtmloutput   XMLTYPE;
lxsl          CLOB;
lxmldata      XMLTYPE;
lcontext      DBMS_XMLGEN.ctxhandle;
BEGIN
-- get a handle on the ref cursor --
lcontext := DBMS_XMLGEN.newcontext (rf);
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
DBMS_XMLGEN.setnullhandling (lcontext, 1);
-- create XML from ref cursor --
lxmldata := DBMS_XMLGEN.getxmltype (lcontext, DBMS_XMLGEN.NONE);

IF lxmldata IS NOT NULL
THEN
-- this is a generic XSL for Oracle's default XML row and rowset tags --
-- " " is a non-breaking space --
lxsl := lxsl || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
lxsl :=
lxsl
|| q'[<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
lxsl := lxsl || q'[ <xsl:output method="html"/>]';
lxsl := lxsl || q'[ <xsl:template match="/">]';
lxsl := lxsl || q'[   <table >]';
lxsl := lxsl || q'[     <tr >]';
lxsl := lxsl || q'[      <xsl:for-each select="/ROWSET/ROW[1]/*">]';
lxsl := lxsl || q'[       <th><xsl:value-of select="name()"/></th>]';
lxsl := lxsl || q'[      </xsl:for-each>]';
lxsl := lxsl || q'[     </tr>]';
lxsl := lxsl || q'[     <xsl:for-each select="/ROWSET/*">]';
lxsl := lxsl || q'[      <tr>]';
lxsl := lxsl || q'[       <xsl:for-each select="./*">]';
lxsl := lxsl || q'[        <td><xsl:value-of select="text()"/> </td>]';
lxsl := lxsl || q'[       </xsl:for-each>]';
lxsl := lxsl || q'[      </tr>]';
lxsl := lxsl || q'[     </xsl:for-each>]';
lxsl := lxsl || q'[   </table>]';
lxsl := lxsl || q'[ </xsl:template>]';
lxsl := lxsl || q'[</xsl:stylesheet>]';
-- XSL transformation to convert XML to HTML --
lhtmloutput := lxmldata.transform (XMLTYPE (lxsl));
-- convert XMLType to Clob --
lretval := lhtmloutput.getclobval ();
RETURN lretval;
ELSE
RETURN NULL;
END IF;
END fncrefcursor2html;
[/sourcecode]

Simple package using above function to write table output to Oracle Apps output file.

[sourcecode language="sql"]
CREATE OR REPLACE PACKAGE  apps.write_html_pkg
AS
PROCEDURE write_clob (l_clob_data CLOB);
PROCEDURE write_html_prc (o_err_msg OUT VARCHAR2, o_err_code OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY apps.write_html_pkg
AS
PROCEDURE write_clob (l_clob_data CLOB)
AS
l_char_data      VARCHAR2 (32767);
v_clob_len       NUMBER;
v_chunk_length   NUMBER;
v_iterations     NUMBER;
v_clob_temp      CLOB;
BEGIN
IF l_clob_data IS NOT NULL
THEN
v_chunk_length := 32767;
v_clob_len := DBMS_LOB.getlength (l_clob_data);
v_iterations := CEIL (v_clob_len / v_chunk_length);

-- v_chunk_length is 32767 and v_clob_length is length of the XML data stored inthe clob variable
FOR i IN 0 .. v_iterations
LOOP
v_clob_temp :=
DBMS_LOB.SUBSTR (l_clob_data,
v_chunk_length,
(i * v_chunk_length) + 1
);
fnd_file.put_line (fnd_file.output, v_clob_temp);
END LOOP;
ELSE
NULL;
END IF;
END;

PROCEDURE OUT (p_text VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.output, p_text);
END;

PROCEDURE write_html_prc (o_err_msg OUT VARCHAR2, o_err_code OUT NUMBER)
IS
l_cursor      sys_refcursor;
l_clob_data   CLOB;
l_style       VARCHAR2 (32767);
BEGIN</pre>
l_style :=
         q'[<style type="text/css">
         table
{
border-collapse:collapse;
}

table td, table th {
    border:1px solid grey;
    border:1px solid #98bf21;
    padding:3px 7px 2px 7px;
}

table th
{

text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#A7C942;
color:#fff;
}

  </style>]';
      OUT (q'[ <html>]');
      OUT (q'[ <head>]');
      OUT (l_style);
      OUT (q'[ </head>]');
      OUT (q'[  <body>]');
<pre>

--------------------------------write table-----------------------------
OPEN l_cursor FOR
SELECT *
FROM scott.emp;

l_clob_data := fncrefcursor2html (l_cursor);
write_clob (l_clob_data);
--------------------------------END :write table-----------------------------
OUT (q'[  </body>]');
OUT (q'[ </html>]');
END;
END write_html_pkg;
/
[/sourcecode]

Output Screenshot

Sudoku Solver (Easy Level) in Oracle PL SQL

Data structures used

Sudoku grid consist of this 9x9 cells. Each cell in grid has actual value(decided value) and a list of possible values.

Cell is represented by PL/SQL Object, consisting of a

• Number type which holds actual value, and


• Nested table which holds possible values for cell.

[sourcecode language="sql"]
--Cell
CREATE TYPE cell AS OBJECT (
actual_value number,
poss_values possible_values
)

--create nested table of possible values
CREATE TYPE possible_values AS TABLE OF number;
[/sourcecode]
[sourcecode language="sql"]
--Sudoku grid of 9 columns
CREATE TABLE xx_test (C1 cell,C2 cell,C3 cell,C4 cell,C5 cell,C6 cell,C7 cell,C8 cell,C9 cell,id number)
NESTED TABLE C1.poss_values STORE AS possible_val_tab1
NESTED TABLE C2.poss_values STORE AS possible_val_tab2
NESTED TABLE C3.poss_values STORE AS possible_val_tab3
NESTED TABLE C4.poss_values STORE AS possible_val_tab4
NESTED TABLE C5.poss_values STORE AS possible_val_tab5
NESTED TABLE C6.poss_values STORE AS possible_val_tab6
NESTED TABLE C7.poss_values STORE AS possible_val_tab7
NESTED TABLE C8.poss_values STORE AS possible_val_tab8
NESTED TABLE C9.poss_values STORE AS possible_val_tab9

--set row number for
update xx_test set id = rownum

[/sourcecode]

A block is defined as group of 9 cells like
Block B1 consists of cells
11 12 13
21 22 23
31 32 33

Update Rule for Possible values of cell

Possible values for a cell is calculated by removing actual value that is present in other cells in that particular row, column and block from list of possible values of the cell.

If a value is present in a row/column/block then it should not appear in any other cell in the same row/column/block.

For example, for cell 22, possible value is calculated by removing actual values of all cells in row R2, column C2 and Block B1 from Varray 1..9(all possible values)

Update Rule for Actual Value of a cell
1.Cell containing only one possible value.
2.Value possible only in one cell in a row, column and block

Pseudo Code

For all cells
Check If actual value is null then
check list of possible value for cell. If only one value possible, then update cell with this value.
For all values 1..9 check if the value is not possible in other cells of that particular row column or block then update cell with that value.

Create .CSV file in Oralce PL SQL

Requirement: To generate delimited output of SQL query using Oracle PL SQL

Solution :

Please find below complete package code.

This package can be used to write delimited output to DBMS output, Oracle Apps Log/Output or File Directory.

Usage

Call the procedure xxtk_generate_csv with mandatory parameter values.

PROCEDURE xxtk_generate_csv (

p_query VARCHAR2,

p_delimiter varchar2,

p_file_handle number,

p_dir varchar2 DEFAULT NULL,

p_filename varchar2 DEFAULT NULL,

p_cur_qry VARCHAR2 DEFAULT 'Q'

) ;

First parameter is SQL query as Varchar2 data type, second is delimiter(tab or comma or other) and third is file handle i.e where to write output.

Example 1

Consider below query

select a "col1", b from (select 1 a, level b from dual connect by level < 4)

Comma separated Output of this query is required

[sourcecode language="sql"]
DECLARE
--q operator is available in 11i to escape quotes withing string
l_qry varchar2(1000):=q'[select a "col1", b from (select 1 a, level b from dual connect by level < 4)]';
BEGIN
--write result of l_qry as comma separated values to dbms output
xxtk_generate_csv_pkg.xxtk_generate_csv
(l_qry,',',xxtk_generate_csv_pkg.write_to_db_out);
END;
[/sourcecode]

Q operator is used to escape quote pairs appearing in data. Otherwise, we need to escape each quote separately.

DBMS Output

"col1","B"

1,1

1,2

1,3

Example 2

4 and 5 parameter are used to specify directory location and file name where to write output.

6 parameter p_cur_qry is used when passing cursor handle.

When input parameters are required for query either we can

• pass query text(first parameter)  with concatenated variable values (like where select ....where dept_no='||p_dept_no||'..' or


• pass parsed query with bind variable and send the parsed query handle (following example)

Second option is better because using bind variable is always preferable instead of concatenating variables in SQL query.

In case when SQL query is already parsed  with bind variable values.

[sourcecode language="sql"]

DECLARE
cursor_name   INTEGER         DEFAULT DBMS_SQL.open_cursor;
l_qry varchar2(1000):=q'[select *  from scott.emp where deptno =:p_dept_no and hiredate > :p_hire_date]';
BEGIN
--get parsed query handle in cursor_name
DBMS_SQL.parse (cursor_name, l_qry, DBMS_SQL.native);
--set value of dept_no to 20
DBMS_SQL.bind_variable (cursor_name, ':p_dept_no',20 );
--set hire date
DBMS_SQL.bind_variable (cursor_name, ':p_hire_date','01-JAN-1981' );

xxtk_generate_csv_pkg.xxtk_generate_csv(p_query=>cursor_name,p_delimiter=>',',

p_file_handle=>xxtk_generate_csv_pkg.write_to_db_out,p_cur_qry=>'C' );
END;
[/sourcecode]

Output

[sourcecode]

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
[/sourcecode]

2 GB File limit on writing file using Oracle SQLPlus in Unix

Requirement

Dump of huge data is required from oracle database. Say, dump of GL data (specific query output and not complete table dump) is required for particular month.

I simply spool the output of query and what I observe is - it always produces 2GB output file, for few months I tested, without giving any error message or warning.

So, I google for "2GB Unix oracle SQLPlus" and I find number of links mentioning this problem

unix 2GB oracle export

One of the solution involves using named pipe.

A named pipe doesn't write data to disk, but instead write to buffer in memory. Writer writes at one end and Reader reads from other end.

Named Pipe

• named pipe is actually file in file-system


• Like unnamed pipe it is  used for Inter process Communication


• Unlike unnamed pipe it is system-persistent, that is, it exists beyond process life and has to be created and deleted explicitly.


• Process that reads or writes to pipe blocks until the other end of pipes performs read or write operation on same

Create a named pipe, redirect data written to it to sed command.

Removes extra tabs coming in spool file(spool usually gives extra tabs when colsep is used), and then pipe output to compress command which writes compressed data to disk.

Sqlplus will write data at one end to named pipe and following command will write compressed data to disk.

cat /tmp/aug11_pipe | compress  > gl_aug11_pipe.csv

Oracle Correlated Query Scope. Invalid Identifier Error

Consider query

[sourcecode language="sql"]
SELECT (SELECT tab.col  FROM DUAL) a
FROM (SELECT 1 col  FROM DUAL) tab;
[/sourcecode]

Output is

A
---
1

Here I am using tab table value inside subquery that is one level down and it's recognized by system.

Now, If I take it one more level down, that is, using tab value inside subquery's subquery, something like

[sourcecode language="sql"]
SELECT (SELECT * FROM (SELECT tab.col  FROM DUAL)) A
FROM (SELECT 1 col  FROM DUAL) tab
[/sourcecode]

It throws "invalid identifier TAB.COL" error

At Second level its not recognized by system.

Oracle NVL function always evaluates second parameter

NVL function doesn't do short circuit evaluation  i.e, all parameters are evaluated irrespective of condition but, value is returned only when condition returns true.

Lets test this

Consider below query to generate test data

[sourcecode language="sql"]
SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5
[/sourcecode]

it returns 5 rows of which one row is always null

Now, Lets write one simple function which returns some value. We will call this function in case column value is null. Like

[sourcecode language="sql"]
SELECT NVL (tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

The above statement should ideally call function temp_func(defined below) only for one row.

--takes input value and prints it in output
--always returns '***'

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION temp_func (p_text VARCHAR2)
RETURN VARCHAR2
AS
i   VARCHAR2(30) := 'who ';
BEGIN
i := i || p_text;
DBMS_OUTPUT.put_line (i);
RETURN '***';
END;
[/sourcecode]

But above SELECT statement returns five lines of dbms output, one for each row. That means function temp_func got called for each row irrespective of condition.

Unlike NVL, COALESCE function uses short circuit evaluation and so, prints only one line of dbms output

[sourcecode language="sql"]
SELECT coalesce(tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]