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

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  

Monday, March 30, 2015

Join using Like Condition - Using Oracle Text

This is also based on one of my posting in OTN. Here, I have demonstrated the use of Oracle Text in place of LIKE operator. This may give significant benefit in terms of performance.

The problem.
create table location_source
(code number,
description varchar2(100)
);

 

insert into location_source values('', 'check in london');
insert into location_source values('', 'flight Paris 9.30');
insert into location_source values('', 'baggage allowance 20kg Hong kong');
insert into location_source values('', 'check out 6.30');
insert into location_source values('', 'new york 23rd Jul');
insert into location_source values('', 'flight jy688 sydney');

 

create table location_lookup
(code number
location_lookup varchar2(10)
);

 

insert into location_lookup values (001, 'London');
insert into location_lookup values (002, 'London');
insert into location_lookup values (003, 'Paris');
insert into location_lookup values (004, 'Hong Kong');
insert into location_lookup values (005, 'Paris');
insert into location_lookup values (006, 'New York');

The SQL looks something like this:

select s.*, min(l.code) , l.location_lookup
from location_source s, locaction_lookup l
and UPPER(s.description||'%') like upper(l.location_lookup||'%')
group by s.*, l.location_lookup

 

Output Required:

 
Code	Description	                  Code	Location_Lookup
	check in london	                  001	London
	flight Paris 9.30                 003	Paris
	baggage allowance 20kg Hong kong  004	Hong Kong
	check out 6.30		
	new york 23rd Jul	          006	New York
	flight jy688 sydney

Now, The problem is other methods using LIKE operator is running slow for huge amount of data. Oracle Text is very useful in these cases.

Preparing SCOTT to use Oracle Text:
    SQL>   
    SQL> SHOW USER  
    USER is "SYS"  
    SQL>   
    SQL> GRANT ctxapp TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;  
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  

Now the actual test::
    SQL> SHOW user  
    USER is "SCOTT"  
    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> SELECT * FROM location_source;  
      
          CODE DESCRIPTION  
    ---------- ----------------------------------------------------------------------------------------------------  
               check in london  
               flight Paris 9.30  
               baggage allowance 20kg Hong kong  
               check out 6.30  
               new york 23rd Jul  
               flight jy688 sydney  
      
    6 rows selected.  
      
    SQL> SELECT * FROM location_lookup;  
      
          CODE LOCATION_L  
    ---------- ----------  
             1 London  
             2 London  
             3 Paris  
             4 Hong Kong  
             5 Paris  
             6 New York  
      
    6 rows selected.  
      
    SQL> CREATE INDEX location_source_idx ON location_source(description) INDEXTYPE IS CTXSYS.CONTEXT;  
      
    Index created.  
      
    SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LOCATION_SOURCE', cascade=>TRUE);  
      
    PL/SQL procedure successfully completed.  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT ls.code,  
      2    ls.description,  
      3    MIN(ll.code) new_code,  
      4    ll.location_lookup  
      5  FROM location_source ls  
      6  LEFT OUTER JOIN location_lookup ll  
      7  ON contains(ls.description,ll.location_lookup)>0  
      8  GROUP BY ls.code,  
      9    ls.description,  
     10*   ll.location_lookup  
    SQL> /  
      
          CODE DESCRIPTION                                                                                            NEW_CODE LOCATION_L  
    ---------- ---------------------------------------------------------------------------------------------------- ---------- ----------  
               check in london                                                                                               1 London  
               baggage allowance 20kg Hong kong                                                                              4 Hong Kong  
               check out 6.30  
               new york 23rd Jul                                                                                             6 New York  
               flight Paris 9.30                                                                                             3 Paris  
               flight jy688 sydney  
      
    6 rows selected.  

Tuesday, March 10, 2015

Listing Operating system disk usages with Oracle External Table PREPROCESSOR feature

