Thanks to all #odevchoice voters!

And special thanks to all the great people who voted for me! :)
I want to list all voters for all nomenees in one page:

The query (using xt_http of course :) )

  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  (more...)

Oracle package for HTTPS/HTTP[version 0.2]

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 (more...)

Oracle Database Developer Choice Awards: Up-Voters list

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:

where NNNN is Idea ID from nomenee’s page.
For example my page – so my voters page will be
BTW, though this page is called “VotingHistory”, but it shows up-voters only :)

So we can easily get full voters list:

--  (more...)

Very simple oracle package for HTTPS and HTTP

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 :)

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



This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

RESULT_CACHE: run-time dependency tracking

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2
   res varchar2(30);
   execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res;
   return res;
create table a as select 'a' a from dual;
create table b as select 'b' b from dual;
create view v_ab as select a,b from a,b;


And (more...)

A function gets called twice if the result_cache is used

Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But (more...)

SQL*Plus tips #7: How to find the current script directory

You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t (more...)

Little quiz: Ordering/Grouping – Quess the output

How many times have you guessed the right answer? :)

select * from dual order by -1;
select * from dual order by 0;


select *                   from dual                                     order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;


select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2  (more...)

12c: Little test of “TABLE ACCESS INMEMORY FULL” with count stopkey

The table has 9M rows:

SQL> with function f return int is
  2       begin
  3          for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop
  4             dbms_output.put_line(r.value);
  5             return r.value;
  6          end loop;
  7       end;
  8  select f() from t_inmemory where rownum<=1
  9  ;
 10  /


1 row selected.

SQL> /


1 row selected.

SQL> /