Oracle in Russian

Eclipse Kepler + Tomcat 7

2014-01-04T10:29:00.002-08:00

Пост не по теме основного блога. Скорее это закладка на память.
Если при попытке добавить сервер Tomcat 7 ничего не получается, а экран New Server выглядит следующим образом (поле Server name пустое):

То надо выполнить следующие действия:

Закрыть Eclipse
В {workspace-directory}/.metadata/.plugins/org.eclipse.core.runtime/.settings удалить 2 файла:
- org.eclipse.wst.server.core.prefs
- org.eclipse.jst.server.tomcat.core.prefs
Перезапустить Eclipse

Первоисточник решения: http://stackoverflow.com/questions/14791843/eclipse-add-tomcat-7-blank-server-name

[INS-30131] Initial setup required for the execution of installer validations failed. In Oracle Linux 6.4

2013-11-10T06:32:00.003-08:00

Решил я, наконец-то, попробовать Oracle 12c. Закатал в виртуалку свежий Oracle Linux 6.4, поставил xorg + gnome, выполнил магическое комбо yum install oracle-rdbms-server-12cR1-preinstall, которое обещало мне безпроблемную установку базы данных, даже пользователю oracle изменил пароль, но, видимо, карма моя была недостаточно чиста, а индусы или кто там ещё, знали об этом заранее.

Проблема №1:
Получаем сообщение об ошибке, что нам отказано в доступе к database/install/.oui
chmod +x database/install/.oui решает эту проблему

Проблема №2:
Полная аналогия с проблемой №1, только в этот раз нет доступа к database/install/unzip
chmod +x database/install/unzip решает эту проблему

Подозреваю, что я сам виноват в этих ошибках, т.к. поленился распаковывать архивы из консоли с проставлением положенных флагов, а воспользовался гномом.

