Grudzień 2018 – WILAMOWSKI.IT

Klucz obcy odwołujący się do tej samej tabeli

23 grudnia 2018

00:02

Leave a comment on Klucz obcy odwołujący się do tej samej tabeli

Co zrobić w sytuacji gdy w tabeli Pracownicy masz mieć zawartą informacje o przełożonym tej osoby? Przeciez przełożony jest też pracownikiem. Nie trzeba tworzyć zawiłych redundatnych konstrukcji, z pomocą przychodzi rekurencja – możemy odwołać się do tej samej encji.

CREATE TABLE pracownik(
ID NUMBER(4) CONSTRAINT PR_PK PRIMARY KEY,
IMIE VARCHAR2(50) not null,
NAZWISKO VARCHAR2(30) not null,
ETAT VARCHAR(10) CONSTRAINT PR_ETAT_FK REFERENCES ETAT(NAZWA),
SZEF NUMBER(4) CONSTRAINT PR_PR_FK REFERENCES PRACOWNIK(NUMER),
);

CREATE TABLE pracownik(

ID NUMBER(4) CONSTRAINT PR_PK PRIMARY KEY,

IMIE VARCHAR2(50) not null,

NAZWISKO VARCHAR2(30) not null,

ETAT VARCHAR(10) CONSTRAINT PR_ETAT_FK REFERENCES ETAT(NAZWA),

SZEF NUMBER(4) CONSTRAINT PR_PR_FK REFERENCES PRACOWNIK(NUMER),

);

Analiza relacji tabel

by arek

21 grudnia 2018

16:50

Leave a comment on Analiza relacji tabel

SQL i PL/SQL

odwrotna inżynieria

Mały tool wyświetlający referencje do podrzędneych tabelu. Select wykonany w Developerze wyświetli okienko wprowadzenia zmiennej (‚&tableOfInterest’) dla nazwy tabeli parent. Przydatny do szukania relacji, analizy struktury bazy.
Szybsza opcja niz szukanie w GUI po foreign key, ale raczej do wspomagania niz jako substytut Data Modelera 🙂

1. Mamy FK sprawdzmy do których PK odwoluja sie:

select
 a.constraint_type cons_type
,a.table_name child_table
,a.constraint_name child_cons
,b.table_name parent_table
,b.constraint_name parent_cons
,b.constraint_type cons_type
from dba_constraints a
 ,dba_constraints b
where a.owner = upper('&owner')
and a.table_name = upper('&table_name')
and a.constraint_type = 'R' 
and a.r_owner = b.owner
and a.r_constraint_name = b.constraint_name;

select

a.constraint_type cons_type

,a.table_name child_table

,a.constraint_name child_cons

,b.table_name parent_table

,b.constraint_name parent_cons

,b.constraint_type cons_type

from dba_constraints a

,dba_constraints b

where a.owner = upper('&owner')

and a.table_name = upper('&table_name')

and a.constraint_type = 'R'

and a.r_owner = b.owner

and a.r_constraint_name = b.constraint_name;

2. I odwrotnie. Mamy PK i chemy znalezc tabele, ktore sie do niego odwoluja.

select
 b.table_name primary_key_table
 ,a.table_name fk_child_table
 ,a.constraint_name fk_child_table_constraint
from dba_constraints a
 ,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner')
and b.table_name = upper('&table_name');

select

b.table_name primary_key_table

,a.table_name fk_child_table

,a.constraint_name fk_child_table_constraint

from dba_constraints a

,dba_constraints b

where a.r_constraint_name = b.constraint_name

and a.r_owner = b.owner

and a.constraint_type = 'R'

and b.owner = upper('&table_owner')

and b.table_name = upper('&table_name');

Klucze główne w Oracle, autoinkrementacja oraz funkcja sys_guid

by arek

16 grudnia 2018

13:16

Leave a comment on Klucze główne w Oracle, autoinkrementacja oraz funkcja sys_guid

Oracle

Primary Key, Projektowanie baz, SQL

W odróżnieniu od innych baz, w Oraclu do wersji 11g, do tworzenia inkrementacji klucza głównego nie mogliśmy użyć zwyczajnie autonumeracji jak na poniższym przykładzie w MySQL

CREATE TABLE USERS (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
);

CREATE TABLE USERS (

ID int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

);

Zamiast tego trzeba było stosować wyzwalacz, który pobierał wartość z sekwencji.

