Архив

Публикации с меткой ‘SQL’

SQL Склонение количества ошибок

9 Февраль 2016 2 comments


Пример SQL запроса для склонения слова «ошибка» для вывода количества ошибок: «Найдена 1 ошибка»; «Найдено 3 ошибки»; «Найдено 5 ошибок» :

select 
  val
 ,case when mod(val,10) between 2 and 4 then 'Найдено '||val||' ошибки'
       
       when val between 11 and 14 or mod(val,10) between 5 and 9 
            or mod(val,10) = 0 then 'Найдено '||val||' ошибок' 
                          
       when mod(val,10) = 1 then 'Найдена '||val||' ошибка'
 end str
from ( select level val from dual connect by level<=25 )

       VAL STR
---------- -------------------------------------------------------
         1 Найдена 1 ошибка
         2 Найдено 2 ошибки
         3 Найдено 3 ошибки
         4 Найдено 4 ошибки
         5 Найдено 5 ошибок
         6 Найдено 6 ошибок
         7 Найдено 7 ошибок
         8 Найдено 8 ошибок
         9 Найдено 9 ошибок
        10 Найдено 10 ошибок
        11 Найдено 11 ошибок
        12 Найдено 12 ошибки
        13 Найдено 13 ошибки
        14 Найдено 14 ошибки
        15 Найдено 15 ошибок
        16 Найдено 16 ошибок
        17 Найдено 17 ошибок
        18 Найдено 18 ошибок
        19 Найдено 19 ошибок
        20 Найдено 20 ошибок
        21 Найдена 21 ошибка
        22 Найдено 22 ошибки
        23 Найдено 23 ошибки
        24 Найдено 24 ошибки
        25 Найдено 25 ошибок
25 rows selected

Удаление управляющих символов из строки

Для удаления управляющих символов (не отображаемых) можно использовать регулярные выражения:

  • REGEXP_LIKE — для поиска значений с управляющими символами
  • REGEXP_REPLACE — для удаления управляющих символов
select str, regexp_replace(t.str, '[[:cntrl:]]') as new_str
from (
select 'test1'||chr(28) as str from dual
union all
select 'test2' from dual
union all
select 'test3+_*- =\\\|()^%#3@' from dual
) t
where regexp_like(t.str, '[[:cntrl:]]')

STR                    NEW_STR
---------------------- ----------------------
test1                  test1

Список параметров процедуры SQL запросом

SQL запрос для получения списка параметров процедуры/функции

SELECT A.OBJECT_NAME, A.PACKAGE_NAME, A.ARGUMENT_NAME, A.DATA_TYPE, a.SEQUENCE
FROM USER_ARGUMENTS A
    ,ALL_OBJECTS    O
WHERE A.OBJECT_ID = O.OBJECT_ID
      AND O.OBJECT_NAME = 'FND_GLOBAL'      -- pkg
      AND A.OBJECT_NAME = 'APPS_INITIALIZE' -- procedure
/

OBJECT_NAME       PACKAGE_NAME  ARGUMENT_NAME       DATA_TYPE   SEQUENCE
----------------- ------------- ------------------- ----------- ----------
APPS_INITIALIZE   FND_GLOBAL    USER_ID             NUMBER      1
APPS_INITIALIZE   FND_GLOBAL    RESP_ID             NUMBER      2
APPS_INITIALIZE   FND_GLOBAL    RESP_APPL_ID        NUMBER      3
APPS_INITIALIZE   FND_GLOBAL    SECURITY_GROUP_ID   NUMBER      4
APPS_INITIALIZE   FND_GLOBAL    SERVER_ID           NUMBER      5

Даты в oracle — получить первый/последний день

6 Ноябрь 2012 1 комментарий
with t as (select trunc(sysdate) d from dual)
--
select 'ГОД - первый день' descr,trunc(d,'YY') new_date from t
union all
select 'ГОД - последний день', add_months(trunc(d,'YY'),12)-1 from t
union all
select 'КВАРТАЛ - первый день', trunc(d,'Q') from t
union all
select 'КВАРТАЛ - последний день', trunc(add_months(d, 3), 'Q')-1 from t
union all
select 'МЕСЯЦ - первый день' ,trunc(d,'MM') from t
union all
-- LAST_DAY не изменяет время
select 'МЕСЯЦ - последний день',last_day(d) from t 
union all
-- какой день недели считается первым, зависит от параметра NLS_TERRITORY
select 'НЕДЕЛЯ - первый день', trunc(d,'D') from t 
union all
select 'НЕДЕЛЯ - последний день', trunc(d,'D')+6 from t

DESCR                    NEW_DATE
------------------------ -----------
ГОД - первый день        01.01.2012
ГОД - последний день     31.12.2012
КВАРТАЛ - первый день    01.10.2012
КВАРТАЛ - последний день 31.12.2012
МЕСЯЦ - первый день      01.11.2012
МЕСЯЦ - последний день   30.11.2012
НЕДЕЛЯ - первый день     05.11.2012
НЕДЕЛЯ - последний день  11.11.2012
 