Проблема №3 (из заголовка):
[INS-30131] Initial setup required for the execution of installer validations failed.
Самое странное, что, судя по интернету, страдают от неё пользователи Widows, но никто из пользователей Linux даже не упоминал о ней.
Решение: chmod -R 777 /tmp/CVU_12.1.0.1.0_oracle/*

Больше проблем не было. Разве что теперь Oracle 12c Enterpriыe Edition хочет под себя 6,5 гигов. Поэтому виртуальные диски лучше делать по 30 гигов, а не по 20, как раньше

Ubuntu 13.04 + Bumblebee + Lenovo Y580

2013-04-21T08:30:00.000-07:00

    Не дожидаясь недели до выхода Ubuntu 13.04 решил попробовать нового зверя. В основном благодаря куче багов в 12.10 и лестным отзывам по 13.04 на LOR.
    Т.к. терять особо было нечего, да и диск надо было с виндой поделить немного иначе я остановился на чистой установке.
    Ещё на Live-USB меня напрягло то, что подсветка экрана ноута после GRUB автоматом выставляется в 0, но хорошо, что это только подсветка, на Fedora 18 с ядром 3.8.x вообще на экране ничего не отображается.
Но вернёмся в установке шмеля и радостям высокопроизводительной графики, которая кушает батарейку только тогда, когда ей это позволяют.

    Установка Bumblebee производилась по инструкции http://help.ubuntu.ru/wiki/bumblebee , но, т.к. у меня ноут с особенностями, а эти особенности на уровне ядра будут поддерживаться только начиная с 3.8.5 и выше, то для текущего убунтовского ядра 3.8.0 надо собрать модуль.
   Чотобы это сделать надо выполнить в терминале следующую последовательность комманд:

git clone git://github.com/Bumblebee-Project/bbswitch.git -b hack-lenovo
cd bbswitch/

В файле acpi-handle-hack.c заменить код функции dev_set_acpi_handle с:

static void dev_set_acpi_handle(struct pci_dev *pdev, acpi_handle handle) {
        pdev->dev.archdata.acpi_handle = handle;
}

на:

static void dev_set_acpi_handle(struct pci_dev *pdev, acpi_handle handle) {
   #if LINUX_VERSION_CODE >= KERNEL_VERSION(3,8,0)
       pdev->dev.acpi_node.handle = handle;
   #else
       pdev->dev.archdata.acpi_handle = handle;
   #endif
}

mkdir /usr/src/acpi-handle-hack-0.0.2
sudo cp Makefile acpi-handle-hack.c /usr/src/acpi-handle-hack-0.0.2/
sudo cp dkms/acpi-handle-hack.conf /usr/src/acpi-handle-hack-0.0.2/dkms.conf
sudo dkms add acpi-handle-hack/0.0.2
sudo dkms build acpi-handle-hack/0.0.2
sudo dkms install acpi-handle-hack/0.0.2
sudo echo acpi-handle-hack | sudo tee -a /etc/modules
sudo update-initramfs -u

Перезагрузить компьютер, либо выйти и войти.
Епосле всех этих манипуляций Optirun не заработал, надо в файле /etc/bumblebee/xorg.conf.nvidia добавить:

BusID "PCI:01:00:0"

в Section "Device", где 01:00:0 - id вашей дискретной видеокарты, который можно узнать выполнив команд lspci.

Glassfish Admin Console freezing at startup

2013-02-22T04:22:00.001-08:00

If GlassFish Admin Console freezing at loading, and load after switch off all network interfaces, you need start it with option: -Djava.net.preferIPv4Stack=true

How to add start option:

Start server:
./asadmin start-domain
Add option:
./asadmin create-jvm-options -Djava.net.preferIPv4Stack=true

Everything work fine now :)

Glassfish Admin Console зависает при загрузке

2013-02-22T04:17:00.000-08:00

Если консоль администратора GlassFish не запускается, а если отключить все сетевые соединения стартует, то его надо запускать с опцией: -Djava.net.preferIPv4Stack=true

Делается это так:

Запускаем сервер:
./asadmin start-domain
Добавляем опцию:
./asadmin create-jvm-options -Djava.net.preferIPv4Stack=true

Теперь всё будет запускаться без проблем :)

Установка Oracle SQL Developer и Oracle SQL Developer Data Modeller на *.deb дистрибутив

2013-02-18T07:41:00.000-08:00

В связи с переездом на новый ноутбук на который была поставлена не кошерная Ubuntu взамен кошерного Debian, который продолжает крутиться на старом. Мне пришлось проходить всё круги ада по настройке системы под разработку снова. В связи с этим родилась эта заметка.

Итак, в данный момент Oracle SQL Developer и Oracle SQL Developer Data Modeller существуют в виде rpm-пакетов и zip-архивов (только Developer).
Я предпочитаю конвертировать софт из rpm в deb и устанавливать его при помощи менеджера пакетов.
Качаем rpm`ки по ссылкам:

Oracle SQL Developer (http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html)
Oracle SQL Developer Data Modeller (http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html)

Устанавливаем alien, если он ещё не установлен:

    apt-get install alien

Натравливаем alien на rpm пакет:

    alien sqldeveloper-3.2.20.09.87-1.noarch.rpm

В результате получаем deb-пакет, который можем установить в свою систему:

    dpkg -i sqldeveloper_3.2.20.09.87-2_all.deb

Т.к. большинство популярных десктоп дистрибутивов успешно перешли на Gnome 3 и новую систему меню, то скорее всего в меню программ свежеустановленный софт не появится. Для этого нам необходимо создать desktop-файл, который необходимо поместить в ~/.local/share/applications

Пример cсодержимого файла для SQL Developer:

[Desktop Entry]
Encoding=UTF-8
Name=SQL Developer
Comment=Oracle SQL Developer
Icon=/opt/sqldeveloper/icon.png
Exec=sqldeveloper
Terminal=false
Type=Application
Categories=Development

Пример содержимого файла для Data Modeller:

[Desktop Entry]
Encoding=UTF-8
Name=Oracle Data Modeler
Comment=Oracle Data Modeler
Icon=/opt/datamodeler/icon.png
Exec=datamodeler
Terminal=false
Type=Application
Categories=Development

Я подготовил архив с уже готовыми файлами для меню, с файлом-иконкой высокого разрешения для SQL Developer и скриптом, который может положить всё необходимые файлы в нужные места за вас. Главное дайте текущему пользователю права на запись в папку /opt/sqldeveloper, либо запускайте от имени root. Скрипт называется install_menu.sh. Инструкция по пользованию скриптом находится в нём же.
Архив можно скачать по ссылке: https://mega.co.nz/#!Q8NxCKTT!BK2ynySNbJRwvb3ba-yMq2IhL0JDw7V7BKYA_zb3ecI (10 KB)

Важно!!!
Первый запуск приложения должен быть произведён из консоли, т.к. программа попросит указать месторасположение JVM.

Внешние таблицы + препроцессор

2012-10-21T06:13:00.003-07:00

Эту заметку пишу в качестве узелка на память.
Дело в ом, что я уже довольно давно знаю о существовании в Oracle внешних таблиц. Очень удобного средства для перекидывания данных из CVS файлов в базу. Но только сегодня я узнал о существовании в Oracle 11.2 препроцессора, который может подготавливать данные для внешней таблицы посредством выполнения каких-либо программ на машине, где работает сервер базы данных.
Ниже даю ссылки на описание работы с внешними таблицами и документ, в котором на основании простых примеров рассказывается как пользоваться препроцессором:
Внешние таблицы
Использование препроцессора с внешними таблицами

Функция LISTAGG

2012-09-11T02:20:00.003-07:00

Чтобы "вытянуть" значение нескольких строк одной колонки в одну строку в Oracle 11.2 не обязательно придумывать хитрые иерархические запросы. Достаточно воспользоваться функцией LISTAGG.
Вот её описание в документации Oracle: http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

2012-08-15T22:44:00.004-07:00

This is a copy of http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?

Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.This fact is documented application developers guide:

Privileges Required to Create Procedures and FunctionsTo create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:
• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.
Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.
If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.

</quote>Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i. See the PLSQL documentation for more information on this feature and make sure you understand the ramifications). To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.

Почему PL/SQL в коде (представлении/процедуре/триггере/пакете) невозможно использовать чужие объекты. Roles and ORA-00942 table or view does not exist

2012-08-15T22:32:00.003-07:00

Этот вопрос находится в топе вопросов на SQL.RU по СУБД Oracle.
Краткий ответ такой, потому что грант на эти объекты выдан через роль.
Ответ в документации по Oracle: "Потмучто в именованном PL/SQL блоке все роли отключаются"
Чуть более развёрнутый ответ приведён на самом SQL.RU: Почему в своем pl/sql-коде (представлении/процедуре/триггере/пакете) не удается использовать чужой объект - выдается ошибка ORA-00942 table or view does not exist - хотя в sql-запросе или в анонимном pl/sql/блоке все нормально, обращение к объекту другого пользователя проходит без проблем?
Ответ. Скорее всего на чужой объект есть привилегия, выданная только через роль. Для использования объектов другого пользователя или системных привелегий в своих процедурах/триггерах и т.д., а также в job-ах необходимо дать на них привилегию напрямую, не через роль.
Так же это правило следует учесть для динамического SQL: FAQ: ORA-01031: insufficient privileges при выполнении динамического SQL в процедурах (по этой ссылке приводится ещё более развёрнутый ответ)

Ну и самый подробный ответ на этот вопрос нам предлагает ASKTOM.ORACLE.COM.

Привожу перепечатку этого ответа, мало ли он исчезнет:

You Asked

Hi Tom,

We are using 2 schemas, user1, user2.
User1 has user1_table, and created role role1. The following was issued:
grant select, insert, update, delete on user1_table to role1;
grant role1 to user2
User2 created private synonym user1_table for user1.user1_table
and tried to create :

create or replace procedure aa as
aa user1_table.column%TYPE;
begin
null;
end;
/
Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE AA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4      PLS-00201: identifier 'USER1.USER1_TABLE' must be declared
2/4      PL/SQL: Item ignored

Why user2 cannot access user1_table in his procedure, if he has rights on the this table 
via role1?
We do not want to grant  user1_table to user2 directly.

Thanks,

Dusan

and we said...

See

http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

You have no choice but to grant select on user1_table to user2 directly.  It is the only 
way to make it work.

Why is it that roles are not used during the compilation of a procedure?  It has to do 
with the dependency model.  Oracle is not storing exactly WHY you are allowed to access T 
¡V only that you are (directly able to -- not via a role).  Any change to your privileges 
that might cause access to T to go away will cause the procedure to become invalid and 
necessitate its recompilation.  Without roles ¡V that means only ¡§REVOKE SELECT ANY 
TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC.  With roles 
¡V it greatly expands the number of times we would invalidate this procedure.  If some 
role that was granted to some role that was granted to this user was modified, this 
procedure might go invalid, even if we did not rely on that privilege from that role.  
ROLES are designed to be very fluid when compared to GRANTS given to users as far as 
privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored 
objects.  Now, most any time anything was revoked from ANY ROLE we had, or any role any 
role we have has (and so on -- roles can and are granted to roles) -- many of our objects 
would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly 
your entire database must be recompiled! 

Consider the impact of revoking some system privilege from a ROLE, it would be like doing 
that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful 
system privilege from PUBLIC, do it on a test database).  Revoking SELECT ANY TABLE from 
PUBLIC for example would cause virtually every procedure in the database to go invalid.  
If procedures relied on roles ¡V virtually every procedure in the database would 
constantly become invalid due to small changes in permissions.  Since one of the major 
benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be 
disastrous for performance.

Also consider that roles may be

á Non-default: If I have a non-default role and I enable it and I compile a procedure 
that relies on those privileges, when I log out I no longer have that role -- should my 
procedure become invalid -- why? Why not? I could easily argue both sides.

á Password Protected: if someone changes the password on a ROLE, should everything that 
might need that role be recompiled?  I might be granted that role but not knowing the new 
password ¡V I can no longer enable it. Should the privileges still be available?  Why or 
Why not?  Again, arguing either side of this is easy.  There are cases for and against 
each.  

The bottom line with respect to roles in procedures with definers rights are:

á You have thousands or tens of thousands of end users. They don't create stored objects 
(they should not). We need roles to manage these people.  Roles are designed for these 
people (end users).

á You have far fewer application schema's (things that hold stored objects). For these 
we want to be explicit as to exactly what privileges we need and why.  In security terms 
this is called the concept of 'least privileges' -- you want to specifically say what 
privilege you need and why you need it. If you inherit lots of privileges from roles you 
cannot do that effectively. We can manage to be explicit since the number of development 
schemas is SMALL (but the number of end users is large)...

á Having the direct relationship between the definer and the procedure makes for a much 
more efficient database.  We recompile objects only when we need to, not when we might 
need to.  It is a large efficiency enhancement.

Чудесная функция COALESCE

2012-08-09T21:44:00.000-07:00

Как всем известно в Oracle SQL есть замечательная функция NVL, которая позволяет вывести результат "по умолчанию", в случае если значение колонки равно NULL.
На протяжении всей своей оракловой жизни я успешно пользовался этой функцией, жил не тужил, хотя нет, иногда мне становилось грустно, когда мне надо было вывести в колонку значение которой надо было взять из других колонок, при этом оно не должно было быть NULL, если все колонки равны NULL, то надо было вывести значение по умолчанию. Обычно эту задачу я решал при помощи CASE, но это требовало много сил и писанины. А вчера произошло чудо, оказывается есть замечательная функция COALESCE, которая замечательно заменяет этот CASE, ниже привожу её синтаксис и пример из документации по Oracle.

COALESCE (expr1, expr2, ..., exprn), for n>=3

Пример:

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050
   ORDER BY product_id, list_price, min_price, "Sale";

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5



Синтаксис и пример взяты документации по SQL СУБД Oracle

Как поместить в одну ячейку значение нескольких столбцов

2012-04-27T05:32:00.001-07:00

Это скорее небольшая заметка, чем справочная информация.
Столкнулся я с такой проблемой, что надо было мне результат табличной функции представить в в одной ячейке и отделить каждое значение запятой. Самый здравый способ сделать это - функция на PL/SQL, но мы не ищем лёгких путей. Поэтому получилось вот такое:

select text
from (select ltrim(sys_connect_by_path(col, ', '), ', ') as text, CONNECT_BY_ISLEAF is_leaf
from (select <имя колонки, для вытягивания в строку> as col,
rownum as up_rnum,
rownum+1 as rnum
from table(<сюда помещаем нашу табличную функцию>)
)
start with up_rnum = 1
connect by prior rnum = up_rnum)
where is_leaf = 1

UPD:
Оказывается всё уже придумано до нас :)))
На просторах интернета нашёл статью, в которой приведено достаточно способов для вытягивания значения нескольких строк в одну ячейку. Исходник статьи здесь: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Самый "огонь", аналитическая функция, которая появилась в 11gR2 - LISTAGG

Пример её использования:

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

DBMS_ROWID

2011-12-26T21:13:00.000-08:00

В Oracle существует замечательный пакет DBMS_ROWID, а замечателен он тем, что по rowid строки может рассказать всякую информацию о нет, в том числе и сведения, о том, в какой таблице она находится.

Подробности о функциях, содержащихся в пакете можно прочитать по ссылкам: Oracle Doc и PSOUG
Есть тонкость в использовании самой ходовой, для меня, функции из этого пакета:

     DBMS_ROWID.ROWID_OBJECT (rowid_id IN ROWID) RETURN NUMBER;

    Данная функция возвращает Id объекта, который содержит наш rowid, самым не очевидным моментом связанным с этой функцией является то, обстоятельство, что object id, который она возвращает для таблиц (по крайней мере в Oracle 11g) надо искать в  user_objects или all_objects, не в поле object_id, а поле data_object_id.

    Этот не самый очевидный момент заставлял меня несколько раз прерывать программирование и лазить в дебрях БД.

Oracle Database 11g: Новые возможности в PL/SQL

2011-10-31T23:42:00.000-07:00

Для людей желающих прочитать про плюшки, которые есть в Oracle 11g рекомендую почитать этот документ. В нём представлен список новых фишек, которые есть в Oracle 11g

Oracle Database 11g: Новые возможности в PL/SQL

Также есть замечательный вебсайт, но он на английском, где также можно прочитать о новых возможностях разных версий:

oracle-developer.net

Динамический SQL

2011-10-31T06:38:00.000-07:00

Порой случается, что надо чтобы код выполнял запрос, который заранее точно не известен, для решения таких задач в СУБД Oracle существует 3 способа решения данной задачи.
Здесь я только перечислю те решения, которыми мне доводилось пользоваться, подробности можно почерпнуть из документации Oracle.

EXECUTE IMMEDIATE
Наиболее часто используемый мной подход. После имени этой команды подаём строку с SQL запросом, которая немедленно выполняется. Для того, чтобы нас не считали неграмотными программистами используем bind переменные, которы позволяют избежать многократного распарсивания запроса. Также из EXECUTE IMMEDIATE можно возвращать данные при помощи INTO и BULK COLLECT INTO.
Подробная документация находится по этой ссылке: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#BHCEBBAI
OPEN FOR
Конструкция, которой я пользуюсь очень редко, т.к. обычно хватает способа под цифрой 1. По сути - это курсор, даже не знаю как его правильно назвать, явным или неявный. Дело заключается в том, что для того, чтобы воспользоваться данной командой необходимо создать курсорную переменную типа REF CURSOR. Далее работаем как и с обычными курсорами.
Вот пример:
declare
type t_cursor REF CURSOR;
cursor t_sursor;
num number;
begin
OPEN cursor FOR 'select 1 from dual';
fetch cursor into num;
close cursor;
end;
Пакет DBMS_SQL
Пакет очень мощный, но в тоже время не очевидный, чтобы с ним разобраться рекомендую почитать не только документацию по адресу: http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_sql.htm , но и сайт на русском языке: http://osql.ru/?p=111
Рекомендую обратить особое внимание на функции TO_CURSOR_NUMBER и TO_REFCURSOR , они помогают конвертировать курсор из обычного формата в DBMS_SQL формат и обратно.

Primary key и триггер

2011-10-20T23:42:00.000-07:00

Поскольку с триггерами у меня мягко говоря не очень, а корпоративная политика гласит: "Триггерами пользоваться сугубо в исключительных ситуациях, когда без них не обойтись" - то изучать их в рабочем процессе не получается.
Но вопрос: "Как сделать автоинкремент primary key?" - меня не отпускал, то я решил найденное решение записать в блог.

Итак, нам понядобятся:

Таблица:
create table test (id number, testdata varchar2(255));
Последовательность: create sequence test_seq start with 1 increment by 1 nomaxvalue;
И собственно говоря триггер, который будет создавать нам значение ключа: create trigger test_trigger before insert on test for each row begin select test_seq.nextval into :new.id from dual; end;

После создания всех этих объектов нам достаточно выполнить: insert into test(testdata) values('voila!');

И наши данные будут без проблем добавлены.
Текст примеров взять с: http://jen.fluxcapacitor.net/geek/autoincr.html

Индекс порождающий виртуальную колонку

2011-10-18T01:16:00.000-07:00

Я, да и не только я, всегда думал что всё что связано с индексами хранится где-то в недрах базы данных и Oracle скрывает от непосвящённых эти данные, но всё оказалось не так просто.
Только что наткнулся на очень интересную штуковину - виртуальную колонку, которую я не создавал. Когда начал разбираться выяснил, что индекс на базе функции может добавить в таблицу колонку, о существовании которой вы даже не догадывались.
В моём случае на одну из колонок был повешен индекс такого вида:

CREATE INDEX IE3_ADDR_DATA ON ADDR_DATA (ADDR_ID ASC, LOWER(VALUE) ASC)
После этого при запросе к словарю данных user_tab_cols видим колонку SYS_NC00007$, которая в поле DATA_DEFAULT содержит LOWER("VALUE") и полу VIRTUAL_COLUMN хранится значение YES.
Будьте осторожны при чтении словарей данных и их использовании в обычной жизни.

Сбор статистики по схеме

2011-08-31T00:47:00.000-07:00

execute dbms_stats.gather_schema_stats (ownname =>'USR',cascade => TRUE);

Помимо GATHER_SCHEMA_STATS, собирающей статистику по всей схеме, можно собирать статистику по отдельной таблице, индексу и т.п. Это может быть полезно, если требуется ускорить конкретный запрос (можно быстро собрать статистику только по конкретным таблицам). Вот полный список процедур сбора статистики из пакета DBMS_STAT:

GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS
GATHER_SYSTEM_STATS

Взято с : http://oraclenotes.ru/?p=65

Индекс для контекстного поиска на основе функции

2011-06-09T23:29:00.000-07:00

В Oracle есть замечательная возможность осуществлять контекстный поиск по тексту, который в явном виде не хранится в таблице. Для того, чтобы воспользоваться этой возможностью необходимо написать функцию, которая по rowid целевой таблицы построит соответсвующую ей текстовую строку строку.

Прототип данной функции, или скорее процедуры, т.к. это процедура выглядит так:

procedure ctx_foo(p_rid in rowid, p_ctx_text in out nocopy varchar2);

p_rid - rowid таблицы, в которой находятся данные, которые мы будем искать

p_ctx_text - текст, который мы проиндексируем контекстным индексом для дальнейшего применения функции CONTAINS.

Далее надо настроить контекстный индекс при помощи функций: создать perference в словаре Oracle при помощи процедуры ctx_ddl.create_preference и установить его атрибуты при помощи процедуры ctx_ddl.set_attribute.

Теперь создаём контексный индекс, на любую колонку тип которой: VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, URIType.

create index <index_name> on <table_name(column_name) indextype is ctxsys.context parameters('<params>')

Подробности про параметры можно прочитать по ссылке: http://download.oracle.com/docs/cd/E11882_01/text.112/e16594/ind.htm#i1006887

Ниже я приведу пример создания контекстного индекса, у котрого строка для индексации генерируется процедурой.

Создаём таблицы и заполняем их исходными данными.

create table ctx_source(

grp number,

word varchar2(32),

pos number);

insert into ctx_source values (1, 'Oracle', 1);

insert into ctx_source values (1, 'database', 2);

insert into ctx_source values (1, '11g', 3);

insert into ctx_source values (2, 'Mary', 1);

insert into ctx_source values (2, 'had', 2);

insert into ctx_source values (2, 'a', 3);

insert into ctx_source values (2, 'little', 4);

insert into ctx_source values (2, 'lamb', 5);

insert into ctx_source values (3, 'Lamb', 1);

insert into ctx_source values (3, 'with', 2);

insert into ctx_source values (3, 'sauce', 3);

create table ctx_foo (

id number,

ctx_text varchar2(1));

insert into ctx_foo(id) values (1);

insert into ctx_foo(id) values (2);

insert into ctx_foo(id) values (3);

Создаём пакеты с функциями

create or replace

package ctx as

procedure ctx_foo(p_rid in rowid, p_ctx_text in out nocopy varchar2);

function print_foo(p_id in number) return varchar2;

end ctx;

create or replace

package body ctx as

procedure ctx_foo(p_rid in rowid, p_ctx_text in out nocopy varchar2)

begin

for r in (select word

from ctx_source

where grp = (select id from ctx_foo where rowid = p_rid)

order by pos)

loop

p_ctx_text := p_ctx_text || r.word || ' ';

end loop;

p_ctx_text := rtrim(p_ctx_text);

end ctx_foo;

function print_foo(p_id in number) return varchar2

l_return varchar2(256);

begin

for r in (select word

from ctx_source

where grp = p_id

order by pos)

loop

l_return := l_return || r.word || ' ';

end loop;

return rtrim(l_return);

end print_foo;

end ctx;

Создаём perference

exec ctx_ddl.create_preference( 'foo_datastore', 'user_datastore' );

exec ctx_ddl.set_attribute('foo_datastore', 'procedure', 'ctx.ctx_foo');

exec ctx_ddl.set_attribute('foo_datastore', 'output_type', 'varchar2');

Создаём индекс на основе нашего perference

create index index_ctx_foo on ctx_foo(ctx_text) indextype is ctxsys.context parameters( 'datastore foo_datastore sync (on commit)');

/*sync (on commit) необходимо, чтобы индекс обновлялся автоматически при втавке новых данных, этот параметр не обязательный*/

