A\B U B\A

Dlaczego taki dziwny tytuł - dla ćwiczeń chciałem sobie napisać zapytanie wyświetlające różnicę między tabelą A i B.

Dajmy na to, że mamy tabele A z liczbami parzystymi:

 SELECT  LEVEL * 2  AS n  
            FROM DUAL
            CONNECT BY LEVEL <= 100

oraz tabele B z liczbami podzielnymi przez 3:

SELECT  LEVEL * 3  AS n  
            FROM DUAL
            CONNECT BY LEVEL <= 100

Teraz spróbujemy wyciągnąć różnice między tablą A i B. Całe zapytanie wygląda następująco:

WITH 
A AS
    (
         SELECT  LEVEL * 2  AS n  
            FROM DUAL
            CONNECT BY LEVEL <= 100
       
    ),  
B AS
     (
         SELECT  LEVEL * 3  AS n  
            FROM DUAL
            CONNECT BY LEVEL <= 100
       )
    

(SELECT 'a' tab ,n FROM A
MINUS
SELECT 'a' tab ,n FROM B)
UNION ALL
(SELECT 'b' tab ,n FROM B
MINUS
SELECT 'b' tab ,n FROM A)
ORDER by n 
;

Wynik takiego zapytania jest przedstawiony poniżej:

Brakujące dni w Oraclu

Któryś raz piszę to samo zapytanie, więc postanowiłem zapisać to rozwiązanie na blogu. Może Ci się przyda - mi na pewno :)

Przypuśćmy, że masz tabelę (o nazwie tableWithDate), która ma kolumnę z datą (dateToCheck). Ładujesz dane i zapisujesz dzień na które dane zostały załadowane. Na końcu ładowania danych powinieneś mieć wypełnioną tabelę z dla każdego dnia od 1 stycznia 2014. Chciałbyś sprawdzić dla jakich dni nie zostały załadowane dane.

Pomocne nam będą funkcje do operowania na dacie. Same zapytanie wygląda następująco:

WITH dates as (
    SELECT TRUNC(to_date('1/1/2014','DD/MM/YYYY')) -1 +  LEVEL AS d
    FROM DUAL
    CONNECT BY LEVEL <= 365
    )

SELECT  
d As "Date", 
to_char(d, 'DDD') As "DayOfYear",
to_char(d, 'Day') as "DayOfWeek", 
to_char(d, 'WW') As "WeekOfYear",
to_char(d, 'Month') As "MonthName", 
to_char(d, 'RM') As "Roman" ,
to_char(d, 'Q') As "Quarter"

FROM dates d
where  NOT  EXISTS (
    SELECT  tab.dateToCheck
    from tableWithDate tab
    where to_char(d.d, 'YYYYMMDD')  = to_char(tab.dateToCheck, 'YYYYMMDD')
    group by tab.dateToCheck
)
ORDER BY d
;

Do wyświetlenia daty pomocna nam będzie funkcja to_char z formatowaniem. Jeżeli nie będziemy mieli załadowanych danych to wynik zapytania będzie wyglądał następująco:

Jeżeli załadujemy dane i w śród tych danych nie będzie danych dla dni: 1-3 i 6 listopada, to tak będzie wyglądał wynik zapytania:

Help dla oracla w PS

Co najbardziej mnie denerwuje w oraclu to ilość błędów jakie dostaje ze względu na infrastrukturę lub złożone ustawienia oracla. Natomiast te błędy są całkiem dobrze udokumentowane m.in dzięki portalowi ora-code. Napisałem skrypcik do pobierania informacji o błędach z takiej strony. Skrypt znajduje się w module PS do Oracle, przy którym ostatnio rozwijam.
Skrypt jest podany poniżej:

