Do it, do it. do it well

Even such a small but very useful function saves a lot of time:

procedure assert_equal(expected varchar2, actual varchar2) is
begin
    if expected is null and actual is not null then
         raise_application_error(-20001, 
              'Expected is null'||' Actual '||actual);
    elsif expected is not null and actual is  null then                       
         raise_application_error(-20001, 
               'Expected '||expected||' Actual is null');
    elsif  expected <>  actual then
         raise_application_error(-20001, 
               'Expected '||expected||' Actual '||actual);
    end if;
end;

Valid time tables

I do not get it, seems like everything should be quite the contrary.


Quoting the same Dave Ensor and Jan Stevenson. "Oracle Design"

producte_code	DATE_FROM	DATE_TO	Price
1	13.01.2007	01.02.2007	1.12
1	02.02.2007	31.10.2007	2.24
1	01.11.2007	01.01.3000	5.47

select price from prices
 where sysdate between date_from and date_to

It seems to be efficient to use index for the columns (PRODUCT_CODE, DATE_TO) since search parameter is located near the upper index bounds. However, note that on query processing still each line which has DATE_TO value grater than SYSDATE will be read. In other words, the optimizer will continue lines reading after we found the required string.

Maximum efficiency is achieved witр recent prices selecting, if column DATE_TO is indexed, and with oldest prices selecting, if DATE_FROM is indexed

Unsecure by design

Avoiding Connection String Injection Attacks
A connection string injection attack can occur when dynamic string concatenation is used to build connection strings based on user input. If the string is not validated and malicious text or characters not escaped, an attacker can potentially access sensitive data or other resources on the server. For example, an attacker could mount an attack by supplying a semicolon and appending an additional value. The connection string is parsed using a "last one wins" algorithm, so the hostile input would be substituted for a legitimate value.
msdn

And why rational escaping mechanism is not provided for every eventually? It's all the same impossible to specify any symbol in the password.

using Oracle.DataAccess.Client;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Data.OracleClient.OracleConnectionStringBuilder builder =
                new System.Data.OracleClient.OracleConnectionStringBuilder();
            builder.DataSource = "dev";
            builder.Pooling = false;
            builder.UserID = "user";
            builder.Password = "a\"';1234";
            OracleConnection connection = new OracleConnection();
            connection.ConnectionString = builder.ConnectionString;
            connection.Open();
        }
    }
}

The killer feature for oracle forms

Oracle forms have an awful implementation in themselves but the idea of trying to describe user interface development entities in terms of data base is interesting enough.

By removing redundant abstraction level we simplify the implementation, make easier the interaction between developers of different system levels. Data blocks as a model, triggers instead of events are good but the very important thing is missing. If it would be implemented the correspondence between approaches to developing data bases and gui would be full.

The matter is savepoints. IMHO they should combine with modal interfaces and the standard exceptions mechanism in a very harmonious way. And the idea of save point itself is more natural for dialogs with Cancel button than dummy copying and synchronization of data.

Parser Combinators

It's not too bad, code comes out rather beautiful, short and clear. True, for that purpose I had to quickly look into haskell syntax.

I've written a piece of tasks from the first chapter of SICP in haskell as the warm-up, read the sixth chapter of the first book of _darkus_

And by motifs I've gotten the following parser of pl/sql packages specifications:

terminal = (spaces . utoken)
    where utoken t s | lower t == lower (take n s) = [(lower (drop n s),t)]
                     | otherwise     = []
              where n = length t
                    lower = map toLower

-- Разрешенные идентификаторы
ident = spaces  ((satisfy isAlpha) <:*> zeroOrMore leastChars) <@ (map toLower)
    where leastChars = choise [satisfy isAlpha,
                               satisfy isDigit,
                               symbol '.',
                               symbol '_']

comment = single_line_comment <|> multi_line_comment
    where single_line_comment = pack (token "--") 
                                (zeroOrMore (satisfy (\_->True))) 
                                (zeroOrMore (symbol '\n'))
          multi_line_comment =  reverse . p
              where p = pack (token "/*") (zeroOrMore (satisfy (\_->True))) (token "*/")