Тестируем

select id, ctx.print_foo(id) from ctx_foo where contains(ctx_text,'lamb') > 0;

id ctx.print_foo(id)

------------------------

2 Mary had a little lamb

3 Lamb with sauce

select id, ctx.print_foo(id) from ctx_foo where contains(ctx_text,'oracle') > 0;

id ctx.print_foo(id)

------------------------

1 Oracle database 11g

Желаю успешно использовать в своей работе контекстный индексы. У Oracle есть целый гайд работе с Oracle Text, прочитать его можно по адресу: http://download.oracle.com/docs/cd/E11882_01/text.112/e16594/toc.htm

Контекстный поиск

2011-06-01T07:44:00.001-07:00

Как-то столкнулся я с новой для меня задачей, реализовтаь контектный поиск внутри Oracle.
На русском языке Google сходу выдаёт одну статью, которая конечно помогла мне, но в ней не было по шагам написано что надо сделать, чтобы всё заработало. поэтому здесь я будет краткая справка о том что делать и куда бежать. По сути - это будет пересказ оракловой статьи - Getting Started with Oracle Text.

Добавляем пользователю, который будет работать с пакетом ctx_ddl роль ctxapp.GRANT ctxapp TO myuser;
Дать нашему пользователю гранты на выполнение функций, которые находятся в пакетах, которые относятся к Oracle Text. Следующий код даст гранты на все пакеты OT.
GRANT EXECUTE ON CTXSYS.CTX_CLS TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_THES TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO myuser;

