Oracle Convert Number into Days, Hours, Minutes

There’s a little trick…
Today I had to convert a “number” of minutes into hours:minutes format. Something like convert 570 minutes in format hour:minutes. As you know, 570/60 is “9,5” and should be “9:30”.

Lets use 86399 seconds (23:59:59) as example:

I began testing “to_char(to_date)” functions:
boesing@db>select to_char(to_date(86399,'sssss'),'hh24:mi:ss') formated from dual;

FORMATED
——–
23:59:59

Ok, it works. But using “seconds past midnight” (sssss). By the way, it works between 0 and 86399 only:

boesing@db> select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual;
select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-01853: seconds in day must be between 0 and 86399

The problem remains. How to use minutes in 3 digits (570 minutes -> 9:30), for example?
The best way I solve was:

--- Seconds in hours:minutes:seconds
--- If you comment the first "TO_CHAR" line, can be minutes in hours:minutes too..
select
TO_CHAR(TRUNC(vlr/3600),'FM9900') || ':' || -- hours
TO_CHAR(TRUNC(MOD(vlr,3600)/60),'FM00') || ':' || -- minutes
TO_CHAR(MOD(vlr,60),'FM00') -- second
from dual;

It always works. 🙂

boesing@db>select
2 TO_CHAR(TRUNC(86399/3600),'FM9900') || ':' || -- hours
3 TO_CHAR(TRUNC(MOD(86399,3600)/60),'FM00') || ':' || -- minutes
4 TO_CHAR(MOD(86399,60),'FM00') -- second
5 from dual;

TO_CHAR(TRUNC
————-
23:59:59

boesing@db>select
2 TO_CHAR(TRUNC(570/3600),’FM9900′) || ‘:’ || — hours
3 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — minutes
4 TO_CHAR(MOD(570,60),’FM00′) — second
5 from dual;

TO_CHAR(TRUNC
————-
00:09:30

boesing@db>select
2 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — hours
3 TO_CHAR(MOD(570,60),’FM00′) — minutes
4 from dual;

TO_CHAR
——-
09:30

Any better way? Leave a comment. Thanks!

Matheus.

ORA-24777: use of non-migratable database link not allowed

Hoje reencontrei este erro e, como de costume, já havia esquecido qual era a solução. Por isso fica aqui minha anotação. 🙂
Tive que puxar mais da memória do que qualquer outra coisa, pois todos os links da primeira página do Google tinham apenas o stack do erro mas sem solução. Que na realidade é bem simples:

O erro ocorre devido sessão utilizando shared connection (XA) utilizar DBLink com conexão dedicated. O uso de XA no JDBC é uma configuração do datasource, caso você esteja utilizando weblogic, jboss, tomcat ou outro com suporte a java.
A solução que adotei até hoje consiste setar o “host” do DBLink ou a entrada de TNS utilizada pelo DBLink no arquivo tnsnames do BD de origem para utilizar tipo de conexão “shared” para conexão remota.

Ex Antes:  (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=DEDICATED)))
Ex Depois:  (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=SHARED)))

Segundo o stackoverflow, pode ser resolvido criando o database link como shared.
Ex: CREATE SHARED DATABASE LINK CONNECT TO bob IDENTIFIED  BY “MyNewPasswd1” AUTHENTICATED BY jim IDENTIFIED BY “JimsPass23” USING ‘DB01’;

Na realidade a segunda solução me pareceu muito mais elegante. Mas só percebi que existia depois de já ter resolvido pela primeira abordagem.
Se acontecer novamente, testo da segunda forma e comento aqui. Prometo. 😉

Ah!
Acho que não é necessario mencionar que o BD destino do DBLink precisa estar escutando em algum serviço com suporte a XA, né?
Não é o assunto deste post, então não vou entrar em detalhes. Mas basicamente você precisa setar para um valor diferente de zero os parâmetros: max_shared_servers; shared_servers; max_dispatchers e dispatchers e subir o serviço do dispartcher no listener. Taca no Google aí que essa parte é barbada!

Abraço!
Matheus.

Acentuação no SQLPLUS com Windows em inglês

Hoje perdi tempão pra fazer funcionar a acentuação no SQLPLUS com Windows em inglês. Resolvi compartilhar.
E não. Não basta escolher o idioma certo na instalação do client. Depende realmente do idioma do SO.

Cenário:
Server Windows com client Oracle 11.2 x64 instalado. Necessário usar esta versão do OracleClient visando reconhecimento de scan listeners. Restrições da situação demandavam utilização do sqlplus, obrigatoriamente.

Convém destacar:
– O escopo deste post é fazer com que os dados sejam ‘escritos’ corretamente no BD, e não corrigir dados que já estão gravados incorretamente.
– O escopo deste post não é fazer a conversão de charset do BD, e sim a configuração de nls_lang do client.
– Antes de reclamar que não tá funcionando, dá uma olhadinha no bônus, lá no final do post.

Basicamente, concluí que a melhor configuração para resolver o problema consiste em:
1) Setar o CHCP para 1252 (código de página)
2) Setar variável NLS_LANG do server para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
3) Setar NLS_LANG do Oracle para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

OBS: Funciona com outras combinações, mas optei pelo BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252 pois segundo a Oracle (http://docs.oracle.com/html/B13804_02/gblsupp.htm) é o que temos como ‘oficial’ pro “Português (Brazil).”

Vale lembrar que esta configuração pode ser feita diretamente via prompt de comando antes da utilização do sqlplus. Aí o problema já estaria resolvido (:D).
Até aqui o Eduardo Legatti me ajudou 100%. Mas agora pra que a configuração seja permanente no servidor, como faz?

Faz assim:

1)  Configuração CHCP para 1252 por Default:
– Run > regedit

Em [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]:
– Setar o (Default) para c_1252.nls

Default CHCP

–  SetarOEMCP para 1252

set OEMCP

2) Configurar NLS_LANG do Oracle:

Em [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_]:
– Setar NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

NLS_LANG Oracle
3) Configurar o NLS_LANG como variável de ambiente:

Em [My Computer > Properties > Advanced System Settings > Environment Variables]:
– Setar o NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

set OEMCP
4) Reboot do Servidor:

– Para efetivar mudanças no REGEDIT é necessário reiniciar o server.

Era isso!
Espero que seja útil para mais alguém, assim como foram úteis pra mim os links abaixo:

Nota: Greek Characters Are Not Displayed Correctly In A DOS Window (Doc ID 208902.1)
http://www.leniel.net/2010/03/oracle-nlslang-character-set-encoding.html#sthash.KPuB7Rtg.YxcOtWFd.dpbs
http://docs.oracle.com/html/B13804_02/gblsupp.htm
http://nagaappani.blogspot.com.br/2013/05/setting-nlslang-on-windows-for-oracle.html
http://ss64.com/nt/chcp.html
http://www.fabioprado.net/2012/11/configurando-national-language-support.html
http://tosemopcao.blogspot.com.br/2009/11/bd-oracle-acentos-no-10g-xe.html
http://eduardolegatti.blogspot.com.br/2011/01/habilitando-o-suporte-acentuacao-no.html

Bônus)
Embora as configurações estejam adequadas, a fonte (tipo de letra) do DOS (prompt de comando) pode não suportar os caracteres especiais e acentos. Basta trocar pra “Lucida Console”:

Fonte_Caracteres

Fechado?

Até breve!