Oracle

Преобразование шестнадцатиричного представления числа в десятичное

CREATE OR REPLACE FUNCTION hex2int(hex VARCHAR2)
RETURN PLS_INTEGER IS
len PLS_INTEGER := LENGTH(hex);
BEGIN
    IF NVL(len,1) = 1 THEN
      RETURN INSTR('0123456789ABCDEF',hex) - 1;
    ELSE
      RETURN 16 * hex2int(SUBSTR(hex,1,len-1)) +
        INSTR('0123456789ABCDEF',SUBSTR(hex,-1)) - 1;
END IF;
END hex2int;

Преобразование десятичного представления числа в шестнадцатиричное

CREATE OR REPLACE FUNCTION int2hex(n PLS_INTEGER)
RETURN VARCHAR2 IS
BEGIN
IF n > 0 THEN
    RETURN int2hex(TRUNC(n/16))||
      SUBSTR('0123456789ABCDEF',MOD(n,16)+1,1);
ELSE
    RETURN NULL;
END IF;
END int2hex;

Преобразование десятичного представления числа в двоичное

CREATE OR REPLACE FUNCTION int2bin(int PLS_INTEGER)
RETURN VARCHAR2 IS
BEGIN
IF int > 0 THEN
    RETURN int2bin(TRUNC(int/2))||
      SUBSTR('01',MOD(int,2)+1,1);
ELSE
    RETURN NULL;
END IF;
END int2bin;

Преобразование двоичного представления числа в десятичное

CREATE OR REPLACE FUNCTION bin2int (bin VARCHAR2)
RETURN PLS_INTEGER IS
len PLS_INTEGER := LENGTH(bin);
BEGIN
    IF NVL(len,1) = 1 THEN
      RETURN bin;
    ELSE RETURN
      2 * bin2int(SUBSTR(bin,1,len-1)) + SUBSTR(bin,-1);
    END IF;
END bin2int;

Отправка E-Mail сообщения

CREATE OR REPLACE PROCEDURE aasend_mail (
msg_from    varchar2 :='aabashin@office.xxx.ru',
msg_to      varchar2 := 'andreytest@office.xxx.ru',
msg_subject varchar2 := 'PL/SQL-daemon',
msg_text    varchar2 := 'PL/SQL-демон: Проверка автоматической рассылки сообщений')
IS
conn          utl_tcp.connection;
rc            integer;
mailhost      varchar2(30) := 'md.local'; -- SMTP-сервер
BEGIN
conn := utl_tcp.open_connection(mailhost,25); -- открываем SMTP порт
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'HELO '||mailhost);
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'From: PL/SQL');
rc := utl_tcp.write_line(conn, 'Reply-To: '||msg_from);
rc := utl_tcp.write_line(conn, 'Message-ID: <'||
        to_char(abs(dbms_random.random))||'.'||
        to_char(sysdate,'yyyymmdd')||
        to_char(sysdate,'hh24miss')||'@plsql.xxx.ru'||'>');