Подготовка окончена, теперь переходим к созданию таблиц и заполнения их данными и строим индекс.

Создаём таблицу:REATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
Вставляем в таблицу данные:INSERT INTO docs VALUES(1, '<HTML>California is a state in the US.</HTML>');
INSERT INTO docs VALUES(2, '<HTML>Paris is a city in France.</HTML>');
INSERT INTO docs VALUES(3, '<HTML>France is in Europe.</HTML>');
Строим индекс:CREATE INDEX idx_docs ON docs(text)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

   Теперь выполняем запрос:
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;

SCORE(1)         ID TEXT
---------- ---------- ----------------------------------------
        4          3 <HTML>France is in Europe.</HTML>
        4          2 <HTML>Paris is a city in France.</HTML>
   Хочу заметить, что данный пример покрывает только наипростейший контекстный индекс и контекстный поиск. В следующем посте я напишу как создавать контекстный индекс, основанный на функции. Это гораздо полезней.

NetworkManager не включает беспроводную сетевую карту

2011-05-27T05:27:00.000-07:00

Этот пост не совсем про Oracle а про Fedora Linux, на основе которой строится Red Hat Enterprise Linux на базе которого строится Oracle Linux, на котором корпорация Oracle рекомендует разворачивать свою СУБД.
Вобщем симптомы проблемы следующие: после загрузки беспроводная сетевая карта не активна, попытки её включить ни к чему не приводят, она всё также остаётся в состоянии disable, а интерфейс в down, по dmesg мы видим следующее:
[ 7632.355105] iwlagn 0000:03:00.0: PCI INT A disabled
  [ 7681.733032] iwlagn: Intel(R) Wireless WiFi Link AGN driver for Linux, in-tree:d
  [ 7681.733035] iwlagn: Copyright(c) 2003-2010 Intel Corporation
  [ 7681.733114] iwlagn 0000:03:00.0: PCI INT A -> GSI 17 (level, low) -> IRQ 17
  [ 7681.733124] iwlagn 0000:03:00.0: setting latency timer to 64
  [ 7681.733239] iwlagn 0000:03:00.0: Detected Intel(R) WiFi Link 5100 AGN, REV=0x54
  [ 7681.754617] iwlagn 0000:03:00.0: device EEPROM VER=0x11f, CALIB=0x4
  [ 7681.754619] iwlagn 0000:03:00.0: Device SKU: 0Xb
  [ 7681.754643] iwlagn 0000:03:00.0: Tunable channels: 13 802.11bg, 24 802.11a channels
  [ 7681.754719] iwlagn 0000:03:00.0: irq 46 for MSI/MSI-X
  [ 7681.759905] iwlagn 0000:03:00.0: loaded firmware version 8.83.5.1 build 33692
  [ 7681.760899] ieee80211 phy1: Selected rate control algorithm 'iwl-agn-rs'
  [ 7717.265118] ADDRCONF(NETDEV_UP): wlan0: link is not ready
  [ 7718.948245] ADDRCONF(NETDEV_UP): wlan0: link is not ready

