Oracle SQL

How to speed up slow unicode migration of a table with xmltype columns

Posted on by Posted in oracle, parallel, query optimizing, troubleshooting Leave a comment

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute 

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF';

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

row pieces, 255 columns, intra-block row chaining in details

Posted on by Posted in oracle, trace, undocumented 10 Comments

You may know about Intra-block row chaining, that may occur when the number of columns in a table is more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block. (not valid since 12.2)

I’ll show in examples with dumps:
Continue reading→

How even empty trigger increases redo generation

Posted on by Posted in oracle 2 Comments

Very simple example:

Test case 

set feed on;
-- simple table:
create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
-- same table but with empty trigger:
create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
  null;
end;
/

set autot trace stat;
update xt_curr1 set b=a;
set autot off;

set autot trace stat;
update xt_curr2 set b=a;
set autot off;
set feed off

drop table xt_curr1 purge;
drop table xt_curr2 purge;

[collapse]

Update 2017/11/19: I have posted more detailed test cases with logfile dumps here.

SQL> -- simple table:
SQL> create table xt_curr1 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> -- same table but with empty trigger:
SQL> create table xt_curr2 as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
  2  begin
  3    null;
  4  end;
  5  /

Trigger created.

SQL> update xt_curr1 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         25  recursive calls
      10553  db block gets
         91  consistent gets
         18  physical reads
    3101992  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> update xt_curr2 set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         11  recursive calls
      20384  db block gets
         59  consistent gets
         18  physical reads
    4411724  redo size
        560  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

8 queens chess problem: solution in Oracle SQL

Posted on by Posted in curious, oracle, SQL 1 Comment

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

with 
 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d
8x8: 
SQL> @tests/f
Size[8]: 8

