Архив

Архив раздела ‘SQL’

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

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

  • 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

Определение четная или нечетная текущая неделя

Запрос определения четная или нечетная неделя относительно текущей даты

Используем маску ‘IW’ — Week of year (1-52 or 1-53) based on the ISO standard.

select case when mod(to_number(to_char(sysdate, 'IW')), 2) = 0 then
          'Четная неделя'
         else
          'Нечетная неделя'
       end cur_week
      ,to_number(to_char(sysdate, 'IW')) num_week
from dual


Oracle® Database SQL Language Reference 11g Release 2 (11.2) Table Datetime Format Elements

Скрипт обновления параметров профиля

28 Апрель 2014 Нет комментариев

Скрипт обновления параметров профиля. В данном примере изменяется поле SQL_VALIDATION.

declare
  l_sql VARCHAR2(4000);
begin
           
  l_sql := 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
            INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
            FROM FND_LOOKUP_VALUES_VL
            WHERE LOOKUP_TYPE = ''!!!LOOKUP_NAME''
                  AND ENABLED_FLAG = ''Y''
                  AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE)
                      AND NVL(END_DATE_ACTIVE,SYSDATE)
            ORDER BY MEANING"
            COLUMN="\"Включить проверку\"(20)"';

  for i in (select profile_option_name
                 , user_profile_option_name
                 , start_date_active
                 , end_date_active
                 , user_changeable_flag
                 , user_visible_flag
                 , read_allowed_flag
                 , write_allowed_flag
                 , site_enabled_flag
                 , site_update_allowed_flag
                 , app_enabled_flag
                 , app_update_allowed_flag
                 , resp_enabled_flag
                 , resp_update_allowed_flag
                 , user_enabled_flag
                 , user_update_allowed_flag
                 , (select a.application_short_name 
                    from fnd_application a 
                    where a.application_id = t.application_id
                    ) as appl_short_name
            from fnd_profile_options_vl t
            where profile_option_name like '!!!%'
          )
  loop
   dbms_output.put_line('update '||i.profile_option_name);
   fnd_profile_options_pkg.load_row (
                   x_profile_name             => i.profile_option_name,
                   x_owner                    => '!!!XX',
                   x_application_short_name   => i.appl_short_name,
                   x_user_profile_option_name => i.user_profile_option_name,
                   x_description              => i.user_profile_option_name,
                   x_user_changeable_flag     => i.user_changeable_flag,
                   x_user_visible_flag        => i.user_visible_flag,
                   x_read_allowed_flag        => i.read_allowed_flag,
                   x_write_allowed_flag       => i.write_allowed_flag,
                   x_site_enabled_flag        => i.site_update_allowed_flag,
                   x_site_update_allowed_flag => i.site_update_allowed_flag,
                   x_app_enabled_flag         => i.app_enabled_flag,
                   x_app_update_allowed_flag  => i.app_update_allowed_flag,
                   x_resp_enabled_flag        => i.resp_enabled_flag,
                   x_resp_update_allowed_flag => i.resp_update_allowed_flag,
                   x_user_enabled_flag        => i.user_enabled_flag,
                   x_user_update_allowed_flag => i.user_update_allowed_flag,
                   x_start_date_active        => to_char(i.start_date_active,'YYYY/MM/DD'),
                   x_end_date_active          => to_char(i.end_date_active,'YYYY/MM/DD'),
                   x_sql_validation           => l_sql
                                    );
    dbms_output.put_line('done');
  end loop;
  
end;

Поля STATUS_CODE и PHASE_CODE из FND_CONCURRENT_REQUESTS

14 Октябрь 2013 Нет комментариев

Расшифровка STATUS_CODE и PHASE_CODE из таблицы канкарентов FND_CONCURRENT_REQUESTS

PHASE_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_PHASE_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;
LOOKUP_CODE MEANING_RU MEANING_US
R Выполнение Running
P Отложено Pending
I Неактивно Inactive
C Завершено Completed