Причина проблемы в модуле ядра acer_wmi, его надо отправить в blacklist и всё чудесным образом заработает.

If Network Manager do not enable your wireless card and you dmesg log look like log on top, just insert module "acer_wmi" into blacklist.

Расширенные функции Oracle 10g по работе со стеком ошибок

2011-05-24T11:50:00.000-07:00

В Oracle 10g возможности для работы со стеком ошибок были расширены. В пакете DBMS_UTILITY появились функции, которые позволяют получить больше информации о том, как ведёт себя программа в случае возникновения исключительной ситуации.

Вот эти функции:

FORMAT_ERROR_BACKTRACE - позволяет получить строку из программы, в которой произошла исключительная ситуация.
FORMAT_CALL_STACK - отображает на экране стэк программы на момент возникновения исключения.
FORMAT_ERROR_STACK - позволяет получить “полное” сообщение об ошибке (дело в том, что привычная всем функция SQLERRM имеет ограничение на длину выводимого текста 512 символов, новая функция имеет ограничение в 2000 символов).

  Самый простой пример, который демонстрирует работу этих функций выглядит так:
   DECLARE
     local_exception EXCEPTION;
     FUNCTION nested_local_function
     RETURN BOOLEAN IS
       retval BOOLEAN := FALSE;
     BEGIN
       RAISE local_exception;
       RETURN retval;
     END;
     BEGIN
       IF nested_local_function THEN
         dbms_output.put_line('No raised exception');
       END IF;
     EXCEPTION
       WHEN others THEN
         dbms_output.put_line('DBMS_UTILITY.FORMAT_CALL_STACK');
         dbms_output.put_line('------------------------------');
         dbms_output.put_line(dbms_utility.format_call_stack);
         dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
         dbms_output.put_line('-----------------------------------');
         dbms_output.put_line(dbms_utility.format_error_backtrace);
         dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_STACK');
         dbms_output.put_line('-------------------------------');
         dbms_output.put_line(dbms_utility.format_error_stack);
     END;

   Если его выполнить, то получим:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object    line     object