function Get-OracleHelp
{
    param(
    [ValidateNotNullOrEmpty()]
    [Alias('code')]
    [string]$errorCode
    )

    [ref]$errorNumber=0
    $isNumber = [int]::TryParse($errorCode, $errorNumber)
    if($isNumber)
    {
        $errorCode = 'ORA-{0:00000}' -f $errorNumber.Value
    }

    $errorCode = $errorCode.ToUpper()

    $url = "http://{0}.ora-code.com/" -f $errorCode


    $request = Invoke-WebRequest -Uri $url  -UseDefaultCredentials

    $table = $request.ParsedHtml.body.getElementsByTagName('table')

    $trs = $table | %{$_.getElementsByTagName('tr')} | ? {$_.getAttributeNode('valign').value -eq 'top' }

    $desc=''
    $cause=''
    $action=''

    $trs | %{
        [string]$text = $_.innerText

         if( $text.StartsWith($errorCode, 'CurrentCultureIgnoreCase'))
         {
            $desc = $text.Remove(0, $errorCode.Length+1)
         }
 
         if( $text.StartsWith('Cause', 'CurrentCultureIgnoreCase'))
         {
            $cause = $text.Remove(0, 'Cause'.Length+1)
         }
         
         if( $text.StartsWith('Action', 'CurrentCultureIgnoreCase'))
         {
            $action = $text.Remove(0, 'Action'.Length+1)
         }
    }
    
    return  new-object PSObject `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Code' -Value $errorCode `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Description' -Value $desc `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Cause' -Value $cause `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Action' -Value $action `
        | Add-Member -MemberType NoteProperty -PassThru -Name 'Url' -Value $url 
}

Wywołanie wygląda następująco:

Get-OracleHelp -errorCode ORA-01243

I dostajemy taki opis błędu:

Oprócz całego kodu błedu to można wpisać tylko numer błędu:

Get-OracleHelp -code 12154

Dynamiczne parametry funkcji w PS ( dynamicParam )

ValidateSet jest fantastycznym atrybutem do parametrów funkcji. Umożliwia podpowiadanie argumentów oraz nie dopuszcza zmienianie wartości. Ma jednak jedną wadę. dozwolone wartości musza być stałymi przedefiniowanymi. Jeżeli chcielibyśmy zwalidować dane wejściowe, które ilość oraz wartości są zmienne to nie mamy takiej możliwości. Z pomocą przychodzi nam dynamiczny parametr. Całkowicie zmienia składnie funkcji. Przykład wywołania dynamicznego parametru jest poniżej. Przypuśćmy, że mamy hashtable z dozwolonymi nazwami oraz ich wartościami

$dparamColl = @{
param1 = 'test';
param2 = 'abc';
param3 = 'xyz'
}

function function_name
{
    param(
     $param
   )

    dynamicParam {

       $attributes = new-object System.Management.Automation.ParameterAttribute
       $attributes.ParameterSetName = "__AllParameterSets" 
       $attributes.Mandatory = $true
       $attributeCollection =      new-object -Type System.Collections.ObjectModel.Collection[System.Attribute]
       $attributeCollection.Add($attributes)
       $_Values  = $dparamColl.Keys
       $ValidateSet =      new-object System.Management.Automation.ValidateSetAttribute($_Values)
       $attributeCollection.Add($ValidateSet)
       $dynParam1 = new-object -Type System.Management.Automation.RuntimeDefinedParameter( "dparam", [string], $attributeCollection)
       $paramDictionary = new-object -Type System.Management.Automation.RuntimeDefinedParameterDictionary
       $paramDictionary.Add("dparam", $dynParam1)
       return $paramDictionary
    }

    begin{}
    process{
      return $dparam
    }
    end{}
}

Można stworzyć osobną funkcję, która będzie zwracała RuntimeDefinedParameterDictionary z dynamicznymi parametrami:

function Get-DynamicParam
{
    param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [array]$paramSet, 
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$paramName)

        $attributes = new-object System.Management.Automation.ParameterAttribute
        $attributes.ParameterSetName = "__AllParameterSets" 
        $attributes.Mandatory = $true
        $attributeCollection =      new-object -Type System.Collections.ObjectModel.Collection[System.Attribute]
        $attributeCollection.Add($attributes)
        $_Values  = $paramSet
        $ValidateSet =      new-object System.Management.Automation.ValidateSetAttribute($_Values)
        $attributeCollection.Add($ValidateSet)
        $dynParam1 = new-object -Type System.Management.Automation.RuntimeDefinedParameter( $paramName, [string], $attributeCollection)
        $paramDictionary = new-object -Type System.Management.Automation.RuntimeDefinedParameterDictionary
        $paramDictionary.Add($paramName, $dynParam1)
        return $paramDictionary
}