CREATE TABLE user(
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE user ADD (
  CONSTRAINT user_pk PRIMARY KEY (ID));

CREATE SEQUENCE user_seq START WITH 1;
/

CREATE TABLE user(

ID NUMBER(10) NOT NULL,

DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE user ADD (

CONSTRAINT user_pk PRIMARY KEY (ID));

CREATE SEQUENCE user_seq START WITH 1;

CREATE OR REPLACE TRIGGER user_biu
BEFORE INSERT ON user
FOR EACH ROW

BEGIN
  SELECT user_bir.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

CREATE OR REPLACE TRIGGER user_biu

BEFORE INSERT ON user

FOR EACH ROW

BEGIN

SELECT user_bir.NEXTVAL

INTO :new.id

FROM dual;

END;

Lata pózniej w wersji 12c wprowadzono możliwość określenia takiej właściwości przy tworzeniu tabeli, w prostszy sposób.

CREATE TABLE tabela(
id		NUMBER GENERATED ALWAYS AS IDENTITY,
descripiton	VARCHAR2(30)
);

CREATE TABLE tabela(

id NUMBER GENERATED ALWAYS AS IDENTITY,

descripiton VARCHAR2(30)

);

Funkcja sys_guid.

W niektórych przypadkach przydatna może być funkcja sys_guid generująca i zwracająca globalny, unikalny ID (RAW, do 16 bajtów). Składa się z id hosta, procesu lub id wątku oraz niepowtarzalnej wartości. Nie jest to inkrementacja tzn. nie są to kolejne „user-friendly” liczby, a wartości heksadecymalne (vide wynik przykładu 1. poniżej). Poza tym może być używana jako wartość domyślna w CREATE TABLE (DEFAULT)

CREATE TABLE tabela (
  primary_key_column raw(16) DEFAULT sys_guid() primary key,
)

CREATE TABLE tabela (

primary_key_column raw(16) DEFAULT sys_guid() primary key,

)

Przykłady sys_guid():
1. Aktualizacja kolumny.

ALTER TABLE locations ADD (uid_col RAW(32));
UPDATE locations SET uid_col = SYS_GUID();
SELECT uid_col FROM locations;

ALTER TABLE locations ADD (uid_col RAW(32));

UPDATE locations SET uid_col = SYS_GUID();

SELECT uid_col FROM locations;

Wynik:

UID_COL
—————————————-
7CD5B7769DF75CEFE034080020825436
7CD5B7769DF85CEFE034080020825436
7CD5B7769DF95CEFE034080020825436
7CD5B7769DFA5CEFE034080020825436
…

2. Zastosowanie przy uzyciu wyzwalacza

CREATE OR REPLACE TRIGGER team_members_biu
    BEFORE INSERT OR UPDATE
    on team_members
    for each row
BEGIN
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    :new.username := upper(:new.username);
END team_members_biu;
/

CREATE OR REPLACE TRIGGER team_members_biu

BEFORE INSERT OR UPDATE

on team_members

for each row

BEGIN

if :new.id is null then

:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

end if;

:new.username := upper(:new.username);

END team_members_biu;

Oracle Apex 5: odblokowanie admina bez @apxchpwd

by arek

9 grudnia 2018

18:00

Leave a comment on Oracle Apex 5: odblokowanie admina bez @apxchpwd

Blog IT

Uruchamiamy SQLPLUS as sysdba.
1. Sprawdzamy id:

select user_id from APEX_050000.WWV_FLOW_FND_USER where user_name = 'ADMIN' order by last_update_date desc;

1	select user_id from APEX_050000.WWV_FLOW_FND_USER where user_name = 'ADMIN' order by last_update_date desc;

2. Zmieniamy hasło.

update APEX_050000.WWV_FLOW_FND_USER
set web_password = 'hasło'
where user_name = 'ADMIN'
and user_id = 56502607595642;
commit;

update APEX_050000.WWV_FLOW_FND_USER

set web_password = 'hasło'

where user_name = 'ADMIN'

and user_id = 56502607595642;

commit;

3. Odblokowujemy konto.

alter session set current_schema = APEX_050000;
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
commit;
end;

alter session set current_schema = APEX_050000;

begin

wwv_flow_security.g_security_group_id := 10;

wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');

commit;

end;

SQLPLUS: Command not found

by arek

1 grudnia 2018

20:27

Leave a comment on SQLPLUS: Command not found

Oracle

Przyczyną może być brak ustawionych zmienych.

Kopiujemy je z instalacji Oracle za pomocą…

<span class="hljs-built_in">echo</span> <span class="hljs-string">". /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh"</span> &gt;&gt; /etc/bashrc
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

1 2	<span class="hljs-built_in">echo</span> <span class="hljs-string">". /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh"</span> >> /etc/bashrc . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

lub
Uruchamiamy narzedzie:
/usr/bin/oraenv
Ewentualnie:

export ORACLE_SID=XE 
export ORAENV_ASK=NO
export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE
. /opt/oracle/product/18c/dbhomeXE/bin/oraenv
PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=XE

export ORAENV_ASK=NO

export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE

. /opt/oracle/product/18c/dbhomeXE/bin/oraenv

PATH=$PATH:$ORACLE_HOME/bin

Funkcja sys_guid.

Przykłady sys_guid(): 1. Aktualizacja kolumny.

Przykłady sys_guid():
1. Aktualizacja kolumny.