handle    number   name
20909240      18   anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception

Скорее всего, самой востребованной функцией будет - DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, рекомендую ознакомиться с более интересным примеров стека ошибок на сайте документации по Oracle 10g: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#i1003874

PIVOT и UNPIVOT в Oracle 11g

2011-05-19T05:12:00.000-07:00

В Oracle 11g появились новые функции, которые позволяют разворачивать таблицу. Здесь я приведу пример для быстрого понимания того, как это работает, в конце статьи дам ссылку на чуть более подробное объяснение того как это работает.

PIVOT берёт данные из строк, аггрегирует их и возвращает в виде столбцов, иначе говоря, строит сводную таблицу.
   В качестве примера берём классику жанра, таблицу emp из схемы scott
   Сделаем разворот этой таблицы, чтобы получить сумму зарплат разбитую по должностям и департаментам:
   Строим таблицу суммы заплат по должностям и департаментам, которую потмо будем разворачивать
SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno

JOB DEPTNO SAL
--------- ---------- ----------
MANAGER 20 2975
PRESIDENT 10   5000
CLERK 10   1300
SALESMAN 30 5600
ANALYST 20 6000
MANAGER 30 2850
MANAGER 10 2450
CLERK 30 950
CLERK 20 1900

Внешний запрос строит сводную таблицу по должностям и департаментам, внутри функции PIVOT, надо указать аггрегирующую функцию, и значения столбцов, по которым надо будет группировать данные
SELECT *
FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno)
PIVOT ( sum(sal) FOR deptno IN (10, 20, 30, 40) );