-- Базовые типы
pls_type = choise [terminal "integer",
                   terminal "varchar2",
                   terminal "number",
                   terminal "date",
                   field_type]
    where field_type = ident <* spaces(symbol '%') <*> terminal "type"

-- Разделители
sep_semicolon = spaces (symbol ';')
sep_comma     = spaces (symbol ',')

-- Общие части выражений
def_var    = ident <*> ((param_type) <|> succeed "in") <*> spaces (pls_type)
    where param_type = choise [terminal "in",
                               terminal "out",
                               (terminal "in" <*> terminal "out") <@ (\_ -> "in out")]

def_params = spaces ((parens var_list) <|> succeed [])
    where var_list = (listOf def_var sep_comma) <|> succeed []

def_fun = ident <*> def_params 

-- Спецификации
spec_procedure = terminal "procedure" *> def_fun <*> (succeed "None") <* sep_semicolon
spec_function = terminal "function" *> def_fun <*> terminal "return" 
                *> ident <* sep_semicolon
spec_declaration = spec_procedure <|> spec_function

spec_create_package = cr_or_repl <*> (terminal "package") *> ident <*> terminal "as" 
                      *> zeroOrMore spec_declaration
                      <* terminal "end" <* sep_semicolon <* terminal "/"
    where cr_or_repl = terminal "create" <:*> option (terminal "or" *> terminal"replace")

Evidently, parser in python intends to the same approach.
Now I have to look into the system of haskell types and classes to have a possibility of digestible using the parser results.

You can't take a step without SQL

Cool problem at Project Euler - Find the sum of the only ordered set of six cyclic 4-digit numbers for which each polygonal type: triangle, square, pentagonal, hexagonal, heptagonal, and octagonal, is represented by a different number in the set.

I just felt that it is ideal for sql ... I used my brains and as a result I've got a small table and a small, absolutely dumb query.

I'd like to have a-la «implementation schedule» support in languages not relating to db.

SQL Programming Style

I've finished «SQL Programming Style» by Joe Celko. Chapter about scales and measures is great, I'll read it again.
I laughed a lot at the book pages telling what won't do. I'm fully agree with it. All negative examples I'm watching in the real life.

The author's devotion to natural keys seemed very strange to me.

Argument against it — very often the appearance of a natural key is the result of concrete stage of a workflow. When building database scheme based on natural key we implicitly specify the actions order. Business rules are changed a little - oops, on basic stages we lost the key.

But in general the book is consist as it is expected from the title of personal rules of code writing style. Too many moot points...

Oracle burrs

It will work only if to remove small Russian letter p (sounds like r)

declare
  buffer   varchar2(2000);
  parser   xmlparser.Parser;
  xmlClob  CLOB;
begin
   parser := xmlparser.newParser;
   buffer := '<?xml version="1.0" encoding="ISO-8859-5"?>'
       ||'<root>абвгдежзиклмнопрстуфхцчшщьыъэюя</root>';
   dbms_lob.createtemporary(xmlClob, TRUE);
   dbms_lob.open(xmlClob, dbms_lob.lob_readwrite);
   dbms_lob.write(xmlClob,length(buffer),1,buffer); 
   xmlparser.parseClob(parser, xmlClob);      
   xmlparser.freeParser(parser);
end;

Why should I test the code, if it works all the same?

I'm wild about it:

Almost pseudo code:

databaseAdapter.open(UID,PWD);
databaseAdapter.execute("select 2 from dual");
databaseAdapter.close();
databaseAdapter.open(UID,PWD);
databaseAdapter.execute("select 2 from dual");
databaseAdapter.close();

It falls on the second select.

PLSQL Procedure Call in Python

I had some trouble. In cx_Oracle documentation there is nothing. Ours is here.

How to call PL/SQL function returning an array in python?

result = cursor.callfunc('getFoo', [cx_Oracle.STRING, 20])