Dynamiczny parametr pierwszy raz zastosowałem jak pisałem moduł do Oracla. W jednej zmiennej przetrzymuje zapytania sql, a funkcja z parametrem dynamicznym wywołuje to zapytanie sql:

$sqlHelpQueryCollection = @{
DatabaseName = @'
select ora_database_name from dual
'@;

Instance = @'
select * from v$instance
'@;
#and many more
}


function Invoke-OracleSqlHelpQuery
{
    [CmdletBinding()]
    param(
    $conn
    )
    dynamicParam {
        return Get-DynamicParam -paramSet $sqlHelpQueryCollection.Keys -paramName 'sqlHelpQuery'
    }

    begin{}
    process{
    $sql = $sqlHelpQueryCollection[$sqlHelpQuery]
    return Get-OracleDataTable -conn $conn -sql $sql
    }
    end{}
}

Efekt dynamicznego parametru jest przedstawiony poniżej:

Cały kod modułu jest na githubie.

PowerShell moduł do Oracle

Zacząłem pisać ps moduł do bazy danych w Oraclu. Kod źródłowy jest na githubie.

Na samym początku należy zainicjalizować moduł.

Load-OracleAssemblyes  'path_to_oracle_dataacces_dir\Oracle.DataAccess.dll'

Będziemy mogli przeglądać hasła oraz przeglądać połączenia w TNS:

$secPass, $pass = Get-Password

Get-TnsOracleConnectionString | Out-GridView

Można pobrać połączenie z app.configu oraz sprawdzić wersję servera:

$connectStr = Get-ConfigConnectionString 'path_to_app_dir\App.config' DatabaseNr1

Get-OracleServerVersion $connectStr

A wywoływanie zapytania SQL jest bardzo proste. Poniżej jest przykład zapisania do csv wszystkich obiektów w bazie danych, które stworzyliśmy dla schematu 'my':

$conn = new-OracleConnection $connectStr
$conn.Open()
$dbObj =Get-OracleDataTable -conn $conn -sql "select * from all_objects where owner like 'my' order by last_ddl_time desc"
$conn.Close()

$dbObj | Export-Csv -Path 'LastChangedObjects.csv'

Oczywiście można wywołać polecenie z pliku, gdzie znajduję się zapytanie sql:

Get-OracleDataTable -file file.sql -conn $conn

Gdy będziemy przeglądać rekordy w bardzo dużej tabeli to będziemy mogli uzyskać wyjątek z brakiem pamięci:

Błąd powyższy odnosi się do braku pamięci:
#out-lineoutput : Exception of type 'System.OutOfMemoryException' was thrown.
# + CategoryInfo : NotSpecified: (:) [out-lineoutput], OutOfMemoryException
# + FullyQualifiedErrorId : System.OutOfMemoryException,Microsoft.PowerShell.Commands.OutLineOutputCommand

Wtedy zamiast ładować wszystko do adaptera to można odczytywać z DataReader'a:

$reader = Get-OracleDataReader  -conn $conn -sql "select * from all_objects"

while($reader.Reader())
{
 # operacja na reader
}

Dodatkową funkcjonalnością jest porównywanie tabel dla różnych baz danych. Poniżej jest przykład sprawdzenia czy na 2 bazach są takie same tabele z takim samym właścicielem (owner) i z taką samą ilością rekordów:

$connStr1 = 'Data Source=...'
$connStr2 = 'Data Source=...'

$conn1 = New-OracleConnection  $connStr1
$conn2 = New-OracleConnection  $connStr2

$allTables1  = Get-OracleSystemTables -conn $conn1 -SystemTable ALL_TABLES
$allTables2  = Get-OracleSystemTables -conn $conn2 -SystemTable ALL_TABLES