STATUS_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_STATUS_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;
LOOKUP_CODE MEANING_RU MEANING_US
W Пауза Paused
R   Обычное   Normal
U Отключено Disabled
H Заблокирован On Hold
P Запланировано Scheduled
I  Обычное  Normal
D Отменено Cancelled
M Нет диспетчера No Manager
Q Резервный Standby
C Обычный Normal
B Возобновлен Resuming
X Прекращено Terminated
T Прекращение Terminating
A Ожидание Waiting
Z  Ожидание  Waiting
E Ошибка Error
S Приостановлено Suspended
G Предупреждение Warning

 

Читать дальше про “Поля STATUS_CODE и PHASE_CODE из FND_CONCURRENT_REQUESTS” »

Регулярные выражения — оставить только цифры или символы

28 Август 2013 8 comments

Пример использования регулярного выражения (regexp_replace)
— оставить только символы
— оставить только цифры

with t as (select '#2 apps(0_-ORACLE+*.ru_ )ПРИ13мер' str from dual)
select str
     , regexp_replace(str,'[^[[:alpha:]]]*') as only_char
     , regexp_replace(str,'[^[[:digit:]]]*') as only_number
from t
/

STR                                 ONLY_CHAR                 ONLY_NUMBER
---------------------------------   ------------------------  --------------------
#2 apps(0_-ORACLE+*.ru_ )ПРИ13мер   appsORACLEruПРИмер        2013

OEBS Генерация параметров запуска канкарента

Скрипт генерации параметров запуска канкарента

declare
 p_conc_code VARCHAR2(100) := 'AFBFULDL';
 --
 l_space_num number := 40;
 l_space varchar2(100) := lpad(' ',l_space_num,' ');
 l_appl_code varchar2(50);
 l_conc_prog_rec fnd_concurrent_programs_vl%rowtype;
 
begin
 
 select * into l_conc_prog_rec from fnd_concurrent_programs_vl t
 where concurrent_program_name = p_conc_code;
 
 select application_short_name into l_appl_code
 from fnd_application a where a.application_id = l_conc_prog_rec.application_id;
 
 dbms_output.put_line('-- '||l_conc_prog_rec.USER_CONCURRENT_PROGRAM_NAME);
 dbms_output.put_line('p_req_id := fnd_request.submit_request( application => '''
                      ||l_appl_code ||''',');
 dbms_output.put_line(l_space||'program     => '''||p_conc_code||''',');
 dbms_output.put_line(l_space||'start_time  => null,');
 dbms_output.put_line(l_space||'description => null,');
 dbms_output.put_line(l_space||'sub_request => false,');   
 
 for p in (select max(rownum) over() as max_rownum
                 ,row_number() over(order by column_seq_num) as row_num, t.*
           from fnd_descr_flex_col_usage_vl t
           where descriptive_flexfield_name = '$SRS$.'||p_conc_code
                 and enabled_flag='Y'
           order by column_seq_num)
 loop
   dbms_output.put_line(l_space||rpad('argument'||p.row_num,12,' ')
                     ||'=> null'||case when p.row_num!=p.max_rownum then ',' else ' ' end
                     ||' -- '||'REQUIRED='||p.REQUIRED_FLAG
                     ||' '||nvl(p.description,p.form_left_prompt)
                     ||' ('||p.end_user_column_name||')'
                     || case when p.DEFAULT_VALUE is not null 
                         then ' DEFAULT_VALUE='||p.DEFAULT_VALUE
                        end
                     );
 end loop;
 dbms_output.put_line(lpad(' ',l_space_num-2,' ')||');');  
 
end;

-- Пример вывода
-- Диспетчер масс-обработки файлов
p_req_id := fnd_request.submit_request( application => 'FND',
                                        program     => 'AFBFULDL',
                                        start_time  => null,
                                        description => null,
                                        sub_request => false,
                                        argument1   => null, -- REQUIRED=N Имя файла (File Name)
                                        argument2   => null, -- REQUIRED=N Тип содержимого файла (File Content Type)
                                        argument3   => null, -- REQUIRED=N Имя программы (Program Name)
                                        argument4   => null, -- REQUIRED=N Тег программы (Program Tag)
                                        argument5   => null, -- REQUIRED=N Язык (Language)
                                        argument6   => null, -- REQUIRED=N Формат файла (File Format)
                                        argument7   => null, -- REQUIRED=N Режим (upload/download или upload_help/download_help) (Mode)
                                        argument8   => null, -- REQUIRED=Y Загрузка/выгрузка каталога (Directory)
                                        argument9   => null, -- REQUIRED=N Создание каталога в случае его отсутствия (Create Directory) DEFAULT_VALUE=true
                                        argument10  => null, -- REQUIRED=N Переопределение каталога для выгрузки в случае, если выгружаемый файл является абсолютным (Override Directory) DEFAULT_VALUE=false
                                        argument11  => null, -- REQUIRED=N Конец строки (Line Break)
                                        argument12  => null, -- REQUIRED=N Заголовок (Title)
                                        argument13  => null  -- REQUIRED=N Имя контрольного значения (Target Name)
                                      );

Дополнительный код для запуска параллельной программы

declare 
  p_req_id    number;
  p_user_name varchar2(100) := '!!!USER_NAME';
  --
  l_user_id number;
begin
  select nvl(max(u.user_id),-1) into l_user_id from fnd_user u where u.user_name=p_user_name;
  fnd_global.apps_initialize(l_user_id, 20639, 200); --Диспетчер кредиторов - Кредиторы
 
  execute immediate 'alter session set NLS_LANGUAGE = ''RUSSIAN''';
  execute immediate 'alter session set NLS_DATE_FORMAT = ''DD.MM.YYYY HH24:MI:SS''';

  -- вставить запуск канкарента
  -- ...  
  
  dbms_output.put_line('p_req_id='||p_req_id);                                    
  commit;
end;
-- запрос для просмотра запущенного канкарента
-- select t.completion_text, t.* from APPLSYS.FND_CONCURRENT_REQUESTS t where t.request_id=

Мониторинг использования индексов

Если появилось желание проверить используется индекс или нет, то:

1) Сначала включаем мониторинг интересующих нас индексов

