APPS-ORACLE.RU

Получаем список значений профиля на разных уровнях используя скрипт SQLPlus.

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40

select p.profile_option_name short_name,
       n.user_profile_option_name NAME,
       decode(v.level_id,
              10001,
              'Site',
              10002,
              'Application',
              10003,
              'Responsibility',
              10004,
              'User',
              10005,
              'Server',
              10006,
              'Org',
              10007,
              decode(to_char(v.level_value2),
                     '-1',
                     'Responsibility',
                     decode(to_char(v.level_value),
                            '-1',
                            'Server',
                            'Server+Resp')),
              'UnDef') level_set,
       decode(to_char(v.level_id),
              '10001',
              '',
              '10002',
              app.application_short_name,
              '10003',
              rsp.responsibility_key,
              '10004',
              usr.user_name,
              '10005',
              svr.node_name,
              '10006',
              org.name,
              '10007',
              decode(to_char(v.level_value2),
                     '-1',
                     rsp.responsibility_key,
                     decode(to_char(v.level_value),
                            '-1',
                            (select node_name
                               from fnd_nodes
                              where node_id = v.level_value2),
                            (select node_name
                               from fnd_nodes
                              where node_id = v.level_value2) || '-' ||
                            rsp.responsibility_key)),
              'UnDef') "CONTEXT",
       v.profile_option_value VALUE
  from fnd_profile_options       p,
       fnd_profile_option_values v,
       fnd_profile_options_tl    n,
       fnd_user                  usr,
       fnd_application           app,
       fnd_responsibility        rsp,
       fnd_nodes                 svr,
       hr_operating_units        org
 where p.profile_option_id = v.profile_option_id(+)
   and p.profile_option_name = n.profile_option_name
   and upper(p.profile_option_name) in
       (select profile_option_name
          from fnd_profile_options_tl
         where upper(user_profile_option_name) like upper('%&user_profile_name%'))
   and usr.user_id(+) = v.level_value
   and rsp.application_id(+) = v.level_value_application_id
   and rsp.responsibility_id(+) = v.level_value
   and app.application_id(+) = v.level_value
   and svr.node_id(+) = v.level_value
   and org.organization_id(+) = v.level_value
 order by short_name, user_profile_option_name, level_id, level_set;

-- пример вывода данных
SQL> 

SHORT_NAME       NAME                            LEVEL_SET       CONTEXT         VALUE
---------------- ------------------------------- --------------- --------------- -------
AFLOG_LEVEL      FND: Debug Log Level            Site                            6
AFLOG_LEVEL      FND: Debug Log Level            User            USERNAME1       6
AFLOG_LEVEL      FND: Debug Log Level            User            USERNAME2       1
AFLOG_LEVEL      БОП: уровень журнала отладки    Site                            6
AFLOG_LEVEL      БОП: уровень журнала отладки    User            USERNAME1       6
AFLOG_LEVEL      БОП: уровень журнала отладки    User            USERNAME2       1

6 rows selected

Oracle 11G

Что такое LOB

Large Objects (LOB) это тип данных используемый для хранения больших объектов — различные форматы текстов, изображения, видео, звуковые файлы. Использование LOB для хранения данных позволяет эффективно манипулировать данными в приложении.

Компоненты LOB

LOB состоит из локатора и значения.

Локатор – это внутренний указатель на фактическое значение большого объекта.

Значение – это реальное содержимое объекта.

LOB хранит локатор в таблице и данные в различных сегментах, за исключением случая,  когда размер данных меньше 4000 байт.

Максимальный размер LOB составляет ( 4 GB (4 294 967 295 байт) — 1 ) *  (значение CHUNK parameter of LOB storage); размер может достигать до 128 терабайт.

Читать дальше про “Большие объекты (LOB) в ORACLE” »

SQL Query Result Cache

Для явного указания, что запрос необходимо кэшировать используем хинт result_cache.

select /*+ RESULT_CACHE */
       p.prod_category,
       sum(s.amount_sold) revenue
  from products p, sales s
 where s.prod_id = p.prod_id
       and s.time_id between to_date('01-JAN-2006', 'dd-MON-yyyy')
                             to_date('31-DEC-2006', 'dd-MON-yyyy')
 group by rollup(p.prod_category);

------------------------------------------------------------------
| Id  | Operation                   | Name                       |
------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |
|   1 |  RESULT CACHE               | fz6cm4jbpcwh48wcyk60m7qypu |
|   2 |   SORT GROUP BY ROLLUP      |                            |
|*  3 |    HASH JOIN                |                            |
|   4 |     PARTITION RANGE ITERATOR|                            |
|*  5 |      TABLE ACCESS FULL      | SALES                      |
|   6 |     VIEW                    | index$_join$_001           |
|*  7 |      HASH JOIN              |                            |
|   8 |       INDEX FAST FULL SCAN  | PRODUCTS_PK                |
|   9 |       INDEX FAST FULL SCAN  | PRODUCTS_PROD_CAT_IX       |
------------------------------------------------------------------

Читать дальше про “Result Cache” »