rc := utl_tcp.write_line(conn, 'To: '||msg_to);
rc := utl_tcp.write_line(conn, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(conn, '');
rc := utl_tcp.write_line(conn, msg_text);
rc := utl_tcp.write_line(conn, '.'); -- Конец тела сообщения
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
utl_tcp.close_connection(conn);    -- Закрываем соединение
EXCEPTION
when others then
    raise_application_error(-20000,
    'Не могу отправить E-mail сообщение!');
END;

Загрузка данных в CLOB из интернета

declare
   pieces   utl_http.html_pieces;
   HBody    clob:= EMPTY_CLOB;

BEGIN
dbms_lob.createtemporary(HBody, TRUE);
dbms_lob.open(HBody, dbms_lob.lob_readwrite);
-- Тащим из интернета
pieces := utl_http.request_pieces(url => 'http://www.site.ru');
for i in 1 .. pieces.count loop
   dbms_lob.writeappend(HBody, LENGTHB(pieces(i)), pieces(i));
end loop;

dbms_output.put_line('Дальнейшая обработка...');

dbms_lob.close(Hbody);

end;

Загрузка данных в CLOB из файла

declare
   HBody    clob:= EMPTY_CLOB;
   src_file BFILE := bfilename('CTEMP', 'test.txt');
   len_file BINARY_INTEGER;
BEGIN
dbms_lob.createtemporary(HBody, TRUE);
dbms_lob.open(HBody, dbms_lob.lob_readwrite);
-- Читаем из файла
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- определение размера файла
len_file := dbms_lob.getlength(src_file);
-- читаем из файла
dbms_lob.loadfromfile(HBody, src_file, len_file);

dbms_output.put_line('Дальнейшая обработка...');

dbms_lob.close(HBody);

end;

Внимание!
Необходим доступ к указанной директории.
для создания:
create directory ctemp as 'c:\temp';
для проверки наличия доступа:
select * from dba_directories;

Словарь по таблицам и представлениям схемы пользователя

/*
Словарь по таблицам и представлениям схемы пользователя
*/
set serveroutput on;
set lin 255;

declare

cursor cols_cur(p_tname varchar2) is
         select t.column_name, data_type, comments
         from user_tab_columns t, user_col_comments c
         where t.table_name=c.table_name and
         t.column_name=c.column_name and t.table_name=p_tname order by 1;

initial_letter varchar2(1):=' ';

begin

dbms_output.enable(1000000);

for c1 in (select table_name,replace('('||table_type||')','(TABLE)','') w, comments
             from user_tab_comments order by 1) loop

    if initial_letter != substr(c1.table_name,1,1) then
      initial_letter:=substr(c1.table_name,1,1);
      dbms_output.put_line('- '||initial_letter||' -'||chr(10));
    end if;
    dbms_output.put_line(c1.table_name||c1.w||' - '||c1.comments);
    dbms_output.put_line(rpad(' ',length(c1.table_name)+ length(c1.w) +1,'-'));

    for cols_rec in cols_cur(c1.table_name)
    loop
      dbms_output.put_line(substr(rpad(cols_rec.column_name,31,' ')||' '||
      rpad(cols_rec.data_type,15)||' '||cols_rec.comments,1,255));
    end loop;

    dbms_output.put_line(chr(10));

end loop;

end;
/

Поиск в теле процедур и функций по заданной фразе поиска

/*
   Поиск в теле процедур и функций по заданной фразе поиска
*/
set serveroutput on;
set lin 300;

declare

cursor body_cur(p_proc_name varchar2) is
select text from sys.user_source where name=p_proc_name
order by line;

v_text     varchar2(4000);
v_stext    varchar2(4000):='open '; -- искомая фраза
v_tit_flag integer:=0;
v_case     integer:=0; -- учитывать регистр(0-нет, 1-да)
v_first    integer:=0; -- показывать только первое вхождение(0-нет, 1-да)

function myInstr
(
   p_text varchar2,
   p_stext varchar2,
   p_case integer
)
   return integer is
begin
    if p_case=0 then
      return instr(upper(p_text), upper(p_stext));
    else
      return instr(p_text, p_stext);
    end if;
end;

begin

dbms_output.enable(1000000);

for c1 in (select object_name, object_type from user_objects
where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
order by 2)
loop
    open body_cur(c1.object_name);
    loop
      exit when body_cur%notfound;
      fetch body_cur into v_text;
      if myInstr(v_text, v_stext, v_case) != 0 then
       if v_tit_flag=0 then
         v_tit_flag:=1;
         dbms_output.put_line(c1.object_type||' '||c1.object_name);
       end if;
       dbms_output.put_line(substr('...'||v_text,1,252)||'...');
       exit when v_first=1;
      end if;
    end loop;
    close body_cur;
    v_tit_flag:=0;
end loop;

end;
/

Поиск подстроки по таблицам схемы

set serveroutput on format wrapped;
set lin 80;

declare

   cursor tab_cur is
          select table_name from sys.user_tables;

   cursor cols_cur(p_tname varchar2) is
          select column_name, data_type, data_length
          from sys.user_tab_columns
          where table_name=p_tname and
          data_type in ('CHAR','NCHAR','VARCHAR2','NVARCHAR2','LONG','CLOB','NCLOB')
          order by 1;

   cols_rec cols_cur%rowtype;

   v_searchstr varchar2(32767):='Подстрока поиска';
   v_tname     varchar2(30):=' ';
   v_clob_flag integer:=1; -- искать в CLOB-полях (0-нет/1-да)
   v_count     integer;
   v_notfirst integer;

begin

   dbms_output.enable(1000000);

   dbms_output.put_line('Искомая подстрока: '||v_searchstr);
   dbms_output.put_line('---------------------------------------------');
   open tab_cur;
    loop
      fetch tab_cur into v_tname;
      exit when tab_cur%notfound;
      v_notfirst := 0;

      open cols_cur(v_tname);
       loop
         fetch cols_cur into cols_rec;
         exit when cols_cur%notfound;

         if cols_rec.data_type='CLOB' and v_clob_flag=0 then
           null;
         else
           if (length(v_searchstr) <= cols_rec.data_length) or
              (cols_rec.data_type='CLOB') then
              --
              execute immediate 'select count(1)'||' from '||v_tname||
                                ' where instr('||cols_rec.column_name||
                                ','''||v_searchstr||''')<>0' into v_count;

              if v_count != 0 then
                 if v_notfirst = 0 then
                   v_notfirst:=1;
                   dbms_output.put_line(' Таблица: '||v_tname);
                 end if;
                 dbms_output.put_line('       Поле: '||cols_rec.column_name||
                                      '('||cols_rec.data_type||')'||
                                      ', кол-во вхождений: '||v_count);
              end if;
              --
           end if;
         end if;

       end loop;
      close cols_cur;

    end loop;
   close tab_cur;

end;
/

Управление пользователями схемы

create or replace procedure users_control
/*
p_password - пароль. Заполняется, если создается новый пользователь
               или меняется пароль текущего пользователя;
p_role     - Роль пользователя. Pаполняется, если создается новый
               пользователь или меняется роль текущего пользователя;
p_shema    - Схема, к которой предоставляется доступ. Заполняется, если
               отличается от текущей;
p_errcode - код ошибки. 0 - нормальное завершение
*/
(
p_username varchar2,
p_password varchar2 default null,
p_role      varchar2 default null,
p_shema     varchar2 default user,
p_errcode in out integer
)
is

/* Объявление исключений */
err_create_user EXCEPTION;
err_alter_user EXCEPTION;
err_revoke_role EXCEPTION;
err_grant_role EXCEPTION;
err_create_syn EXCEPTION;
err_drop_syn    EXCEPTION;

v_countu integer;

/* Создание пользователя*/
procedure Create_User(p_username varchar2, p_password varchar2)
is
def_tablespace varchar2(30);
tem_tablespace varchar2(30);
begin
    select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE into def_tablespace, tem_tablespace
    from DBA_USERS WHERE USERNAME = p_shema;
    execute immediate 'create user '||p_username||' identified by "'||p_password||
    '" default tablespace '||def_tablespace||' temporary tablespace '||tem_tablespace;
    EXCEPTION
     when others then
      raise err_create_user;
end;

/* Удаление синонимов */
procedure Drop_Syn(p_username varchar2)
is
begin
    FOR PR IN (SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE OWNER=UPPER(P_USERNAME)) LOOP
   EXECUTE IMMEDIATE 'DROP synonym '||P_USERNAME||'.'||pr.SYNONYM_NAME;
    END LOOP;
    EXCEPTION
     when others then
      raise err_drop_syn;
end;

/* Создание синонимов */
procedure Create_Syn(p_role varchar2, p_username varchar2)
is
begin
    for pr in (select distinct table_name from dba_tab_privs where
              owner=p_shema and grantee=p_role and table_name not in
              (select synonym_name from all_synonyms where owner=p_username)) loop
       /* если подразумевается, что синонимы с объектами одноименны, то создаем. */
       execute immediate 'create synonym '||p_username||'.'||pr.table_name||
                         ' for '||p_shema||'.'||pr.table_name;
    end loop;
    /* Спуститься на следующий уровень подчиненности */
    for rec in (select granted_role from dba_role_privs where grantee=p_role) loop
      Create_Syn(REC.GRANTED_ROLE, p_username); -- рекурсивный вызов
    end loop;
    EXCEPTION
     when others then
      raise err_create_syn;
end;

/* Отмена прав на роль */
procedure Revoke_Role(p_username varchar2)
is
begin
    for rec in (select granted_role from dba_role_privs where grantee=p_username) loop
      EXECUTE IMMEDIATE 'revoke '||rec.granted_role||' from '||p_username;
    end loop;

    EXCEPTION
     when others then
      raise err_revoke_role;
end;

/* Предоставление прав */
procedure Grant_Role(p_role varchar2, p_username varchar2)
is
begin
    EXECUTE IMMEDIATE 'GRANT '||p_role||' TO '||p_username;
    EXCEPTION
     when others then
      raise err_grant_role;
end;

/* Смена пароля */
procedure Alter_User(p_username varchar2, p_password varchar2)
is
begin
    EXECUTE IMMEDIATE
      'alter user '||p_username||' identified by '||p_password;
    EXCEPTION
     when others then
      raise err_alter_user;
end;

begin

   select count(1) into v_countu from all_users where username=p_username;
   if v_countu = 0 then
     Create_User(p_username, p_password);
   else
     if p_password is not null then
       Alter_User(p_username, p_password);
     end if;
     if p_role is not null then
       Drop_Syn(p_username);
       Revoke_Role(p_username);
     end if;
   end if;
   if p_role is not null then
     Grant_Role(p_role, p_username);
     Create_Syn(p_role, p_username);
   end if;

   p_errcode:=0;

   EXCEPTION
    when err_create_user then
       dbms_output.put_line('Oшибка при создании пользователя');
       p_errcode:=1;
    when err_revoke_role then
       dbms_output.put_line('Oшибка при отмене прав');
       p_errcode:=2;
    when err_grant_role then
       dbms_output.put_line('Oшибка при предоставлении прав');
       p_errcode:=3;
    when err_create_syn then
       dbms_output.put_line('Oшибка при создании синонимов');
       p_errcode:=4;
    when err_drop_syn    then
       dbms_output.put_line('Oшибка при удалении синонимов');
       p_errcode:=5;
    when err_alter_user then
       dbms_output.put_line('Oшибка при смене пароля пользователя');
       p_errcode:=6;
    when others then
       dbms_output.put_line('Неизвестная ошибка: '||SQLERRM);
       p_errcode:=7;

end users_control;
/

Контроль ИНН предприятия

create or replace function CheckINN(p_INN varchar2) Return integer
/*
Контроль ИНН
возвращает 0 при корректном ИНН
*/
is
type    tlist_fields is table of integer;
inn10   tlist_fields:= tlist_fields(2, 4, 10, 3, 5, 9, 4, 6, 8, 0, 0, 0);
inn12_1 tlist_fields:= tlist_fields(7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0, 0);
inn12_2 tlist_fields:= tlist_fields(3, 7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0);
innb    tlist_fields:= tlist_fields(null, null, null, null, null, null, null, null, null, null, null, null);

err_len_inn               exception;
err_10d_not_correct       exception;
err_11d_of12d_not_correct exception;
err_12d_of12d_not_correct exception;

v_INN varchar2(12);
i     integer;

function GetINNCheckSum(INN tlist_fields, Co tlist_fields, CoLen integer)
Return Integer
is
i      integer;
Result integer;
begin
    Result := 0;
    for i in 1..CoLen loop
      Result:=Result+INN(i)*Co(i);
    end loop;
    Result := Result mod 11;
    Result := Result mod 10;
    Return Result;
end;

begin

v_INN := replace(p_INN,' ','');
if ((Length(v_INN) !=10) and (Length(v_INN) != 12)) then
    raise err_len_inn;
end if;
for i in 1..INNB.count loop
   if i <= Length(v_INN) then
     INNB(i) := To_number(Substr(v_INN,i,1));
   else
     INNB(i) := 0;
   end if;
end loop;
if Length(v_INN) = 10 then
   if To_number(Substr(v_INN,10,1)) != GetINNCheckSum(INNB, INN10, 9) then
     raise err_10d_not_correct;
   end if;
else
   i := GetINNCheckSum(INNB, INN12_1, 10);
   if To_number(Substr(v_INN,11,1)) <> i then
     raise err_11d_of12d_not_correct;
   else
     INNB(11) := i;
     if To_number(Substr(v_INN,12,1)) <> GetINNCheckSum(INNB, INN12_2, 11) then
       raise err_12d_of12d_not_correct;
     end if;
   end if;
end if;
dbms_output.put_line('INN O''k!');
Return 0;
exception
when err_len_inn then
   dbms_output.put_line('INN length not correct');
   Return 1;
when err_10d_not_correct then
   dbms_output.put_line('10 digit INN not correct');
   Return 2;
when err_11d_of12d_not_correct then
   dbms_output.put_line('11 digit of 12 digits INN not correct');
   Return 3;
when err_12d_of12d_not_correct then
   dbms_output.put_line('12 digit of 12 digits INN not correct');
   Return 4;
when others then
   dbms_output.put_line(SQLERRM);
   Return 5;
end;
/

Расчет ключа 20-значного лицевого счета(для банков)

create or replace function k20(accnumb varchar2, bik varchar2)
Return varchar2
/*
   accnumb - лицевой счет
   bik     - БИК банка(последние 3 цифры)
*/
is
v_acc varchar2(23);
v_sum integer := 0;
v_key varchar2(23):='71371371371371371371371';
i integer;
n integer;
begin
   v_acc := SubStr(accnumb, 1, 8)||'0'||SubStr(accnumb, 10, 11);
   v_acc := bik||v_acc;
   i := 1;
   while i <= 23 loop
     n := to_number(SubStr(v_acc, i, 1)) * substr(v_key,i,1);
     If (n > 9) then
        n := n - (trunc(n / 10) * 10);
     end if;
     v_sum := v_sum + n;
     i := i + 1;
   end loop;
   If (v_sum > 9) then
      v_sum := (v_sum - (trunc(v_sum / 10) * 10)) * 3;
   end if;
   If (v_sum > 9) then
      v_sum := v_sum - (trunc(v_sum / 10) * 10);
   end if;
   v_acc := SubStr(v_acc, 4, 8)|| Trim(to_char(v_sum)) || SubStr(v_acc, 13, 11);
   Return v_acc;
end;
/