N
--------------------------------------------------------------------------------
a1 b5 c8 d6 e3 f7 g2 h4
a1 b6 c8 d3 e7 f4 g2 h5
a1 b7 c4 d6 e8 f2 g5 h3
a1 b7 c5 d8 e2 f4 g6 h3
a2 b4 c6 d8 e3 f1 g7 h5
a2 b5 c7 d1 e3 f8 g6 h4
a2 b5 c7 d4 e1 f8 g6 h3
a2 b6 c1 d7 e4 f8 g3 h5
a2 b6 c8 d3 e1 f4 g7 h5
a2 b7 c3 d6 e8 f5 g1 h4
a2 b7 c5 d8 e1 f4 g6 h3
a2 b8 c6 d1 e3 f5 g7 h4
a3 b1 c7 d5 e8 f2 g4 h6
a3 b5 c2 d8 e1 f7 g4 h6
a3 b5 c2 d8 e6 f4 g7 h1
a3 b5 c7 d1 e4 f2 g8 h6
a3 b5 c8 d4 e1 f7 g2 h6
a3 b6 c2 d5 e8 f1 g7 h4
a3 b6 c2 d7 e1 f4 g8 h5
a3 b6 c2 d7 e5 f1 g8 h4
a3 b6 c4 d1 e8 f5 g7 h2
a3 b6 c4 d2 e8 f5 g7 h1
a3 b6 c8 d1 e4 f7 g5 h2
a3 b6 c8 d1 e5 f7 g2 h4
a3 b6 c8 d2 e4 f1 g7 h5
a3 b7 c2 d8 e5 f1 g4 h6
a3 b7 c2 d8 e6 f4 g1 h5
a3 b8 c4 d7 e1 f6 g2 h5
a4 b1 c5 d8 e2 f7 g3 h6
a4 b1 c5 d8 e6 f3 g7 h2
a4 b2 c5 d8 e6 f1 g3 h7
a4 b2 c7 d3 e6 f8 g1 h5
a4 b2 c7 d3 e6 f8 g5 h1
a4 b2 c7 d5 e1 f8 g6 h3
a4 b2 c8 d5 e7 f1 g3 h6
a4 b2 c8 d6 e1 f3 g5 h7
a4 b6 c1 d5 e2 f8 g3 h7
a4 b6 c8 d2 e7 f1 g3 h5
a4 b6 c8 d3 e1 f7 g5 h2
a4 b7 c1 d8 e5 f2 g6 h3
a4 b7 c3 d8 e2 f5 g1 h6
a4 b7 c5 d2 e6 f1 g3 h8
a4 b7 c5 d3 e1 f6 g8 h2
a4 b8 c1 d3 e6 f2 g7 h5
a4 b8 c1 d5 e7 f2 g6 h3
a4 b8 c5 d3 e1 f7 g2 h6
a5 b1 c4 d6 e8 f2 g7 h3
a5 b1 c8 d4 e2 f7 g3 h6
a5 b1 c8 d6 e3 f7 g2 h4
a5 b2 c4 d6 e8 f3 g1 h7
a5 b2 c4 d7 e3 f8 g6 h1
a5 b2 c6 d1 e7 f4 g8 h3
a5 b2 c8 d1 e4 f7 g3 h6
a5 b3 c1 d6 e8 f2 g4 h7
a5 b3 c1 d7 e2 f8 g6 h4
a5 b3 c8 d4 e7 f1 g6 h2
a5 b7 c1 d3 e8 f6 g4 h2
a5 b7 c1 d4 e2 f8 g6 h3
a5 b7 c2 d4 e8 f1 g3 h6
a5 b7 c2 d6 e3 f1 g4 h8
a5 b7 c2 d6 e3 f1 g8 h4
a5 b7 c4 d1 e3 f8 g6 h2
a5 b8 c4 d1 e3 f6 g2 h7
a5 b8 c4 d1 e7 f2 g6 h3
a6 b1 c5 d2 e8 f3 g7 h4
a6 b2 c7 d1 e3 f5 g8 h4
a6 b2 c7 d1 e4 f8 g5 h3
a6 b3 c1 d7 e5 f8 g2 h4
a6 b3 c1 d8 e4 f2 g7 h5
a6 b3 c1 d8 e5 f2 g4 h7
a6 b3 c5 d7 e1 f4 g2 h8
a6 b3 c5 d8 e1 f4 g2 h7
a6 b3 c7 d2 e4 f8 g1 h5
a6 b3 c7 d2 e8 f5 g1 h4
a6 b3 c7 d4 e1 f8 g2 h5
a6 b4 c1 d5 e8 f2 g7 h3
a6 b4 c2 d8 e5 f7 g1 h3
a6 b4 c7 d1 e3 f5 g2 h8
a6 b4 c7 d1 e8 f2 g5 h3
a6 b8 c2 d4 e1 f7 g5 h3
a7 b1 c3 d8 e6 f4 g2 h5
a7 b2 c4 d1 e8 f5 g3 h6
a7 b2 c6 d3 e1 f4 g8 h5
a7 b3 c1 d6 e8 f5 g2 h4
a7 b3 c8 d2 e5 f1 g6 h4
a7 b4 c2 d5 e8 f1 g3 h6
a7 b4 c2 d8 e6 f1 g3 h5
a7 b5 c3 d1 e6 f8 g2 h4
a8 b2 c4 d1 e7 f5 g3 h6
a8 b2 c5 d3 e1 f7 g4 h6
a8 b3 c1 d6 e2 f5 g7 h4
a8 b4 c1 d3 e6 f2 g7 h5

92 rows selected.

[collapse]

Solution for N between 10 and 100 

with
 t as (select level i, to_char(level,'fm00') c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||to_char(i,'fm00')||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,length(c)*level-1,length(c))
                                      or L+1 + t.i = level + substr(s,length(c)*level-1,length(c))
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d

[collapse]

It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s

script for sqlplus 

set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
with 
 t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d
/
col n clear;

[collapse]

Update: Fixed the typo, thanks to Brian Fitzgerald (@ExaGridDba)

Maven: how to copy files after a build into several distribution directories

Posted on by Posted in java Leave a comment

Sometimes it is convenient to copy jar-files automatically after a build into several different directories, for example if you have different config files for local tests and for office test server, then you may want to copy these files into local test directory, internal office test server and public distribution directory.
This short part of pom.xml contains 2 different methods to build UBER-JAR and copying them into 3 different directories: localtest, officetest and public

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>tv.tmd.YourMainClass</mainClass>
                        </manifest>
                        <manifestEntries>
                            <Class-Path>.</Class-Path>
                        </manifestEntries>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <manifestEntries>
                                        <Main-Class>tv.tmd.YourMainClass</Main-Class>
                                        <Build-Number>2</Build-Number>
                                    </manifestEntries>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <artifactId>maven-antrun-plugin</artifactId>
                <version>1.8</version>
                <executions>
                    <execution>
                        <id>copy</id>
                        <phase>package</phase>
                        <configuration>
                            <target>
                                <echo>ANT TASK - copying files....</echo>
                                <copy todir="${basedir}/distribution/localtest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>

                                <copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
                                    <fileset dir="${basedir}" includes="*.bat" >
                                        <include name="*.bat" />
                                        <include name="ReadME.*" />
                                        <include name="target/*.jar" />
                                    </fileset>
                                </copy>
                            </target>
                        </configuration>
                        <goals>
                            <goal>run</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