Массив переменной длины

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

Использование массива переменной длины в SQL

Создаем тип

CREATE TYPE XX_TYPE_ORDERS IS VARRAY(5) OF VARCHAR2(100);

Создаем таблицу

CREATE TABLE XX_ORDERS (ORDER_ID NUMBER,ORDER_NAME XX_TYPE_ORDERS);

Вставляем строки в таблицу

INSERT INTO XX_ORDERS VALUES (1, XX_TYPE_ORDERS('Order 1','Order 2','Order 3'));
INSERT INTO XX_ORDERS VALUES (2, XX_TYPE_ORDERS('Order 21','Order 22','Order 23'));

Читать дальше про “Массив переменной длины (VARRAY)” »

Ассоциативный массив это наиболее часто используемый вид коллекции. Его не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам.

Пример  1: INDEX BY PLS_INTEGER

SQL> set serveroutput on size unlimited;
SQL> DECLARE
  2   TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3   cost XX_COST_TYPE;
  4   l_res NUMBER;
  5  BEGIN
  6   cost(1) := 5 ;
  7   cost(20) := 10 ;
  8   cost(12) := 15 ;
  9   l_res := cost(1) + cost(20) + cost(12);
 10   DBMS_OUTPUT.PUT_LINE(l_res);
 11  END;
 12  /

30

PL/SQL procedure successfully completed

Пример  2: INDEX BY VARCHAR2

SQL> DECLARE
  2   TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
  3   cost XX_COST_TYPE;
  4   L_SELECTED VARCHAR2(100) := 'CHAIR';
  5  BEGIN
  6   cost('TABLE') := 5000;
  7   cost('CHAIR') := 84020 ;
  8   cost('LAMP')  := 8300;
  9   cost('PENCIL'):= 110;
 10   DBMS_OUTPUT.PUT_LINE(cost(L_SELECTED));
 11  END;
 12  /

84020

PL/SQL procedure successfully completed

Ограничения:

Нельзя создать тип ассоциативного массива на уровне схемы:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE TYPE xx_test_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER
  2  /

Warning: Type created with compilation errors
Предупреждение: Тип создан с ошибками компиляции.

SQL> show errors
Ошибки для TYPE XX_TEST_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/22     PLS-00355: использование таблицы pl/sql не разрешается в этом
         контексте

Вложенные таблицы

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

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

Наиболее важным отличием между вложенными и индексированными таблицами является то, что вложенные таблицы являются постоянной формой коллекции.

Свойства вложенных таблиц

Читать дальше про “Вложенные таблицы (Nested Tables)” »

Коллекции

Коллекцией называется упорядоченная группа элементов одного типа.

Язык PL/SQL поддерживает три вида коллекций

• вложенные таблицы (nested tables);
• ассоциативные массивы (associative arrays);
• массивы переменной длины (variable-size arrays).

Плюсы использования коллекций

• Кеширование статичной информации
• Отслеживание элементов данных для спец.обработки
• Хранение списков непосредственно в столбцах таблицы

Какой тип коллекций лучше использовать

Псевдофункции коллекций

Читать дальше про “Коллекции в PL/SQL” »

Используя regexp_substr, получим дробные числа из строки.

SELECT regexp_substr(str, '([[:digit:]]+[.]{1}[[:digit:]]+)+', 1, level) str
FROM (
      SELECT ' 5646.45, 45 sd eds,.sd 9 2 566.11 12.3' str FROM dual) t
CONNECT BY 
 regexp_substr(str,'([[:digit:]][.]{1}[[:digit:]])+',1, level)  is not null;

STR
---------------------------------------
5646.45
566.11
12.3

Узнать кто активно грузит базу большим количеством курсоров, можно следующим запросом

select a.value,
       s.program,
       s.username,
       s.sid,
       s.serial#,
       s.client_identifier
from v$sesstat  a, 
     v$statname b, 
     v$session  s
where a.statistic# = b.statistic#
      and s.sid = a.sid
      and b.name = 'opened cursors current'
order by 1 desc;

Список проектов

select * from pa.pa_projects_all;

Просмотр позиций поставки

select * from pa.pa_proj_elements t where t.OBJECT_type = 'PA_DELIVERABLES';
select * from pa_deliverables_v;
select * from oke_deliverables_b where source_code = 'PA';

Просмотр типов позиций поставки

select * from pa.pa_task_types t where t.object_type = 'PA_DLVR_TYPES';

Просмотр статусов позиций поставки

select * from pa.pa_project_statuses s
 where 1 = 1
       and s.status_type = 'DELIVERABLE'
       and trunc(sysdate) between nvl(s.start_date_active, trunc(sysdate)) and
                                  nvl(s.end_date_active, trunc(sysdate));

Список задач

select * from pa.pa_proj_elements t where t.OBJECT_type = 'PA_TASKS';

Типы задач

select * from PA_TASK_TYPES t where object_type = 'PA_TASKS';

Типы проектов

select * from PA_PROJECT_TYPES_ALL t;

Статьи затрат

select * from pa_expenditure_types_desc_v;