This is based on one of my posting in OTN.
Database version and directory setups:

    SQL> SHOW user  
    USER is "SYS"  
    SQL>  
    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> --Creating 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.  
      
    SQL>
    
    Now, the OS setups:
    1. [oracle@localhost saubhik]$  
    2. [oracle@localhost saubhik]$ ## My Operating System.  
    3. [oracle@localhost saubhik]$ uname -a  
    4. Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
    5. [oracle@localhost saubhik]$ pwd  
    6. /home/oracle/saubhik  
    7. [oracle@localhost saubhik]$ cat free_space.sh  
    8. #This shell script is call by External Table's PREPROCESSOR claus.  
    9. # As per the external table definitin the file named as "frespace.txt" is passed as argument 1 ($1) to this script.  
    10. #!/bin/bash  
    11.   
    12. # Reading the output of the file into a varriable  
    13. a=`/bin/cat $1`  
    14.   
    15. # Listing the space informations  
    16. /bin/df -Pkh $a  
    17. exit  
    18. [oracle@localhost saubhik]$  
    19. [oracle@localhost saubhik]$ cat freespace.txt  
    20. /home  
    21. [oracle@localhost saubhik]$  
    Now the External Table setup:
    1. SQL>  
    2. SQL> SHOW USER  
    3. USER is "SCOTT"  
    4. SQL> --My database version.  
    5. SQL> SELECT * FROM v$version;  
    6.   
    7. BANNER  
    8. --------------------------------------------------------------------------------  
    9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    10. PL/SQL Release 11.2.0.1.0 - Production  
    11. CORE    11.2.0.1.0      Production  
    12. TNS for Linux: Version 11.2.0.1.0 - Production  
    13. NLSRTL Version 11.2.0.1.0 - Production  
    14.   
    15. SQL> -- Dropping the table before creating. You may not ned this.  
    16. SQL> DROP TABLE freespace_ext PURGE;  
    17.   
    18. Table dropped.  
    19.   
    20. SQL> --Creating the table.  
    21. SQL> ed  
    22. Wrote file afiedt.buf  
    23.   
    24.   1   CREATE TABLE "SCOTT"."FREESPACE_EXT"  
    25.   2     (       "FILESYSTEM" VARCHAR2(50 BYTE),  
    26.   3     "FSIZE" VARCHAR2(20 BYTE),  
    27.   4     "USED" VARCHAR2(20 BYTE),  
    28.   5     "FREE" VARCHAR2(20 BYTE),  
    29.   6     "USE_PER" VARCHAR2(20 BYTE),  
    30.   7     "MOUNTPOINT" VARCHAR2(20 BYTE)  
    31.   8     )  
    32.   9     ORGANIZATION EXTERNAL  
    33. 10      ( TYPE ORACLE_LOADER  
    34. 11        DEFAULT DIRECTORY "SAUBHIK"  
    35. 12        ACCESS PARAMETERS  
    36. 13        ( RECORDS DELIMITED BY NEWLINE  
    37. 14             PREPROCESSOR SAUBHIK: 'free_space.sh'  
    38. 15             skip 2  
    39. 16             badfile SAUBHIK:'freespace_ext%a_%p.bad'  
    40. 17             logfile SAUBHIK:'freespace_ext%a_%p.log'  
    41. 18             fields terminated by whitespace lrtrim  
    42. 19             missing field values are null (filesystem,  
    43. 20                                            fsize ,  
    44. 21                                            used ,  
    45. 22                                            free ,  
    46. 23                                            use_per ,  
    47. 24                                            mountpoint)  
    48. 25                                               )  
    49. 26        LOCATION  
    50. 27         ( "SAUBHIK":'freespace.txt'  
    51. 28         )  
    52. 29      )  
    53. 30     REJECT LIMIT UNLIMITED  
    54. 31*   PARALLEL 2  
    55. SQL> /  
    56.   
    57. Table created.  
    58.   
    59. SQL> -- A sample testing. Our /home/oracle/freespace.txt contains /home. So it should report the usages of that.  
    60. SQL> SELECT * FROM freespace_ext;  
    61.   
    62. FILESYSTEM                                         FSIZE                USED                 FREE                 USE_PER              MOUNTPOINT  
    63. -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------  
    64. Filesystem                                         Size                 Used                 Avail                Use%                 Mounted  
    65. /dev/mapper/VolGroup00-LogVol04                    2.0G                 104M                 1.8G                 6%                   /home  
    66.   
    67. SQL>  
    Now if you want it in a flexible way:
      SQL> SQL> SHOW USER USER is "SCOTT" SQL> --Creating a pipeline table function to get the free space. The mount point passed as an argument. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE freespace_typ AS OBJECT 2 ( filesystem VARCHAR2(50), 3 fsize VARCHAR2(20), 4 used VARCHAR2(20), 5 free VARCHAR2(20), 6 use_per VARCHAR2(20), 7 mountpoint VARCHAR2(20) 8* ) SQL> / Type created. SQL> CREATE OR REPLACE TYPE freespace_tbl_typ AS TABLE OF freespace_typ; 2 / Type created. SQL> ed Wrote file afiedt.buf 1 ---My wrapper function. 2 CREATE OR REPLACE 3 FUNCTION get_free_space 4 ( 5 pi_path VARCHAR2) 6 RETURN freespace_tbl_typ PIPELINED 7 AS 8 v_file_handle utl_file.file_type; 9 v_dir_name VARCHAR2(50):='SAUBHIK'; 10 v_max_linesize INTEGER :=32767; 11 v_file_name VARCHAR2(50):='freespace.txt'; 12 v_write_buffer VARCHAR2(4000); 13 BEGIN 14 v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize); 15 v_write_buffer:=pi_path; 16 utl_file.put_line(v_file_handle,v_write_buffer,TRUE); 17 utl_file.fclose(v_file_handle); 18 FOR i IN 19 (SELECT filesystem, 20 fsize , 21 used , 22 free , 23 use_per , 24 mountpoint 25 FROM freespace_ext 26 ) 27 LOOP 28 PIPE ROW (freespace_typ(i.filesystem,i.fsize,i.used,i.free,i.use_per,i.mountpoint)); 29 END LOOP; 30 RETURN; 31* END get_free_space; SQL> / Function created. SQL> SELECT * FROM TABLE(get_free_space('/')); FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('/u01')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 /dev/mapper/VolGroup00-LogVol04 2.0G 104M 1.8G 6% /home /dev/mapper/VolGroup00-LogVol03 5.7G 851M 4.6G 16% /tmp /dev/sda1 99M 13M 82M 14% /boot tmpfs 1.5G 719M 782M 48% /dev/shm 7 rows selected. SQL>