8 rows selected
SQL> select (sysdate-trunc(sysdate-1)) day to second + time '00:00:00' as diff_time from dual;
 
DIFF_TIME
---------
15:32:2
Categories: SQL Tags: , , , ,

Кто съел весь TEMP?

13 Сентябрь 2012 Нет комментариев

Запрос для просмотра сессий использующих TEMP

select s.sid
     , s.client_identifier
     , s.status
     , sum(round(u.blocks*8192/1024/1024,3)) "TEMP usage, Mb"
     , s.osuser, s.machine, s.module, s.action
  from v$session s,
       v$sort_usage u
 where s.saddr = u.session_addr
   and u.blocks*8192/1024 > 128 -- отсеиваем мелочь
group by s.sid, s.osuser, s.machine, s.module, s.action, s.client_identifier,s.status
order by "TEMP usage, Mb" desc
Categories: SQL Tags: , , ,

Поиск VIEW по используемым объектам

24 Апрель 2012 Нет комментариев

Поиск VIEW

Запрос для поиска VIEW, в которой используются определенные объекты.
Можно, например, использовать для поиска связки между таблицами.

with s as (
select 'XLA_AE_HEADERS' obj1, 'XLA_AE_LINES' obj2 from dual
)
select owner, name from All_Dependencies t, s
where type='VIEW' and t.REFERENCED_NAME like s.obj1
 intersect      
select owner, name from All_Dependencies t, s
where type='VIEW' and t.REFERENCED_NAME like s.obj2

OWNER                          NAME
------------------------------ ------------------------------
APPS                           ARFV_ADJ_DISTS_ALC_V
APPS                           ARFV_APP_DISTS_ALC_V
APPS                           ARFV_CASH_DISTS_ALC_V
APPS                           ARFV_FREIGHT_DISTS_ALC_V
APPS                           ARFV_REVENUE_DISTS_ALC_V
APPS                           ARFV_TAX_DISTS_ALC_V
APPS                           ARFV_UNREC_DISTS_ALC_V
APPS                           ARFV_UNREV_DISTS_ALC_V
...
Categories: SQL Tags: , , ,

Просмотр всех процедур PL/SQL package

29 Февраль 2012 1 комментарий

Для поиска определенных функций в plsql package можно использовать следующий запрос:

SQL> select object_name,
           procedure_name
    from ALL_PROCEDURES
    where 1=1
          and owner = 'APPS'
          and object_name like 'FND%'
          and object_type = 'PACKAGE'
          and subprogram_id <> 0
          --
          and procedure_name like '%CANONICAL%'
   /
 
OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------
FND_DATE                       CANONICAL_TO_DATE
FND_DATE                       DATE_TO_CANONICAL
FND_DATE                       STRING_TO_CANONICAL
FND_NUMBER                     CANONICAL_TO_NUMBER
FND_NUMBER                     NUMBER_TO_CANONICAL
FND_OAM_DSCFG_API_PKG          GET_PROPERTY_CANONICAL_VALUE
FND_OAM_DSCFG_PROPERTIES_PKG   GET_PROPERTY_CANONICAL_VALUE
FND_OAM_DSCFG_UTILS_PKG        BOOLEAN_TO_CANONICAL
FND_OAM_DSCFG_UTILS_PKG        CANONICAL_TO_BOOLEAN
FND_OAM_DSCFG_UTILS_PKG        CANONICAL_TO_DATE
FND_OAM_DSCFG_UTILS_PKG        CANONICAL_TO_NUMBER
FND_OAM_DSCFG_UTILS_PKG        DATE_TO_CANONICAL
FND_OAM_DSCFG_UTILS_PKG        NUMBER_TO_CANONICAL
 
13 rows selected

MySave — Plug-in for PL/SQL Developer

15 Февраль 2012 2 comments

В процессе работы постоянно приходится дописывать в файлы пакетов команду «SHOW ERRORS».
Делать это постоянно крайне утомило, да и забывается иногда.
Настройками PL/SQL Developer это сделать не удалось, поэтому попробовал решить данный вопрос написав свой плагин.

Представляю вашему вниманию плагин MySave.

Описание: Сохраняет данные в файл, дописывает в конце «/» и «SHOW ERRORS».
Description: Use this plug-in to save file with ‘SHOW ERRORS’
Requirements: PL/SQL Developer 5.1.2 or newer

Пункт меню:

  • File -> Save with show errors
  • File -> Save with show errors as …

Install Plug-in

1) Download plug-in MySave
2) Run Setup.exe or Do copy file MySave.dll to folder PlugIns

allroundautomations.com

Categories: Others, SQL Tags: , , ,

Просмотр настройки сегментов плана счетов

15 Февраль 2012 Нет комментариев

Запрос для просмотра настройки сегментов плана счетов