WINDOW NOSORT STOPKEY + RANK()

Posted on by Posted in CBO, oracle, query optimizing, SQL Leave a comment

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1 

create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
            ,n
      from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;

[collapse]
Output 

         N
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

10 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk             ,n       from test) where rnk<=3

Plan hash value: 1892911073

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |     10 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW                  |         |      1 |   1000 |     10 |00:00:00.01 |       3 |       |       |          |
|*  2 |   WINDOW NOSORT STOPKEY|         |      1 |   1000 |     30 |00:00:00.01 |       3 | 73728 | 73728 |          |
|   3 |    INDEX FULL SCAN     | IX_TEST |      1 |   1000 |     31 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNK"<=3)
   2 - filter(RANK() OVER ( ORDER BY "N")<=3)

[collapse]

As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where 

DENSE_RANK<=3

but not 

RANK<=3

The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:

PLSQL STOPKEY version 

create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
      n          int
     ,max_rank   int
   ) 
   return rowids_table pipelined
as
begin
   for r in (
      select/*+ index_rs_asc(t (n))  */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
      from test t
      where t.n > get_rowids_by_rank.n
      order by n
   )
   loop
      if r.rnk <= max_rank then
         pipe row (r.chr_rowid);
      else
         exit;
      end if;
   end loop;
   return;
end;
/
select/*+ leading(r t) use_nl(t) */
   t.*
from table(get_rowids_by_rank(1, 3)) r
    ,test t
where t.rowid = chartorowid(r.column_value)
/

[collapse]
In that case the fetch from a table will stop when rnk will be larger than max_rank

Thanks to all #odevchoice voters!

Posted on by Posted in odevchoice, oracle, Oracle database developer choice award Leave a comment

And special thanks to all the great people who voted for me! 🙂
I want to list all voters for all nomenees in one page: http://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course :):

Spoiler 

with 
  finalists(category, userid, name) as (
      --                               SQL~ Voting:
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'SQL'     , 6899,  'Stew Ashton      ' from dual union all
      select 'SQL'     , 6900,  'Sean Stuber      ' from dual union all
      select 'SQL'     , 6901,  'Sayan Malakshinov' from dual union all
      select 'SQL'     , 6902,  'Matthias Rogel   ' from dual union all
      select 'SQL'     , 6903,  'Kim Berg Hansen  ' from dual union all
      select 'SQL'     , 6904,  'Justin Cave      ' from dual union all
      select 'SQL'     , 6905,  'Erik Van Roon    ' from dual union all
      select 'SQL'     , 6906,  'Emrah Mete       ' from dual union all
      --                               PL/SQL~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'PL/SQL'  , 6907,  'Sean Stuber      ' from dual union all
      select 'PL/SQL'  , 6908,  'Roger Troller    ' from dual union all
      select 'PL/SQL'  , 6909,  'Patrick Barel    ' from dual union all
      select 'PL/SQL'  , 6910,  'Morten Braten    ' from dual union all
      select 'PL/SQL'  , 6911,  'Kim Berg Hansen  ' from dual union all
      select 'PL/SQL'  , 6912,  'Bill Coulam      ' from dual union all
      select 'PL/SQL'  , 6913,  'Adrian Billington' from dual union all
      --                               ORDS ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6881,  'Tim St. Hilaire  ' from dual union all
      select 'ORDS'    , 6882,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6883,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6884,  'Dimitri Gielis   ' from dual union all
      select 'ORDS'    , 6885,  'Dietmar Aust     ' from dual union all
      select 'ORDS'    , 6886,  'Anton Nielsen    ' from dual union all
      --                               APEX ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6887,  'Trent Schafer    ' from dual union all
      select 'ORDS'    , 6888,  'Paul MacMillan   ' from dual union all
      select 'ORDS'    , 6889,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6890,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6891,  'Karen Cannell    ' from dual union all
      select 'ORDS'    , 6893,  'Juergen Schuster ' from dual union all
      select 'ORDS'    , 6894,  'Jari Laine       ' from dual union all
      --                             DatabaseDesign ~ Voting
      ------ ----------------- -------------------------------------------------------------------------------------
      select 'DbDesign', 6896,  'Michelle Kolbe   ' from dual union all
      select 'DbDesign', 6897,  'Mark Hoxey       ' from dual union all
      select 'DbDesign', 6898,  'Heli Helskyaho   ' from dual union all
      select 'DbDesign', 6919,  'Rob Lockard      ' from dual
)
select 
       f.category
      ,f.name         as Nominee
      ,row_number()over(partition by f.category,f.name 
                        order by 
                        case 
                           when regexp_like(t.column_value,'^user\d+$')                      then 2
                           when regexp_like(t.column_value,'^\d+$')                          then 3
                           when regexp_like(t.column_value,'\w{6}-\w{4}-\w{4}-\w{4}-\w{12}') then 4
                           else 1
                        end
                        ) n
      ,t.column_value as VoterName 