ALTER INDEX OWNER.INDEX_NAME MONITORING USAGE;

2) После того, как отработала программа, которая потенциально должна была использовать индекс, выполняем запрос

select io.name as index_name
      ,t.name  as table_name
      ,decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring
      ,decode(bitand(ou.flags, 1), 0, 'NO', 'YES')    as used
      ,ou.start_monitoring as start_monitoring
      ,ou.end_monitoring   as end_monitoring
from sys.obj$         io
    ,sys.obj$         t
    ,sys.ind$         i
    ,sys.object_usage ou
where i.obj# = ou.obj#
      and io.obj# = ou.obj#
      and t.obj# = i.bo#;

3) Выключаем мониторинг индекса

ALTER INDEX OWNER.INDEX_NAME NOMONITORING USAGE;

Есть также view V$OBJECT_USAGE, но она показывает данные только для текущей схемы.

Вывод промежутка времени в часах,минутах, секундах (hh:mi:ss)

Скрипт с примером вывода промежутка времени в часах,минутах, секундах (hh:mi:ss)

SET SERVEROUTPUT ON
declare
  l_point1 NUMBER;
  l_point2 NUMBER;
  l_diff   NUMBER;
  l_time_s NUMBER;

  -- Возвращает затраченное время в секундах
  function get_time_diff(p_time number) return number
  is
  begin
    return round( p_time/100 , 0);
  end get_time_diff;
  
  -- Возвращает секунды
  function get_time_diff_ss(p_all_time number) return number
  is
  begin
    return round(mod(p_all_time,60),0);
  end get_time_diff_ss; 
  
  -- Возвращает минуты
  function get_time_diff_mi(p_all_time number) return number
  is
  begin
    return mod(floor(p_all_time/60),60);
  end get_time_diff_mi;    
  
  -- Возвращает часы
  function get_time_diff_hh(p_all_time number) return number
  is
  begin
    return floor(p_all_time/(60*60));
  end get_time_diff_hh;     
  
  -- Возвращает разницу в hh:mi:ss
  function get_time_diff_hhmiss(p_all_time number) return varchar2
  is
    l_time_s NUMBER;
  begin
    l_time_s := round( p_all_time/100 , 0);
    return        lpad( floor(round(l_time_s /3600,2)) ,2,'0')
           ||':'||lpad( mod(floor(l_time_s /60),60)    ,2,'0')
           ||':'||lpad( round(mod(l_time_s ,60),0)     ,2,'0');
  end get_time_diff_hhmiss;     
   
