SQL - Как выбрать то, что не существует

Столкнулся с проблемой. Положим, есть у нас таблица, DUMMY_TABLE.
Каким образом  выбрать поле из таблицы по ИД, или же строку 'NOT EXISTS' если такой записи не существует? При этом использовать можно только SQL, никакого процедурного программирования.

В результате родился такой запрос:


SELECT ID from DUMMY_TABLE
  where id=1
UNION
SELECT
 'NOT EXISTS' from DUAL WHERE NOT EXISTS(
    select ID from DUMMY_TABLE WHERE ID=1)

Работает! Вот как бы его только поэлегантнее представить?

Аналитические функции Oracle SQL

Сталкиваешься с текими запросами не каждый день, однако знать про эти возможности просто необходимо.
http://orafaq.com/node/55

В статье доходчиво объясняется, как и для чего стоит применять функции :

• OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )
• ROW_NUMBER( )
• LEAD
• FIRST_VALUE() OVER
• и т.д.
  

Oracle AQ - Listener

Сегодня решал очередную проблему. Проблему подкинули наши деловые партнёры. Подкинуть-подкинули, а решать пришлось мне.
Словом, в очередь Oracle AQ пишется сообщение в XML-формате. Сообщение необходимо передато в другой програмный модуль, но при этом добавив к нему Namespace. То есть, необходимо каким-то образом слушать очередь входящих сообщений, трансформировать их, и передавать дальше. Вот как я это решил:

-- Создал очередь для выходящих сообщений
BEGIN
DBMS_AQADM.create_queue_table (
queue_table => 'jmsuser.JMS_QUEUE_OUT_TABLE', queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');

DBMS_AQADM.create_queue (
queue_name => 'jmsuser.JMS_QUEUE_OUT',
queue_table => 'jmsuser.JMS_QUEUE_OUT_TABLE');

DBMS_AQADM.start_queue (
queue_name => 'jmsuser.JMS_QUEUE_OUT',
enqueue => TRUE);
END;
/

--
-- Процедура - обработчик, читает из входю очереди, пишет в выход. очередь
--
-------------------------------------------------------
CREATE OR REPLACE procedure notifyAbout(
 context raw,
 reginfo sys.aq$_reg_info,
 descr sys.aq$_descriptor,
 payload raw,
 payloadl number)
AS
 dequeue_opts   DBMS_AQ.dequeue_options_t;
 enqueue_opts   DBMS_AQ.enqueue_options_t;
 message_props  DBMS_AQ.message_properties_t;
 message_handle    RAW(16);
 msg           SYS.AQ$_JMS_TEXT_MESSAGE;
 msqOut        SYS.AQ$_JMS_TEXT_MESSAGE;
 xml VARCHAR2(32000 CHAR);
BEGIN
 -- get the consumer name and msg_id from the descriptor
 dequeue_opts.msgid := descr.msg_id;
 dequeue_opts.consumer_name := descr.consumer_name;

 -- Dequeue the message
 DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
                 dequeue_options => dequeue_opts,
                 message_properties => message_props,
                 payload => msg,
                 msgid => message_handle);
   
 dbms_output.put_line('Dequeued '||message_handle) ;

 -- Change the payload
 msg.get_text(xml);
 xml := replace( xml, '<:RESPONSE', '<:RESPONSE xmlns="http://www.lodestarcorp.com" ');

 -- Enqueue the new message
 msqOut := sys.aq$_jms_text_message.construct;
 msqOut.set_text(xml);

 DBMS_AQ.enqueue(queue_name => 'jmsuser.JMS_QUEUE_OUT',
                   enqueue_options => enqueue_opts,
                 message_properties => message_props,
                 payload => msqOut,
                 msgid => message_handle);
 DBMS_OUTPUT.put_line (message_handle);       

 commit;
END;
/

--
-- Регистрация листенера
--
DECLARE
 reginfo1     sys.aq$_reg_info;
 reginfolist  sys.aq$_reg_info_list;

BEGIN
  -- register for the pl/sql procedure notifyCB to be called on notification
 reginfo1 := sys.aq$_reg_info('jmsuser.JMS_QUEUE',
                    DBMS_AQ.NAMESPACE_AQ,
                    'plsql://jmsuser.notifyAbout',
                    HEXTORAW('FF'));

 -- Create the registration info list
 reginfolist := sys.aq$_reg_info_list(reginfo1);

 -- do the registration
 sys.dbms_aq.register(reginfolist, 1);

END;

ESB: форматирование даты и времени (xp20:format-dateTime)

Озадачился этим вопросом. Гугл нам в помощь, выдал сразу вариант решения:
ora:formatDate
Всем хороша функция, и простая, и в качестве шаблона берёт всеми любимый SimpleDateFormat из Явы. Да по закону бутерброда отказывается работать в ESB.
Дальнейшие поиски привеля меня к другому решению. Можно пользовать XPath Extension Functions :
xp20:format-dateTime(/ns0:EndDate, '[Y0001]-[M01]-[D01]' )
Работает на ура.

Oracle AQ

