Tuesday, April 7, 2015

Example of downloading a pdf from web (https) and saving into disk using PL/SQL.

Some times before I have posted this example: http://saubbane.blogspot.co.uk/2010/12/example-of-downloading-pdf-from-web-and.html
But this will not work in case of https. So I have decided to post another example using UTL_HTTP. This can download any file with https also.

[oracle@localhost saubhik]$ pwd
/home/oracle/saubhik
[oracle@localhost saubhik]$ ls -l *.pdf
-rw-r--r-- 1 oracle oinstall 60055 Apr  6 14:03 TheLoveDare.pdf
[oracle@localhost saubhik]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 7 16:56:49 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> --My database version.
SQL> ----------------------
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> ed
Wrote file afiedt.buf

  1  /* This code is just a demo and can be wrriten more efficiently and in modularied way*/
  2  /*************************************************************************************/
  3  DECLARE
  4    lv_url VARCHAR2(500) := 'https://docs.oracle.com/cd/E11882_01/appdev.112/e25519.pdf';
  5    lc_return BLOB       := EMPTY_BLOB();
  6    ---Varriables declared for writing the LOB to pdf file --
  7    l_file UTL_FILE.FILE_TYPE;
  8    l_buffer RAW(32767);
  9    l_amount BINARY_INTEGER := 32767;
 10    l_pos INTEGER           := 1;
 11    l_blob BLOB             := EMPTY_BLOB();
 12    l_blob_len INTEGER;
 13    /* This procedure is used to get the file from web*/
 14    /*************************************************/
 15  PROCEDURE get_blob_from_https
 16    (
 17      pi_url  IN VARCHAR2,
 18      po_blob IN OUT NOCOPY BLOB)
 19              IS
 20    l_http_request UTL_HTTP.req;
 21    l_http_response UTL_HTTP.resp;
 22    l_http_buffer RAW(32767);
 23  BEGIN
 24    --Setting my wallet. I have downloaded the certificate and configured earlier.
 25    UTL_HTTP.SET_WALLET ('file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle', 'oracle123');
 26    -- Make a HTTP request and get the response.
 27    l_http_request := UTL_HTTP.begin_request(pi_url);
 28    --UTL_HTTP.SET_HEADER(l_http_request, 'User-Agent', 'Mozilla/4.0');
 29    l_http_response := UTL_HTTP.get_response(l_http_request);
 30    BEGIN
 31      LOOP
 32        UTL_HTTP.read_raw(l_http_response, l_http_buffer, 32766);
 33        DBMS_LOB.writeappend (po_blob, utl_raw.length(l_http_buffer), l_http_buffer);
 34      END LOOP;
 35    EXCEPTION
 36    WHEN UTL_HTTP.end_of_body THEN
 37      UTL_HTTP.end_response(l_http_response);
 38    END;
 39  EXCEPTION
 40  WHEN UTL_HTTP.NETWORK_ACCESS_DENIED THEN
 41    dbms_output.put_line(' Check your ACL with DBMS_NETWORK_ACL_ADMIN or Your  Oracle wallet');
 42    RAISE;
 43  WHEN UTL_HTTP.TRANSFER_TIMEOUT THEN
 44    dbms_output.put_line('No data is read and a read timeout occurred');
 45    RAISE;
 46  WHEN OTHERS THEN
 47    UTL_HTTP.end_response(l_http_response);
 48    RAISE;
 49  END get_blob_from_https;
 50  -- End of the procedure.
 51  --- The main execution section begins.
 52  BEGIN
 53    dbms_lob.createtemporary(lc_return, true);
 54    --Get the pdf file from web.
 55    get_blob_from_https(lv_url, lc_return);
 56    l_buffer := NULL;
 57    /*****************************************************************************/
 58    -- Now lc_return is holding the file. You can insert in a table or whatever
 59    -- I am writing in a file using UTL_FILE.
 60    /*****************************************************************************/
 61    -- Open the destination file.
 62    l_file := UTL_FILE.FOPEN('SAUBHIK', 'e25519.pdf', 'wb');
 63    --Get the total length of the BLOB
 64    l_blob_len := DBMS_LOB.getlength(lc_return);
 65    -- Read chunks of the BLOB and write them to the file
 66    -- until complete.
 67    WHILE l_pos < l_blob_len
 68    LOOP
 69      DBMS_LOB.READ(lc_return, l_amount, l_pos, l_buffer);
 70      UTL_FILE.put_raw(l_file, l_buffer, FALSE);
 71      l_pos := l_pos + l_amount;
 72    END LOOP;
 73    -- Close the file.
 74    UTL_FILE.FCLOSE(l_file);
 75  EXCEPTION
 76  WHEN OTHERS THEN
 77    -- Close the file if something goes wrong.
 78    IF UTL_FILE.IS_OPEN(l_file) THEN
 79      UTL_FILE.FCLOSE(l_file);
 80    END IF;
 81    RAISE;
 82* END;
 83  /

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost saubhik]$ ls -l *.pdf -rw-r--r-- 1 oracle oinstall 4916565 Apr 7 16:58 e25519.pdf -rw-r--r-- 1 oracle oinstall 60055 Apr 6 14:03 TheLoveDare.pdf [oracle@localhost saubhik]$

Saturday, April 4, 2015

Joining a comma separated list with Oracle Text.

This is also based on one of my posting in OTN.
The problem description:

SQL> create table xx2(id varchar2(10),name varchar2(10));

Table created.

SQL> insert into xx2 values (1,'xyz');

1 row created.

SQL> insert into xx2 values (2,'abc');

1 row created.

SQL> insert into xx2 values (3,'zadaf');

1 row created.

SQL> create table xx3(id varchar2(10),name varchar2(10));

Table created.

SQL>  insert into xx3 values (1,'afdafef');