begin
  l_point1 := dbms_utility.get_time();
  dbms_lock.sleep(5);
  l_point2 := dbms_utility.get_time();

  l_diff   := l_point2 - l_point1;
  l_time_s := get_time_diff(l_diff);
  
  dbms_output.put_line('весь период времени в секундах = '|| l_time_s || 's');
  
  dbms_output.put_line('только секунды = '|| get_time_diff_ss(l_time_s)||'s' );
  
  dbms_output.put_line('только минуты = '|| get_time_diff_mi(l_time_s)||'m' );  

  dbms_output.put_line('только часы = '|| get_time_diff_hh(l_time_s)||'h' );  

  dbms_output.put_line('h:m:s = '|| get_time_diff_hh(l_time_s)||'h:'
                      ||get_time_diff_mi(l_time_s)
                      ||'m:'||get_time_diff_ss(l_time_s)||'s' ); 

  dbms_output.put_line('hh:mi:ss = '|| lpad(get_time_diff_hh(l_time_s),2,'0')
                        ||':'||lpad(get_time_diff_mi(l_time_s),2,0)
                        ||':'||lpad(get_time_diff_ss(l_time_s),2,'0') );
  
  dbms_output.put_line('get_time_diff_hhmiss = '|| get_time_diff_hhmiss(l_diff));
    
end;
/

весь период времени в секундах = 5s
только секунды = 5s
только минуты = 0m
только часы = 0h
h:m:s = 0h:0m:5s
hh:mi:ss = 00:00:05
get_time_diff_hhmiss = 00:00:05

Список параметров процедуры 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 Создание PARTITION BY RANGE по полю DATE

21 Ноябрь 2012 1 комментарий

Скрипт помогающий создать PARTITION BY RANGE по полю DATE

SET LINESIZE 150
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER SQLERROR CONTINUE
SPOOL C:\create_partition_date.log
declare
 iter number;
 l_date_start date := to_date('01-11-2011','dd-mm-yyyy');
 l_date_end   date := to_date('01-01-2023','dd-mm-yyyy');
 l_name_part  varchar2(20) := 'TEST';
 l_part_field varchar2(20) := 'MY_DATE';
begin
 dbms_output.put_line('PARTITION BY RANGE('||l_part_field||')');
 dbms_output.put_line('(');
 dbms_output.put_line(' PARTITION '||l_name_part
                        ||'_MIN     VALUES LESS THAN (to_date('''
                        ||to_char(add_months(l_date_start,-1),'dd-mm-yyyy')
                        ||''',''dd-mm-yyyy''))');
 for j in (
              with t as (
              select l_date_start d1,
                     l_date_end d2
              from dual
            )
            --
            select decode(level,1,d1,
                          trunc(add_months(d1,level-1),'mm')
                         ) as date_from
            from t
            connect by add_months(trunc(d1,'mm'),level-1) <= d2
           ) 
 loop
   dbms_output.put_line(',PARTITION '||l_name_part||'_'
                        ||to_char(add_months(j.date_from,-1),'mm_yyyy')
                        ||' VALUES LESS THAN (to_date('''
                        ||to_char(j.date_from,'dd-mm-yyyy')
                        ||''',''dd-mm-yyyy''))');
 end loop;
 dbms_output.put_line(',PARTITION '||l_name_part
                      ||'_MAX     VALUES LESS THAN (MAXVALUE)'); 
 dbms_output.put_line(')');
end;
/
spool off

PARTITION BY RANGE(MY_DATE)
(
 PARTITION TEST_MIN     VALUES LESS THAN (to_date('01-10-2011','dd-mm-yyyy'))
,PARTITION TEST_10_2011 VALUES LESS THAN (to_date('01-11-2011','dd-mm-yyyy'))
,PARTITION TEST_11_2011 VALUES LESS THAN (to_date('01-12-2011','dd-mm-yyyy'))
...
,PARTITION TEST_11_2022 VALUES LESS THAN (to_date('01-12-2022','dd-mm-yyyy'))
,PARTITION TEST_12_2022 VALUES LESS THAN (to_date('01-01-2023','dd-mm-yyyy'))
,PARTITION TEST_MAX     VALUES LESS THAN (MAXVALUE)
)
 
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to C:\create_partition_date.log