Архитектор выступил с инновационной идеей: для того, чтобы передать из базы сообщение, совсем не обязательно пользовать СОАП-запросы. Можно использовать и асинхронные механизмы Oracle Advanced Queueing.
Сижу, изучаю Advanced Queueing по примерам.

Самый примитивный пример:

CREATE OR REPLACE TYPE event_itr_type AS OBJECT (
xpId Number,
tncpi3Id Number,
startTime Date,
endTime Date,
type VARCHAR2(32)
);
/

BEGIN
DBMS_AQADM.create_queue_table (
queue_table => 'vht_tmp.event_queue_tab', queue_payload_type => 'vht_tmp.event_itr_type');

DBMS_AQADM.create_queue (
queue_name => 'vht_tmp.event_queue',
queue_table => 'vht_tmp.event_queue_tab');

DBMS_AQADM.start_queue (
queue_name => 'vht_tmp.event_queue',
enqueue => TRUE);
END;
/



DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg event_itr_type;
BEGIN
l_event_msg := event_itr_type(123, 456789, SYSDATE, SYSDATE+1/24, 'PLAAN');

DBMS_AQ.enqueue(queue_name => 'vht_tmp.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);

COMMIT;
END;
/

XML in Oracle Database

Продолжаю изучать возможности вызова ВебСервисов из базы данных. В Предыдущем посте я добился этого с помощью UTL_HTTP. Вот только обработка ответа оставалась для меня достаточно туманной темой. Сегодня я проверил в действии возможности Oracle по обработке XML. Для этого существует специальный тип данных - xmltype.

Выглядит примерно так:

-- Декларация
DECLARE
resp XMLType;
...
-- Преобразуем СОАП - ответ в xml
resp := xmltype.createxml(response_env);

-- С помощью XPATH извлекаем нужную ноду
resp := resp.extract('/soapenv:Envelope/soapenv:Body/ns1:response/ns1:status/text()',
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/ xmlns:ns1="http://www.energia.ee/archibus/AfmRegisterMileage');

-- Если нода не найдена - то она нулл
if(resp IS NULL) then
DBMS_OUTPUT.put_line ('NOK');
else
DBMS_OUTPUT.put_line ('OK!!!');
-- Делаем с xml-ом что нам надо
DBMS_OUTPUT.put_line (resp.getStringVal());
end if;

с Днём Знаний!

Кстати, о знаниях.

Работая над одним очень примитивным ЕСБ - проектомЮ столкнулся с проблемой: как с помощью ХСЛТ скопировать один ХМЛ документ в другой. Ждевелопер со своими визардами и автомапами мне не помог. Помог Гугл:

<xsl:template match="/">
 <xsl:copy-of select="@*|node()"/>
</xsl:template>

Вторая проблема - как создать новую System в ESB Console. По умолчанию создаётся имя System0. Влё просто: надо кликнуть на имени - и оно превратится в редактируемое поле. Руки оторвать за такой "дружелюбный" интерфейс, час убил на решение этой проблемы.

Как вызвать ВебСервис из базы Oracle

Самый простой способ - воспользоваться UTL_HTTP:

declare
http_req utl_http.req;
http_resp utl_http.resp;
request_env varchar2(32767);
response_env varchar2(32767);
begin
request_env:='Твой SOAP запрос в виде текста XML';
  dbms_output.put_line('Length of Request:' || length(request_env));
  dbms_output.put_line ('Request: ' || request_env);
  http_req := utl_http.begin_request('http://a05198:8080/afm_register_mileage/services/AfmRegisterMileage', 'POST', utl_http.HTTP_VERSION_1_1);
  utl_http.set_authentication(r => http_req,
     username => 'LOGIN',
     password => 'PASSWoRD',
     scheme => 'Basic',
     for_proxy => FALSE);
  utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
  utl_http.set_header(http_req, 'Content-Length', length(request_env));
  --utl_http.set_header(http_req, 'SOAPAction', '"http://tempuri.org/LogMessage"');
  utl_http.write_text(http_req, request_env);
  dbms_output.put_line('1');
  http_resp := utl_http.get_response(http_req);
  dbms_output.put_line('Response Received');
  dbms_output.put_line('--------------------------');
  dbms_output.put_line ( 'Status code: ' || http_resp.status_code );
  dbms_output.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );
  utl_http.read_text(http_resp, response_env);
  dbms_output.put_line('Response: ');
  dbms_output.put_line(response_env);
  utl_http.end_response(http_resp);

resp := xmltype.createxml(response_env);
 resp := resp.extract('/soapenv:Envelope/soapenv:Body/ns1:response/ns1:status/text()',
         'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/ xmlns:ns1="http://www.energia.ee/archibus/AfmRegisterMileage');
 if(resp IS NULL) then
            DBMS_OUTPUT.put_line ('NOK');
  else
       DBMS_OUTPUT.put_line ('OK!!!');
       DBMS_OUTPUT.put_line (resp.getStringVal());
  end if;

end;

Существуют и другие, "более гибкие" способы, например через JPublisher, UTL_DBWS, но мне кажется , что вышеописанный пример проще.