JOB 10 20 30 40

--------- ---------- ---------- ---------- ----------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000

   UNPIVOT преобразует данные, разбитые по колонкам в данные разбитые по строкам.
Вот пример её работы:
with t as (
select 'Oracle EE' as product, 100 as Q1, 123 as Q2, 130 as Q3, 128 as Q4 from dual
union all
select 'Partitioning', 100, 123, 130, 128 from dual
)

SELECT * FROM t
UNPIVOT INCLUDE NULLS (quantity_sold FOR quarter IN (Q1, Q2, Q3, Q4));

PRODUCT                        QUARTER QUANTITY_SOLD
------------------------------ ------- -------------
Oracle EE                      Q1           100
Oracle EE                      Q2           123
Oracle EE                      Q3           130
Oracle EE                      Q4           128
Partitioning                   Q1           100
Partitioning                   Q2           123
Partitioning                   Q3           130
Partitioning                   Q4           128

   Для работы функции UNPIVOT требуется указать имена колонок, которые будут заполнена значениями из строк в нашем случае это quantity_sold и quarter, колоки, которые будут развёрнуты в строки указываются после IN это: Q1, Q2, Q3, Q4
   Больше информации по этим функциям можно прочитать: http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php и http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE

Обращение к читателям

2011-05-18T10:40:00.000-07:00