from finalists f,
     table(
         xt_http.get_matches(
            pUrl     => 'https://community.oracle.com/voting-history.jspa?ideaID='||to_char(f.userid,'fm0000')||'&start=0&numResults=1000'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
order by 1,2,3,4

[collapse]

Oracle package for HTTPS/HTTP[version 0.2]

Posted on by Posted in java stored procedures, odevchoice, oracle, Oracle database developer choice award, PL/SQL Leave a comment

A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:

  1. Timeout parameter – it would be better to control connection time;
  2. Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the clob many times. But within java procedure it will be just one pass.
  3. Support of plsqldoc – the tool for automatically generating documentation in HTML format.(javadoc analogue)

Upd 2015-10-11:

  1. added HttpMethod parameter – so you can choose POST or GET method
  2. added function get_last_response – returns last HTTP response code.

You can download new version from github: https://github.com/xtender/xt_http
Also it may be interesting if you want to see how to get collection of CLOBs/varchar2 from JAVA stored procedure.

So with new functions I can even more easy get UpVoters list from prevous post:

select * 
from table(
         xt_http.get_matches(
            pUrl     => '&url'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
Results 

select * 
from table(
         xt_http.get_matches(
            pUrl     => '&url'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
/
Enter value for url: https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000

NAME
--------------------------------------------------
Denes Kubicek
Pavel Luzanov
Martin Preiss
AlexAA
scherbak
TimHall
Toon Koppelaars
Dom Brooks
mweedman
BluShadow
Dmitry-Oracle
Mahir M. Quluzade
SA2
Dmitry A. Bogomolov
SQL*Plus
Alexander.Ryndin-Oracle
Mohamed Houri
Randolf Geist
ctrieb
UltraBlast
Kot Dmitriy
user9506228
Timur Akhmadeev
Franck Pachot
pudge
user12068799
user11933056
user11994768
iRAV
user12228999
nicher100
vva
Alexander Semenov
Dmitry_Nikiforov
Bud Light
user7111641
dbms_photoshop
AcidMan
achervov
GokhanAtil
user2616810
Harun Kucuksabanoglu
_Nikotin
Maki
user9066618
user10487079
IgorUsoltsev
edw_otn
Vigneswar Battu
user11198823
be here now
869219
user7543311
VladimirSitnikov
kamineff
Asmodeus
djeday84
Oleh Tyshchenko
87Rb-87Sr
911978
KoTTT
Konstantin
945154
953255
user12217223
Alexander_Anokhin-Oracle
oragraf.sql
Jack10154746
user9502569
Yury Pudovchenko
Sergey Navrotskiy
985277
xifos
MatthiasRogel
Mikhail Velikikh
user12134743
1025067
Oren Nakdimon
1051964
1094595
1209426
user11211533
user6115180
user3990689
d.nemolchev
user8925862
user11222376
user882251
user12279047
1284785
1323138
1336159
Grigory-OC
1373320
1373354
1411786
1421824
user13287062
1442254
1443436
1447180
KSDaemon
user12097700
Sergei Perminov
user3539222
Victor Osolovskiy
1560322
user13609377
user6672754
Alfredo Abate
user11315510
user12245839
1636030
user5399907
user3983717
user12276855
1744386
user9171605
ksAsmodeus
user12058508
1856219
user3214869
Trihin Pavel
1879578
1886567
user8869337
1890583
1913344
user11978061
user1438531
Menno Hoogendijk
2616420
2646629
rpc1
user4770257
user5217858
user11949728
al.netrusov
2693742
AKalugin
2721788
RZGiampaoli
2728073
2769955
2786798
2789541
BobDJ
2816036
user10123230
2831055
DBA_Hans_007
2880604
2896072
2902504
ApInvent
Pinto Das
natalka
2913706
2924025
2957186
2960288
-KE-
user5337688
3001391
3004351
oleksii.kondratenko
zeynep.samsa
Mehmet Kaplan
adbee6ae-dcb4-4c59-915b-487b33773a3f
6648e909-359d-4a32-b7bd-6cea3fd9fec3
mehmetyalcin
60e3f71b-bd5d-422c-b479-7a087d5b3827
3032511
c09042fd-15da-494b-b7b0-243ff4ceeb4b
5e087e18-4789-4923-92cb-cce149ba3072
5291adcf-242b-4f01-bbe6-b7ce44db1aac
9c0321bf-a358-49f9-88e4-462bd4fd5674
03b9266b-53f4-4503-ae04-b339c039bffd
user12044736
4a89f5a6-cc35-424e-8e3c-59964ad8d56b
54113fd4-6592-4a32-9920-663813bdd4c5
590d4b9f-32b6-43ad-b0c4-3b767407c055
ffb03087-d390-4068-98e5-4cfd73e66d00
3fa47a10-7b28-4857-9274-d175f3b7fd48
user5814569
d0b20163-7a58-4d4d-9a7d-01ec973bc3e1
8aaa1ed8-e0f6-4712-bef7-6b1e6579798f
1aee554c-832b-4fa5-bead-0680a53d1cc5
5daa756f-80aa-4260-b91d-10d2c51b78d6
e7897e6a-993d-46db-ad93-215b61b715ad
9f982eda-2b58-4d61-aad9-4c6a50d2dab7
user2503867
3032876
f3012cfb-62b5-4c86-a102-2172c3640d5b
68fe6d1e-d41c-4528-b076-ac3bc5289cc5
41232c56-5a29-442c-a1c6-d5b94477be1d
0682b6e1-8662-498c-8455-629032a25cea
user6592033
59961cb1-b4a6-470c-9802-44432911a7ff
user7345691
ab7980ce-71ba-4ec1-a578-6b716f2ae1ae
user5844404
723b639c-f6c6-4780-8ad6-0315564ef937
28e651f6-c9c3-4d2a-af03-001837eb99ba
3032942
841b96b4-ab84-461e-aed9-58f9df710406
user9961876
d7e48e5c-868f-4b2d-88b5-8614e9d35c80
3033022
f095cbb6-707a-4f40-8f18-a6a9dc37894d
3033091
9e9d3c99-9b5e-4fa2-89ac-4e6216209566
a9e702d3-f8e5-43ee-8e6c-0fe722d9ab50
bdd07d60-c467-4115-8149-8ef2af880d9a
d5571104-4726-4f06-b529-293dc667ae6e
user10865764
petrelevich
87a6503f-5717-4887-ac77-cd916002f53a
user7355088
user6083916
user2300447
user12299863
81ddc21a-7cb3-4298-a96b-ea7c9774b2c4
3405d13c-9d19-4903-8eb0-14a2544cb32b
user2427414
4670adeb-1c9d-4ce4-98eb-962bc4c68f5d
oldhook
1e4428aa-3a63-4a1a-90b3-f2b74292f502
user6367919
7c75e315-487d-4797-8e5b-f3dee58bbc79
user8828289
1a8ad4e7-759f-48ee-8054-c449540d0573
6f3b1262-c9d6-42d6-b703-fc4e6a40b7a1
037f209b-f643-4642-a059-79988d19d77b
PL99
3034106
3034166
user5489918
user2340616
naeel maqsudov
3036157
user2626322
52e8d732-4289-4d0d-b8c5-80e701f3c07d
4b2deedd-84c4-4b8b-8724-837c54dd764e
user12569643
07292d40-2bab-4e94-b68e-cfaae6c093a8
8ec394ac-fd54-4896-9810-0381bb75260e
caec3a42-0f98-440b-ad71-9522cb1e0a0e
9c52d45b-e2e4-489c-9a32-548c77f159b3
f6e966be-8576-4da9-a0df-8fec374b6cd3
5214be2e-d761-4a4e-aeba-23ff7bb4cf4e
f070b484-017d-4d4c-a740-f6ad9db37286
d4f322d3-0265-458d-948d-83bd66d5c7e3

[collapse]

Oracle Database Developer Choice Awards: Up-Voters list

Posted on by Posted in odevchoice, oracle, Oracle database developer choice award 2 Comments

It’s very easy to get and analyze voters list using my new XT_HTTP package 🙂
We can get up-voters list by the URL: 

https://community.oracle.com/voting-history.jspa?ideaID=NNNN&start=0&numResults=1000

where NNNN is Idea ID from nomenee’s page.
For example my page – https://community.oracle.com/ideas/6901 so my voters page will be https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
BTW, though this page is called “VotingHistory”, but it shows up-voters only 🙂

So we can easily get full voters list:

Very simple oracle package for HTTPS and HTTP

Posted on by Posted in java stored procedures, oracle, PL/SQL 2 Comments

I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures 🙂
github/XT_HTTP

java source: xt_http.jsp 

create or replace and compile java source named xt_http as
package org.orasql.xt_http;

import javax.net.ssl.HttpsURLConnection;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.HttpURLConnection;

import java.sql.Connection;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;
 

public class XT_HTTP {

   /**
    * Function getPage
    * @param String Page URL
    * @return String
    */
    public static CLOB getPage(java.lang.String sURL)
    throws java.sql.SQLException
     {
        OracleDriver driver = new OracleDriver();
        Connection conn     = driver.defaultConnection();
        CLOB result         = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
        result.setString(1," ");
        try {
            URL url = new URL(sURL);
            HttpURLConnection con = (HttpURLConnection)url.openConnection();
            //HttpsURLConnection con = (HttpsURLConnection)url.openConnection();
            if(con!=null){
                BufferedReader br =
                        new BufferedReader(
                                new InputStreamReader(con.getInputStream()));
                StringBuilder sb = new StringBuilder();
                String line;
                while ((line = br.readLine()) != null){
                    sb.append(line);
                }
                br.close();
                result.setString(1,sb.toString());
            }
        } catch (MalformedURLException e) {
            result.setString(1, e.getMessage());
        } catch (IOException e) {
            result.setString(1, e.getMessage());
        }
        return result;
    }
    
    public static java.lang.String getString(java.lang.String sURL) {
        String result="";
        try {
            URL url = new URL(sURL);
            HttpURLConnection con = (HttpURLConnection)url.openConnection();
            if(con!=null){
                BufferedReader br =
                        new BufferedReader(
                                new InputStreamReader(con.getInputStream()));
                StringBuilder sb = new StringBuilder();
                String line;
                while ((line = br.readLine()) != null){
                    sb.append(line);
                }
                br.close();
                result = sb.toString().substring(0,3999);
            }
        } catch (MalformedURLException e) {
            return e.getMessage();
        } catch (IOException e) {
            return e.getMessage();
        }
        return result;
    }
}
/

[collapse]

package xt_http 

create or replace package XT_HTTP is
/**
 * Get page as CLOB
 */
  function get_page(pURL varchar2)
    return clob
    IS LANGUAGE JAVA
    name 'org.orasql.xt_http.XT_HTTP.getPage(java.lang.String) return oracle.sql.CLOB';

/**
 * Get page as varchar2(max=4000 chars)
 */
  function get_string(pURL varchar2)
    return varchar2
    IS LANGUAGE JAVA
    name 'org.orasql.xt_http.XT_HTTP.getString(java.lang.String) return java.lang.String';
    
end XT_HTTP;
/

[collapse]

We have to grant connection permissions:

dbms_java.grant_permission(
   grantee           => 'XTENDER'                       -- username
 , permission_type   => 'SYS:java.net.SocketPermission' -- connection permission
 , permission_name   => 'ya.ru:443'                     -- connection address and port
 , permission_action => 'connect,resolve'               -- types
);

And now we can easily get any page:

USAGE example:
declare
  c clob;
  s varchar2(8000);
begin
  --- Through HTTPS as CLOB:
  c:=xt_http.get_page('https://google.com');

  --- Through HTTP as CLOB
  c:=xt_http.get_page('http://ya.ru');
  
  --- Through HTTPS as varchar2:
  s:=xt_http.get_string('https://google.com');

  --- Through HTTP as varchar2
  s:=xt_http.get_string('http://ya.ru');
end;
/
select length( xt_http.get_page('https://google.com') ) page_size from dual