Compare-Object 
($allTables1 | select TABLE_NAME,OWNER,NUM_ROWS)  
($allTables2 | select TABLE_NAME,OWNER,NUM_ROWS)  -IncludeEqual 

Pseudorekurencja w Oracle

Spróbowałem moich sił w oracle w wywołaniach rekurencyjnych. Skorzystałem z klauzury WITH. Tak na prawdę nie są to wywołanie rekurencyjne. Poniżej przedstawiam przykład wyświetlania wartości silni:

with 
Factorial (n,fact) as (
select 
0 as n,
1 as fact
from dual

union all

select 
n +1, (n+1)*fact
from Factorial
where n < 20
)
select * from Factorial 
;

A tutaj przykład wyliczenia liczby ruchów z postaci jawnej dla problemu wieży Hanoi:

with 
hanoi  (n,counts) as (
select 
1 as n,
1 as counts
from dual

union all

select 
n +1, POWER(2,n+1) -1
from hanoi 
where n < 20
)
select * from hanoi  
;

Wśród standardowych przykładów rekurencji zawsze musi wystąpić ciąg Fibonacciego. Zapytanie wyświetlające liczbę oraz wartość fibonacciego dla tej liczb, wygląda następująco:

with 
fibonacci (n,fib, fibadd) as (
select 
0 as n,
0 as fib,
1 as fibadd
from dual

union all

select 
n +1, fibadd, (fib + fibadd)
from fibonacci
where n < 100
)

select n,fib from fibonacci 
;

Jak pewnie zauważyłeś, to nie tworzysz modelu rekurencyjnego z warunkami stopu, ale model iteracyjny z warunkami początkowymi.

Kalendarz w oracle

Z racji tego, że nowy rok się zbliża, postanowiłem napisać zapytanie, które zwraca dni w bieżącym miesiącu - taki kalendarzyk dla danego miesiąca. Poniżej jest przedstawiony kod. Proszę zauważyć, że pierwszy dzień tygodnia zaczyna się w niedzielę. Dla innej kultury będzie inne zapytanie.

with x
      as (
    select *
      from (
    select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
           to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
           to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
           to_char(sysdate,'mm') mth
      from dual
     connect by level <= 31
           )
     where curr_mth = mth
    )
    select max(case dw when 2 then dm end) Mo,
           max(case dw when 3 then dm end) Tu,
           max(case dw when 4 then dm end) We,
           max(case dw when 5 then dm end) Th,
           max(case dw when 6 then dm end) Fr,
           max(case dw when 7 then dm end) Sa,
           max(case dw when 1 then dm end) Su
      from x
     group by wk
     order by wk
;

Dla miesiąca grudzień mamy taki wynik:

Niestety dla ostatnich dni roku (30 i 31 grudnia) mamy problem ze standaryzacją numerów miesiąca w roku. Dla tych dwóch ostatnich dni, numer tygodnia w roku jest 1 dla parametru IW (więcej o parametrach daty znajdują się w funkcji to_char). Będziemy musieli skorzystać z parametru WW.

select *
      from (
    select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'ww')) wn,
           to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
           to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
           to_char(sysdate,'mm') mth
      from dual
     connect by level <= 31
           )
     where curr_mth = mth
;

I na końcu wystarczy zgrupować

with x
      as (
    select *
      from (
    select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'ww')) wn,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'dd')) dm,
           to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
           to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
           to_char(sysdate,'mm') mth
      from dual
     connect by level <= 31
           )
     where curr_mth = mth
    )
    select 
           --wk,
           --max(wn) wna,
           max(case dw when 2 then dm end) Mo,
           max(case dw when 3 then dm end) Tu,
           max(case dw when 4 then dm end) We,
           max(case dw when 5 then dm end) Th,
           max(case dw when 6 then dm end) Fr,
           max(case dw when 7 then dm end) Sa,
           max(case dw when 1 then dm end) Su
      from x
     group by wk
     order by max(wn)
;

A rezultat takiego zapytania wygląda następująco: