Saturday, January 28, 2017

SQL101: Using Oracle Analytical function - range between unbounded preceding and 1 preceding

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to Using Oracle Analytical function with range between unbounded preceding and 1 preceding, the problem was posted in OTN.
Test table and data setup:
First, I have used the following to create the test table and insert the sample data.

--Test table
create table daily_tr_tab
  (
    invid    number unique, --- though this is also not used in my query. if you need any unique column to give me a better solution.
    dates    date,
    purchase number,
    sales    number
  );
  

--Test data setup 
REM INSERTING into daily_tr_tab
SET DEFINE OFF;
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (1,to_date('22-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (2,to_date('23-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (3,to_date('24-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),10,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (4,to_date('25-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),60,45);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (5,to_date('26-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),40,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (6,to_date('27-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (7,to_date('28-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),25,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (8,to_date('29-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (9,to_date('30-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (10,to_date('31-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (11,to_date('01-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (12,to_date('02-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (13,to_date('03-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (14,to_date('04-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (15,to_date('05-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),15,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (16,to_date('06-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (17,to_date('07-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (18,to_date('08-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (19,to_date('09-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,30);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (20,to_date('10-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (21,to_date('11-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (22,to_date('12-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (23,to_date('13-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (24,to_date('14-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),12,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (25,to_date('15-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),13,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (26,to_date('16-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),14,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (27,to_date('17-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),24,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (28,to_date('18-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),34,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (29,to_date('19-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
insert into daily_tr_tab (invid,dates,purchase,sales) values (30,to_date('20-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,10);

The output needed:
  1. DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY  
  2. --------- ----------- ---------- ---------- -----------  
  3. 01-JAN-16         100         20         10         110  
  4. 02-JAN-16         110         30         25         115  
  5. 03-JAN-16         115         25          0         140  
  6. 04-JAN-16         140         35         25         150  
  7. 05-JAN-16         150         15         10         155  
  8. 06-JAN-16         155         20         15         160  
  9. 07-JAN-16         160         25         10         175  
  10. 08-JAN-16         175         30         20         185  
  11. 09-JAN-16         185         35         30         190  
  12. 10-JAN-16         190         20         20         190  
  13. 11-JAN-16         190         30          5         215  
 Opening quantity in a date is total of previous purchase minus total sales. The closing balance is opening quantity of that date minus the sale.

The solution is:
 
SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select *
  2  from
  3    (select dates,
  4        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding )
  5      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) opening_qty ,
  6      purchase,
  7      sales ,
  8        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding)
  9      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) + purchase - sales closing_qty
 10    from daily_tr_tab
 11    )
 12  where dates >= to_date('01/01/16', 'dd/mm/rr')
 13* order by dates
SCOTT@orclSB 28-JAN-17> /

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
01-JAN-16         100         20         10         110
02-JAN-16         110         30         25         115
03-JAN-16         115         25          0         140
04-JAN-16         140         35         25         150
05-JAN-16         150         15         10         155
06-JAN-16         155         20         15         160
07-JAN-16         160         25         10         175
08-JAN-16         175         30         20         185
09-JAN-16         185         35         30         190
10-JAN-16         190         20         20         190
11-JAN-16         190         30          5         215

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
12-JAN-16         215         40          0         255
13-JAN-16         255         50         20         285
14-JAN-16         285         12         10         287
15-JAN-16         287         13         10         290
16-JAN-16         290         14         10         294
17-JAN-16         294         24          5         313
18-JAN-16         313         34          0         347
19-JAN-16         347         35         25         357
20-JAN-16         357         40         10         387

20 rows selected.

SCOTT@orclSB 28-JAN-17> 

SQL101: Using sequence.nextval with group and analytical function.

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to Using sequence.nextval with group and analytical function, the problem was posted in OTN.
The problem with sequence.nextval is it increments within SQL statement even within the group. Carefully watch this test case:
SCOTT@orclSB 28-JAN-17> --My database version.
SCOTT@orclSB 28-JAN-17> 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

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Creating the sequence.
  2* create sequence file_seq minvalue 1 maxvalue 9999999999999999999999999999 increment by 1 start with 100 cache 20 noorder nocycle
SCOTT@orclSB 28-JAN-17> /

Sequence created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Creating the test table.
  2  create table test4
  3    ( file_number number, seq_no number
  4*   )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  --Inserting my test data.
  2  insert into test4
  3  select 10,null from dual
  4  union all
  5  select 10,null from dual
  6  union all
  7  select 10,null from dual
  8  union all
  9  select 20,null from dual
 10  union all
 11  select 20,null from dual
 12  union all
 13* select 30,null from dual
SCOTT@orclSB 28-JAN-17> /

6 rows created.

SCOTT@orclSB 28-JAN-17> commit;

Commit complete.

SCOTT@orclSB 28-JAN-17> select file_number,dense_rank() over (order by file_number) dnk from test4;

FILE_NUMBER        DNK
----------- ----------
         10          1
         10          1
         10          1
         20          2
         20          2
         30          3

6 rows selected.

SCOTT@orclSB 28-JAN-17> 
 
 
Required Output
FILE_NUMBER, DNK
10                          100
10                          100
10                          100
20                          101
20                          101
30                          103

Rules:
If you noticed that I created sequence name as file_SEQ, system should  user this sequence to generate dnk column  and if file_number change then get next sequence. So far I created the below query but it is not working.
SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select file_number,
  2    dense_rank() over (order by file_number) + file_seq.nextval dnk
  3* from test4
SCOTT@orclSB 28-JAN-17> /

FILE_NUMBER        DNK
----------- ----------
         10        101
         10        102
         10        103
         20        105
         20        106
         30        108

6 rows selected.

SCOTT@orclSB 28-JAN-17> 
Now, you can see the problem, it is creating counting without grouping of file_number.
This is the actual solution of this problem:

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  create or replace function retfileseq(
  2      pi_indicator integer)
  3    return number
  4  as
  5  begin
  6    if pi_indicator = 1 then
  7      return (file_seq.nextval);
  8    else
  9      return (file_seq.currval);
 10    end if;
 11* end retfileseq;
SCOTT@orclSB 28-JAN-17> /

Function created.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select file_number ,
  2    case
  3      when lag(file_number) over(order by file_number) is null
  4      or file_number <> lag(file_number) over(order by file_number)
  5      then retfileseq(1)
  6      else retfileseq(0)
  7    end as file_seq
  8* from test4
SCOTT@orclSB 28-JAN-17> /

FILE_NUMBER   FILE_SEQ
----------- ----------
         10        106
         10        106
         10        106
         20        107
         20        107
         30        108

6 rows selected.

SCOTT@orclSB 28-JAN-17> 

SQL101 : Oracle Check constraint to prevent alphanumeric value in column

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to check constraint. We can implement check constraint very efficiently for many of our problems, sometimes people tends to use triggers for those, but remember trigger is not a good option in most of the time. You may want to read this article from Tom Kyte regarding trigger : Ask Tom: The Trouble with Triggers..
I will describe two scenarios, first we have a table with whole_name column, So we want to restrict bad entries in that column and we want to allow only alphabet and space as name can not be alphanumeric or purely numeric!
Second, we have a table with first_name and last_name column, So we want to allow only alphabet here, no numeric and no space.
 
SCOTT@orclSB 28-JAN-17> --My database version.
SCOTT@orclSB 28-JAN-17> 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

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  -- In the whole_name column, we will only allow alphabet and space.
  2  CREATE TABLE contacts
  3      (
  4      whole_name varchar2(30) constraint chk_whole_name check (regexp_like (whole_name,'^[[:alpha:][:space:]]+$'))
  5*     )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');

1 row created.

SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should work as only alphabet and space.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');

1 row created.

SCOTT@orclSB 28-JAN-17> drop table contacts purge;

Table dropped.

SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  -- Now, there is no whole_name column, instead we have broken down as first_name and last_name.
  2  -- So, Only alphabet should be allowed, no numeric character and no space.
  3  CREATE TABLE contacts
  4      (
  5      first_name varchar2(30) constraint chk_first_name check (regexp_like (first_name,'^[[:alpha:]]+$'))
  6*     )
SCOTT@orclSB 28-JAN-17> /

Table created.

SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');

1 row created.

SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> --This should not work as space is not allowed within first_name.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');
insert into contacts values ('Saubhik Banerjee')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated


SCOTT@orclSB 28-JAN-17> drop table contacts purge;

Table dropped.

SCOTT@orclSB 28-JAN-17> 

Wednesday, July 20, 2016

Oracle External Table PREPROCESSOR: grep and find

This is based on one thread in OTN We will be using Oracle external table preprocessor feature to find file with some keyword using grep and also try to find some files with particular name using find command.
You can see my previous posts of listing files and disk usages using external table in this blog.

    [oracle@localhost ~]$ sqlplus / as sysdba  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 18 13:08:53 2016  
      
    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> -- Creating the directory object.  
    SQL> CREATE OR REPLACE DIRECTORY SAUBHIK AS '/home/oracle/saubhik';  
      
    Directory created.  
      
    SQL> GRANT read, write, execute on DIRECTORY saubhik TO scott;  
      
    Grant succeeded.  

    [oracle@localhost saubhik]$ cat search_files.sh  
    #!/bin/bash  
    # This shell script is call by External Table's PREPROCESSOR claus.  
    # As per the external table definitin the file named as "searchfile.txt" is passed as argument 1 ($1) to this script.  
      
      
    # Reading the output of the file into a varriable  
    a=`/bin/cat $1|/bin/cut -d " " -f1 `  
    b=`/bin/cat $1|/bin/cut -d " " -f2`  
      
      
    # Listing the files. recursive search, change according to your requirement.  
    /usr/bin/find $a -type f -exec /bin/grep -H $b {} \;  
    exit  
    [oracle@localhost saubhik]$   

    SQL> ed  
    Wrote file afiedt.buf  
      
      1  create table Searchfiles_Ext  
      2         (   fname varchar2(500 BYTE)  
      3         )  
      4        organization external  
      5         ( type ORACLE_LOADER  
      6           default directory SAUBHIK  
      7           access parameters  
      8           ( records delimited by newline  
      9             PREPROCESSOR SAUBHIK: 'search_files.sh'  
    10             skip 2  
    11             badfile SAUBHIK:'searchfiles_ext%a_%p.bad'  
    12             logfile SAUBHIK:'searchfiles_ext%a_%p.log'  
      
      
      
      
    13             fields terminated by ',' lrtrim  
    14             missing field values are null (fname )  
    15                                           )  
    16           LOCATION  
    17            ( SAUBHIK:'searchfiles.txt'  
    18            )  
    19         )  
    20        reject limit unlimited  
    21*     parallel 2  
    SQL> /  
      
    Table created.  

SQL> ed  
Wrote file afiedt.buf  
  
  1  create or replace type SearchFilesType as object  
  2         (   FileName varchar2(500)  
  3*        )  
  4  /  
  
Type created.  
  
SQL> create or replace type SearchFilesTable as table of  SearchFilesType;  
  2  /  
  
Type created. 

SQL> ed  
Wrote file afiedt.buf  
  
  1      create or replace  
  2      function GetFileList  
  3        (  
  4          SearchPath varchar2,  
  5          SearchString varchar2  
  6          )  
  7        return SearchFilesTable pipelined  
  8      as  
  9        v_file_handle utl_file.file_type;  
10        v_dir_name     varchar2(50):='SAUBHIK';  
11       v_max_linesize integer     :=32767;  
12       v_file_name    varchar2(50):='searchfiles.txt';  
13       v_write_buffer varchar2(4000);  
14     begin  
15       v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize);  
16       v_write_buffer:=SearchPath||' '||SearchString;  
17       utl_file.put_line(v_file_handle,v_write_buffer,TRUE);  
18       utl_file.fclose(v_file_handle);  
19       for i in  
20       (select  
21         fname  
22       from searchfiles_ext  
23       )  
24       loop  
25         pipe row (SearchFilesType(i.fname));  
26       end loop;  
27*   end;  
SQL> /  
  
Function created. 

    SQL> select * from table(GetFileList('/home/oracle/saubhik','saubhik')) where rownum<5 --------------------------------------------------------------------------------="" 13="" 7:="" afiedt.buf:select="" arch_files.sh:="" calling="" command="" cut:="" directory="" during="" encountered="" errno="" error="" etfilelist="" exec:="" execlp="" file="" filename="" from="" home="" is="" kup-04095:="" line="" no="" or="" oracle="" preprocessor="" sa="" saubhik="" se="" search_files.sh="" searchfiles.txt:="" searchfiles_ext000_7419.log:kup-03154:="" searchfiles_ext000_7419.log:kup-04095:="" sql="" such="" table="" ubhik="">   

grep on file names, this is much easier. I have only changed the shell, everything else remain unchanged.
    [oracle@localhost saubhik]$ cat search_files.sh  
    #!/bin/bash  
    # This shell script is call by External Table's PREPROCESSOR claus.  
    # As per the external table definitin the file named as "searchfile.txt" is passed as argument 1 ($1) to this script.  
      
      
    # Reading the output of the file into a varriable  
    a=`/bin/cat $1|/bin/cut -d " " -f1 `  
    b=`/bin/cat $1|/bin/cut -d " " -f2`  
      
      
    # Listing the files. recursive search, change according to your requirement.  
    /usr/bin/find $a -name "*$b*" -print   
    exit  
    [oracle@localhost saubhik]$  

    SQL> select * from table(GetFileList('/home/oracle/saubhik','ext'));   
      
    FILENAME  
    --------------------------------------------------------------------------------  
    /home/oracle/saubhik/searchfiles_ext000_19025.log  
    /home/oracle/saubhik/searchfiles_ext000_11281.log  
    /home/oracle/saubhik/searchfiles_ext000_11040.log  
    /home/oracle/saubhik/searchfiles_ext000_18919.log  
    /home/oracle/saubhik/searchfiles_ext000_17676.log  
    /home/oracle/saubhik/searchfiles_ext000_7419.log  
    /home/oracle/saubhik/searchfiles_ext000_12996.log  
    /home/oracle/saubhik/searchfile_ext000_7419.log  
    /home/oracle/saubhik/searchfiles_ext000_18077.log  
      
    9 rows selected.  
      
    SQL> select * from table(GetFileList('/home/oracle','oracle')) where rownum<5;   
      
    FILENAME  
    --------------------------------------------------------------------------------  
    /home/oracle  
    /home/oracle/.java/.userPrefs/.userRootModFile.oracle  
    /home/oracle/.java/.userPrefs/.user.lock.oracle  
    /home/oracle/.java/.userPrefs/oracle  

Thursday, July 14, 2016

Calling Web Service From PL/SQL : Temperature Converter.

This is also based on a post from OTN.
I have used WebserviceX.NET :: XML Web Services solution provider. This has many webservices that you can use for your practice (I do).
Here is one demo of converting temperature. The code, coding standard and comments are signature of Billy~Verreynne. This is build on function called GetCityWeather.
Now, the question comes where did I get this SOAP envelop. If you go to that WebService detail page and click on the name then you can see various envelops.

    [oracle@localhost ~]$ sqlplus scott/tiger  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 12 12:04:34 2016  
      
    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 dtabase 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> ed  
    Wrote file afiedt.buf  
      
      1  create or replace function ConvertTemperature( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return XmlType is  
      2              --// URL to call  
      3              SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';  
      4              --// SOAP envelope template, containing $ substitution variables  
      5              SOAP_ENVELOPE   constant varchar2(32767) :=  
      6      '<?xml version="1.0" encoding="utf-8"?>  
      7  <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">  
      8    <soap:Body>  
      9      <ConvertTemp xmlns="http://www.webserviceX.NET/">  
     10        <Temperature>$TEMPERATURE</Temperature>  
     11        <FromUnit>$FROMUNIT</FromUnit>  
     12        <ToUnit>$TOUNIT</ToUnit>  
     13      </ConvertTemp>  
     14    </soap:Body>  
     15  </soap:Envelope>';  
     16             --// we'll identify ourselves using an IE9/Windows7 generic browser signature  
     17             C_USER_AGENT    constant varchar2(4000) := ''Mozilla/5.0 (compatible; MSIE 9.0 Windows NT 6.1 Trident/5.0)';  
     18             --// these variables need to be set if web access  
     19             --// is via a proxy server  
     20             proxyServer varchar2(20) default null;  
     21             proxyUser varchar2(20) default null;  
     22             proxyPass varchar2(20) default null;  
     23             --// our local variables  
     24             soapEnvelope    varchar2(32767);  
     25             proxyURL        varchar2(4000);  
     26             request         utl_http.req;  
     27             response        utl_http.resp;  
     28             buffer          varchar2(32767);  
     29             soapResponse    clob;  
     30             xmlResponse     XmlType;  
     31             eof             boolean;  
     32     begin  
     33             --// create the SOAP envelope  
     34             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );  
     35             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );  
     36             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );  
     37             --// our "browser" settings  
     38             utl_http.set_response_error_check( true );  
     39             utl_http.set_detailed_excp_support( true );  
     40             utl_http.set_cookie_support( true );  
     41             utl_http.set_transfer_timeout( 10 );  
     42             utl_http.set_follow_redirect( 3 );  
     43             utl_http.set_persistent_conn_support( true );  
     44             --// configure for web proxy access if applicable  
     45             if proxyServer is not null then  
     46                     proxyURL := 'http://'||proxyServer;  
     47                     if (proxyUser is not null) and (proxyPass is not null) then  
     48                             proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );  
     49                     end if;  
     50                      utl_http.set_proxy( proxyURL, null );  
     51             end if;  
     52             --// make the POST call to the web service  
     53             request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );  
     54             utl_http.set_header( request, 'User-Agent', C_USER_AGENT );  
     55             utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );  
     56             utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );  
     57             utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/ConvertTemp' );  
     58             utl_http.write_text( request, soapEnvelope );  
     59             --// read the web service HTTP response  
     60             response := utl_http.get_response( request );  
     61             dbms_lob.CreateTemporary( soapResponse, true );  
     62             eof := false;  
     63             loop  
     64                     exit when eof;  
     65                     begin  
     66                             utl_http.read_line( response, buffer, true );  
     67                             if length(buffer) > 0 then  
     68                                     dbms_lob.WriteAppend(  
     69                                             soapResponse,  
     70                                             length(buffer),  
     71                                             buffer  
     72                                     );  
     73                             end if;  
     74                     exception when utl_http.END_OF_BODY then  
     75                             eof := true;  
     76                     end;  
     77             end loop;  
     78             utl_http.end_response( response );  
     79             --// as the SOAP responds with XML, we convert  
     80             --// the response to XML  
     81             xmlResponse := XmlType( soapResponse );  
     82             dbms_lob.FreeTemporary( soapResponse );  
     83             return( xmlResponse );  
     84     exception when OTHERS then  
     85             if soapResponse is not null then  
     86                     dbms_lob.FreeTemporary( soapResponse );  
     87             end if;  
     88             raise;  
     89*   end;  
     90  /  
      
    Function created.  
      
    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 ~]$ sqlplus / as sysdba  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 12 12:05:45 2016  
      
    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> BEGIN  
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'ConverTemperature.xml',  
                                        description => 'ConverTemperature ACL',  
                                        principal   => 'SCOTT',  
                                        is_grant    => true,  
                                        privilege   => 'connect');  
      
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'ConverTemperature.xml',  
                                           principal => 'SCOTT',  
                                           is_grant  => true,  
                                           privilege => 'resolve');  
      
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'ConverTemperature.xml',  
                                        host => 'www.webservicex.net');  
     commit;                                      
    END;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16    
     17  /  
      
    PL/SQL procedure successfully completed.  
      
    SQL> conn scott/tiger  
    Connected.  
    SQL> set line 1000  
    SQL> set long 100000  
    SQL> select ConvertTemperature(20,'degreeCelsius','degreeFahrenheit') from dual;  
      
    CONVERTTEMPERATURE(20,'DEGREECELSIUS','DEGREEFAHRENHEIT')  
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
    <?xml version="1.0" encoding="US-ASCII"?>  
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">  
      <soap:Body>  
        <ConvertTempResponse xmlns="http://www.webserviceX.NET/">  
          <ConvertTempResult>68</ConvertTempResult>  
        </ConvertTempResponse>  
      </soap:Body>  
    </soap:Envelope>  
      
      
    SQL> select ConvertTemperature(68,'degreeFahrenheit','degreeCelsius') from dual;  
      
    CONVERTTEMPERATURE(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')  
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
    <?xml version="1.0" encoding="US-ASCII"?>  
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">  
      <soap:Body>  
        <ConvertTempResponse xmlns="http://www.webserviceX.NET/">  
          <ConvertTempResult>20</ConvertTempResult>  
        </ConvertTempResponse>  
      </soap:Body>  
    </soap:Envelope>  
      
      
    SQL>   

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]$