1 row created.

SQL> insert into xx3 values ('56,78,2,6','afefef');

1 row created.

SQL> insert into xx3 values ('45,67,3','svfsrgg');

1 row created.

SQL> select * from xx2;


ID         NAME

---------- ----------
1          xyz
2          abc
3          zadaf
SQL> select * from xx3;

ID         NAME
----------   ----------
1             afdafef
56,78,2,6  afefef
45,67,3    svfsrgg
 
/*
I want to join table xx2 & x3 based on id and i have to retrive all 4 rows becuase id's of xx1 is there in id's of xx2 but in between commas.So i have to look in between commas as well. */

    [oracle@localhost ~]$   
    [oracle@localhost ~]$ sqlplus / as sysdba  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 3 11:36:40 2015  
      
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
      
      
    Connected to:  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
      
    SQL> SHOW USER  
    USER is "SYS"  
    SQL> --My database version.  
    SQL> SELECT * FROM v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> --Preparing the user SCOTT to use Oracle Text.  
    SQL> ----------------------------------------------  
    SQL> --Grant Role.  
    SQL> GRANT ctxapp TO scott;  
      
    Grant succeeded.  
      
    SQL> --Grant EXECUTE Privileges on CTX PL/SQL Packages.  
    SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;   
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;  
      
    Grant succeeded.  
      
    SQL>   
Table and data setup:
    [oracle@localhost ~]$ sqlplus scott/tiger  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 3 11:59:34 2015  
      
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
      
      
    Connected to:  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
      
    SQL> SHOW USER  
    USER is "SCOTT"  
    SQL> --Your table and data setup.  
    SQL> create table xx2(id varchar2(10),name varchar2(10));  
      
    Table created.  
      
    SQL> insert into xx2 values (1,'xyz');  
      
    1 row created.  
      
    SQL> insert into xx2 values (2,'abc');  
      
    1 row created.  
      
    SQL> insert into xx2 values (3,'zadaf');  
      
    1 row created.  
      
    SQL> create table xx3(id varchar2(10),name varchar2(10));  
      
    Table created.  
      
    SQL> insert into xx3 values (1,'afdafef');  
      
    1 row created.  
      
    SQL> insert into xx3 values ('56,78,2,6','afefef');  
      
    1 row created.  
      
    SQL> insert into xx3 values ('45,67,3','svfsrgg');  
      
    1 row created.  
      
    SQL> insert into xx3 values (34,'sff');  
      
    1 row created.  
      
    SQL> set line 150  
    SQL> SELECT * FROM xx2;  
      
    ID         NAME  
    ---------- ----------  
    1          xyz  
    2          abc  
    3          zadaf  
      
    SQL> SELECT * FROM xx3;  
      
    ID         NAME  
    ---------- ----------  
    1          afdafef  
    56,78,2,6  afefef  
    45,67,3    svfsrgg  
    34         sff  
      
    SQL> commit;  
      
    Commit complete.  
      
    SQL>   

Now, The actual setup and testing:
    SQL>   
    SQL> SHOW USER  
    USER is "SCOTT"  
    SQL> -- Creating my text index setup.  
    SQL> --------------------------------  
    SQL> --Droping my preference, you may not need this for the first time.  
    SQL> BEGIN  
      2   CTX_DDL.DROP_PREFERENCE('SAUBHIK_SJ_PREF');  
      3  END;  
      4  /  
      
    PL/SQL procedure successfully completed.  
      
    SQL> -- Creating now.  
    SQL> BEGIN  
      2   CTX_DDL.CREATE_PREFERENCE('SAUBHIK_SJ_PREF',BASIC_LEXER');  
      3  CTX_DDL.SET_ATTRIBUTE('SAUBHIK_SJ_PREF','STARTJOINS',',');  
      4  END;  
      5  /  
    ERROR:  
    ORA-01756: quoted string not properly terminated  
      
      
    SQL> ED  
    Wrote file afiedt.buf  
      
      1  BEGIN  
      2   CTX_DDL.CREATE_PREFERENCE('SAUBHIK_SJ_PREF','BASIC_LEXER');  
      3   CTX_DDL.SET_ATTRIBUTE('SAUBHIK_SJ_PREF','STARTJOINS',',');  
      4* END;  
    SQL> /  
      
    PL/SQL procedure successfully completed.  
      
    SQL> DROP INDEX xx3_context_idx;  
      
    Index dropped.  
      
    SQL> CREATE INDEX xx3_context_idx ON xx3(id) INDEXTYPE IS CTXSYS.CONTEXT parameters('lexer SAUBHIK_SJ_PREF');  
      
    Index created.  
      
    SQL> set line 100  
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3  
      2  FROM xx2 t1,xx3 t2  
      3* WHERE CONTAINS(t2.id,t1.id)>0;  
    SQL> /  
    WHERE CONTAINS(t2.id,t1.id)>0;  
                                 *  
    ERROR at line 3:  
    ORA-00911: invalid character  
      
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3  
      2  FROM xx2 t1,xx3 t2  
      3* WHERE CONTAINS(t2.id,t1.id)>0  
    SQL> /  
      
    ID_XX3     NAME_XX2   NAME_XX3  
    ---------- ---------- ----------  
    1          xyz        afdafef  
    56,78,2,6  abc        afefef  
    45,67,3    zadaf      svfsrgg  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3  
      2  FROM  xx2 t1 RIGHT OUTER JOIN xx3 t2  
      3* ON CONTAINS(t2.id,t1.id)>0  
      4  /  
      
    ID_XX3     NAME_XX2   NAME_XX3  
    ---------- ---------- ----------  
    1          xyz        afdafef  
    56,78,2,6  abc        afefef  
    45,67,3    zadaf      svfsrgg  
    34                    sff