select sob.name                     Ledger_Name
      ,sob.ledger_id                Ledger_Id
      ,sob.chart_of_accounts_id     coa_id
      ,fifst.id_flex_structure_name struct_name
      ,ifs.segment_name
      ,ifs.application_column_name  column_name
      ,sav1.attribute_value         BALANCING
      ,sav2.attribute_value         COST_CENTER
      ,sav3.attribute_value         NATURAL_ACCOUNT
      ,sav4.attribute_value         INTERCOMPANY
      ,sav5.attribute_value         SECONDARY_TRACKING
      ,sav6.attribute_value         GLOBAL
      ,ffvs.flex_value_set_name
      ,ffvs.flex_value_set_id
  from fnd_id_flex_structures       fifs
      ,fnd_id_flex_structures_tl    fifst
      ,fnd_segment_attribute_values sav1
      ,fnd_segment_attribute_values sav2
      ,fnd_segment_attribute_values sav3
      ,fnd_segment_attribute_values sav4
      ,fnd_segment_attribute_values sav5
      ,fnd_segment_attribute_values sav6
      ,fnd_id_flex_segments         ifs
      ,fnd_flex_value_sets          ffvs
      ,gl_ledgers                   sob
 where 1 = 1
   and fifs.id_flex_code = 'GL#'
   and fifs.application_id = fifst.application_id
   and fifs.id_flex_code = fifst.id_flex_code
   and fifs.id_flex_num = fifst.id_flex_num
   and fifst.language = userenv('LANG')
   and fifs.application_id = ifs.application_id
   and fifs.id_flex_code = ifs.id_flex_code
   and fifs.id_flex_num = ifs.id_flex_num
   and sav1.application_id = ifs.application_id
   and sav1.id_flex_code = ifs.id_flex_code
   and sav1.id_flex_num = ifs.id_flex_num
   and sav1.application_column_name = ifs.application_column_name
   and sav2.application_id = ifs.application_id
   and sav2.id_flex_code = ifs.id_flex_code
   and sav2.id_flex_num = ifs.id_flex_num
   and sav2.application_column_name = ifs.application_column_name
   and sav3.application_id = ifs.application_id
   and sav3.id_flex_code = ifs.id_flex_code
   and sav3.id_flex_num = ifs.id_flex_num
   and sav3.application_column_name = ifs.application_column_name
   and sav4.application_id = ifs.application_id
   and sav4.id_flex_code = ifs.id_flex_code
   and sav4.id_flex_num = ifs.id_flex_num
   and sav4.application_column_name = ifs.application_column_name
   and sav5.application_id = ifs.application_id
   and sav5.id_flex_code = ifs.id_flex_code
   and sav5.id_flex_num = ifs.id_flex_num
   and sav5.application_column_name = ifs.application_column_name
   and sav6.application_id = ifs.application_id
   and sav6.id_flex_code = ifs.id_flex_code
   and sav6.id_flex_num = ifs.id_flex_num
   and sav6.application_column_name = ifs.application_column_name
   and sav1.segment_attribute_type = 'GL_BALANCING'
   and sav2.segment_attribute_type = 'FA_COST_CTR'
   and sav3.segment_attribute_type = 'GL_ACCOUNT'
   and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
   and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
   and sav6.segment_attribute_type = 'GL_GLOBAL'
   and ifs.id_flex_num = sob.chart_of_accounts_id
   and ifs.flex_value_set_id = ffvs.flex_value_set_id
   and sob.ledger_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'), sob.ledger_id)
 order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

SYS_CONTEXT

15 Февраль 2012 Нет комментариев

Стандартная функция SYS_CONTEXT служит для получения атрибутов контекста.
Пример использования SYS_CONTEXT для получения общей информации:

select SYS_CONTEXT('USERENV', 'TERMINAL') terminal
      ,SYS_CONTEXT('USERENV', 'LANGUAGE') language
      ,SYS_CONTEXT('USERENV', 'LANG') lang     
      ,SYS_CONTEXT('USERENV', 'SESSIONID') sessionid
      ,SYS_CONTEXT('USERENV', 'INSTANCE') instance
      ,SYS_CONTEXT('USERENV', 'ENTRYID') entryid
      ,SYS_CONTEXT('USERENV', 'ISDBA') isdba
      ,SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory
      ,SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency
      ,SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar
      ,SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format
      ,SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language
      ,SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort
      ,SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user
      ,SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid
      ,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID' ) current_schemaid
      ,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA' ) current_schema
      ,SYS_CONTEXT('USERENV', 'SESSION_USER') session_user
      ,SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid
      ,SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user
      ,SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid
      ,SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain
      ,SYS_CONTEXT('USERENV', 'DB_NAME') db_name
      ,SYS_CONTEXT('USERENV', 'HOST') host
      ,SYS_CONTEXT('USERENV', 'OS_USER') os_user
      ,SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name
      ,SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address
      ,SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol
      ,SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id
      ,SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id
      ,SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type
      ,SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data
      ,SYS_CONTEXT('USERENV', 'CURRENT_SQL') current_sql
      ,SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') client_identifier
      ,SYS_CONTEXT('USERENV', 'GLOBAL_CONTEXT_MEMORY') global_context_memory
from dual

Oracle® Database SQL Language Reference
11g Release 2 (11.2): Table 5-11 Predefined Parameters of Namespace USERENV