Уважаемые читатели моего блога, если у вас есть какие-то вопросы по SQL или PL/SQL, то задавайте их в комментариях к любому посту или пишите мне на почту queer82@gmail.com.
В мире программирования под Oracle есть много вопросов и проблем, так давайте находить решения на них вместе, это поможет нам узнать что-то новое.

Конвейер из табличных (pipelined) функций

2011-05-17T22:23:00.000-07:00

Как-то раньше мне не приходилось сталкиваться с задачей, когда приходилось внутри одной табличной функции запрашивать данные из другой табличной функции в передавать их выше, но в конце концов этот момент настал.
Мне было необходимо перегрузить табличную функцию, а поскольку алгоритм обработки данных полностью повторял уже существующий я решил воспользоваться вызовом табличной функции.
Вроде бы всё просто, делай селект типа:
for foo in (select * from pipelined_function(x))
loop
pipe row (r.a, r.b, r.c ...);
end loop;
Но мне хотелось больше удобства, не хотел я писать кучу этих имён в pipe row, и начались поиски решения. Слава богу знаний хватило на то, чтобы прийти вот к такому решению:

Создаём типы:
create or replace type tr_mixed as object (num number, str varchar2(15));
create or replace type tt_mixed is table of tr_mixed;
Создаём pipelined функцию, из которой в последствии будем получать данные:
create or replace function pipe_func(p_iter number)
return tt_mixed
pipelined
is
begin
for l_index in 1 .. p_iter
loop
pipe row (tr_mixed(l_index, 'str-' || l_index));
end loop;
end pipe_func;
А теперь создаём pipelined функцию, которая будет запрашивать данные из функции выше:
create or replace
function pipe_func_ext
return tt_mixed
pipelined
is
l_temp_tt tt_mixed;
begin
select cast(multiset (select * from table(pipe_func(10))) as tt_mixed)
into l_temp_tt
from dual;
for l_index in l_temp_tt.first .. l_temp_tt.last
loop
pipe row (l_temp_tt(l_index));
end loop;
end pipe_func_ext;
Вся магия заключается в псевдофункции MULTISET, которая используется только 4как аргумент псевдофункции CAST.Она позволяет извлечь из базы данных набор значений и тут же преобразовать его в коллекцию нужного типа. Синтаксис у неё такой:
select cast (multiset (select поле from таблица) as тип_коллекции) from dual
Если всё равно не понятно, то код:
select cast(multiset (select * from table(pipe_func(10))) as tt_mixed)

into l_temp_tt

from dual;

можно представить в виде:

select tr_number(num, str)
bulk collect into l_temp_tt
from table(pipe_func(10));
Только весь кайф заключается в том, что с multiset, можно писать *
Проверяем, что всё работает:
select * from table(pipe_func_ext);