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

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

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

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

Функция LISTAGG

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

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

  Этот вопрос находится в топе вопросов на 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

  Как всем известно в 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

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

    Это скорее небольшая заметка, чем справочная информация.
    Столкнулся я с такой проблемой, что надо было мне результат табличной функции представить в в одной ячейке и отделить каждое значение запятой. Самый здравый способ сделать это - функция на 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.