Páginas

quinta-feira, 26 de dezembro de 2013

[PostgreSQL] - Formatação de Data e Hora com as funções TO_CHAR e TO_DATE

Dados do tipo data e hora apresentam muitas variações de formatação. Para o usuário as datas se apresentam como campos textuais, mas para o SGBD, são tipos de dados específicos, com características próprias, representando valores temporais. Para trabalhar com valores deste tipo, os sistemas fazem frequentemente conversões entre esses dois tipos.

Este post apresenta o uso das funções TO_DATE e TO_CHAR para formatar e tratar datas e horas.

* TO_CHAR - Formatando campos do tipo data para texto.

- Exemplo 1: Formatação padrão

postgres=# SELECT TO_CHAR(current_date, 'DD-MM-YYYY');
  to_char 
------------
 17-06-2013
(1 registro)

- Exemplo 2: Formatação padrão utilizando o separador "/"

postgres=# SELECT TO_CHAR(current_date, 'DD/MM/YYYY');
  to_char 
------------
 17/06/2013
(1 registro)

- Exemplo 3: Alterando a ordem de visualização do mês, dia e ano apresentados.

postgres=# SELECT TO_CHAR(current_date, 'DD-MM-YYYY'), TO_CHAR(current_date, 'MM-DD-YYYY'), TO_CHAR(current_date, 'YYYY-MM-YY');
  to_char   |  to_char   |  to_char 
------------+------------+------------
 17-06-2013 | 06-17-2013 | 2013-06-13
(1 registro)

- Exemplo 4: Separando mês, dia e ano em campos distintos.

postgres=# SELECT TO_CHAR(current_date, 'DD') AS DIA, TO_CHAR(current_date, 'MM') AS MES, TO_CHAR(current_date, 'YYYY') AS ANO;
 dia | mes | ano  
-----+-----+------
 17  | 06  | 2013
(1 registro)

- Exemplo 5: Apresentando o mês e o dia por extenso.

postgres=# SELECT TO_CHAR(current_date, 'DAY') AS DIA, TO_CHAR(current_date, 'MON') AS MES;
    dia    | mes
-----------+-----
 MONDAY    | JUN
(1 registro)

- Exemplo 6: Apresentando a hora do sistema formatada.

postgres=# SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');
 to_char
----------
 07:29:13
(1 registro)
- Exemplo 7: Separando hora, minuto e segundos em camops distintos.

postgres=# SELECT TO_CHAR(current_timestamp, 'HH24'), TO_CHAR(current_timestamp, 'MI'), TO_CHAR(current_timestamp, 'SS');
 to_char | to_char | to_char
---------+---------+---------
 07      | 29      | 13
(1 registro)

- Exemplo 8: Usando TO_CHAR para inserção e atualização de dados.

postgres=#
postgres=# CREATE TABLE char_date_tst (momento varchar(10));
CREATE TABLE
postgres=# INSERT INTO char_date_tst (momento) VALUES (TO_CHAR(current_date, 'MM-DD-YYYY'));
INSERT 0 1
postgres=# UPDATE char_date_tst SET momento = '01-05-2010' WHERE momento = TO_CHAR(current_date, 'MM-DD-YYYY');
UPDATE 1
postgres=# SELECT * FROM char_date_tst;
  momento 
------------
 01-05-2010
(1 registro)
* TO_DATE - Formatando campos do tipo texto para data.

- Exemplo 1: Usando TO_DATE para data em formato DD-MM-YYYY.

postgres=# SELECT TO_DATE('30-04-2010', 'DD-MM-YYYY');
  to_date 
------------
 2010-04-30
(1 registro)

- Exemplo 2: Usando TO_DATE para data em formato MM-DD-YYYY.

postgres=# SELECT TO_DATE('04-30-2010', 'MM-DD-YYYY');
  to_date 
------------
 2010-04-30
(1 registro)

- Exemplo 3: Usando TO_DATE para data com separador "/"

postgres=# SELECT TO_DATE('04/30/2010', 'MM/DD/YYYY');
  to_date 
------------
 2010-04-30
(1 registro)

- Exemplo 4: Teste de igualdade entre datas de formatos distintos usando TO_DATE.

postgres=# SELECT TO_DATE('04-30-2010', 'MM-DD-YYYY') = TO_DATE('30-04-2010', 'DD-MM-YYYY') AS IGUALDADE;
 igualdade
-----------
 t
(1 registro)

- Exemplo 5: Usando TO_DATE para inserção e atualização de dados.

postgres=# CREATE TABLE date_tst (momento date);
CREATE TABLE
postgres=# INSERT INTO date_tst VALUES (TO_DATE('04-30-2010', 'MM-DD-YYYY'));
INSERT 0 1
postgres=# UPDATE date_tst SET momento = momento + 1 WHERE momento = TO_DATE('04-30-2010', 'MM-DD-YYYY');
UPDATE 1
postgres=# SELECT * FROM date_tst;
  momento 
------------
 2010-05-01
(1 registro)
* Considerações Finais

Os exemplos acima não cobrem todas as possibilidades, mas podem ajudar nas principais operações que lidem com a formatação de datas e horas no postgresql. A cláusula CAST é uma alternativa para conversão entre estes tipos de dados, mas não realiza formatações sobre os mesmos.

Fonte:
http://postgresqlbr.blogspot.com.br/2013/06/formatacao-de-data-e-hora-com-as.html

[PostgreSQL] - Os Comandos BEGIN, COMMIT e ROLLBACK

O comando BEGIN inicia um bloco de comandos SQL que fazem parte de uma transação. Os comandos realizados após o este comando só são persistidos em disco e seus resultados só são apresentados aos demais usuários do banco após a efetivação com o comando COMMIT.

A transação é finalizada pelo comando COMMIT, o qual dispara a efetivação da transação no banco de dados e torna visível para os demais usuários os resultados da execução dos seus comandos.

O comando ROLLBACK aborta a transação que está em andamento, impedindo que os as alterações nos dados nela realizadas sejam persistidos no banco de dados.

Sintaxe:

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

onde o transaction_mode é um dos listados abaixo:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY

Obs.: os transaction modes serão discutidos em maior profundidade ao se detalhar o comando SET TRANSACTION.

COMMIT [ WORK | TRANSACTION ]


ROLLBACK [ WORK | TRANSACTION ]

Exemplos:

1 - Sintaxe mais simples com BEGIN e COMMIT

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT;

2 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula WORK não tem efeito é é mantida por compatibilidade.

BEGIN WORK;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT WORK;

3 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula TRANSACTION não tem efeito é é mantida por compatibilidade.

BEGIN TRANSACTION;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT TRANSACTION;

4 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK;

5 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula WORK não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK WORK;

6 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula TRANSACTION não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK TRANSACTION;

[PostgreSQL] - Trate com BLOBs e CLOBs Diretamente no PSQL!

Armazenar grandes objetos binários (Binary Large Objects - BLOBs), tais como vídeos, imagens e arquivos PDF, e grandes objetos textuais (Character Large Objects - CLOBs) em um banco de dados é uma atividade que pode apresentar dificuldade maior que a vivenciada em se lidar com tipos de dados tradicionais.

Cada SGBD apresenta sua padronização e o PostgreSQL apresenta seus métodos de armazenamento, exclusão e recuperação de BLOBs e CLOBS. A portabilidade entre SGBDs não costuma ser fácil neste caso.

 Lidar com Large Objects pode não ser tão trivial!


Um recurso bastante simples para lidar com estes grandes objetos dentro do postgres é disponibilizado de dentro do psql, objeto deste post.

Quatro funções permitem operações sobre large objects, viabilizando a sua criação, recuperação, consulta e exclusão:
\lo_import ARQUIVO [COMENTÁRIO] - Importa um arquivo do filesystem (levando em conta a search_path) para o postgresql, atribuindo ao mesmo um identificador de objeto (OID) e um comentário, quando fornecido este parâmetro
\lo_list - Apresenta uma listagem dos objetos binários importados
\lo_export OIDLOB ARQUIVO - Exporta um BLOB ou CLOB do postgres para o filesystem sem removê-lo do postgresql, referenciando-o pelo seu OID
\lo_unlink OIDLOB - Elimina um large object do postgresql com base no seu OID.

Abaixo, sem a preocupação de esgotar este tema, a demonstração do tratamento de BLOBs e CLOBs no postgres via psql.

* Listagem de grandes objetos (large objets) armazenados

postgres-# \lo_list
Objetos grandes
 ID | Descrição 
----+-----------
(0 registro)

* Recuperação dos caminhos da Serch_path, utilizados para recuperar arquivos para o psql




postgres=# SHOW search_path;
  search_path   
----------------
 "$user",public
(1 registro)





* Importação de Arquivos

postgres=# \lo_import arqdoc.doc
lo_import 16400 (OID DO NOVO ARQUIVO IMPORTADO)
postgres=# \lo_import arqpdf.doc 'Arquivo Binario PDF'
não pôde abrir arquivo "arqpdf.doc": Arquivo ou diretório não encontrado
postgres=# \lo_import arqpdf.pdf 'Arquivo Binario PDF'
lo_import 16401
postgres=# \lo_import arqpng.png 'Arquivo PNG'
lo_import 16402
postgres=# \lo_list (LISTAGEM DE ARQUIVOS IMPORTADOS)
       Objetos grandes
  ID   |      Descrição    
-------+---------------------
 16400 |
 16401 | Arquivo Binario PDF
 16402 | Arquivo PNG
(3 registros)

* Exclusão de arquivo previamente importado

postgres=# \lo_unlink 16401
lo_unlink 16401
postgres=# \lo_list
   Objetos grandes
  ID   |  Descrição  
-------+-------------
 16400 | 
 16402 | Arquivo PNG
(2 registros)









* Exportação de arquivo do postgresql para o filesystem

postgres=# \lo_import novofile.pdf
lo_import 16403
postgres=# \lo_export 16403 testeexport.pdf (gera o arquivotesteexport.pdf no filesystem)

* Importação de CLOB

postgres=# \lo_import miniclob.txt 'Arquivo CLOB'
lo_import 16405
postgres=# \lo_list
   Objetos grandes
  ID   |  Descrição   
-------+--------------
 16400 | 
 16402 | Arquivo PNG
 16403 | 
 16404 | 
 16405 | Arquivo CLOB
(5 registros)





* Considerações Finais


Prefiro trabalhar com large objects no filesystem, ao invés de registrá-los no próprio banco de dados.O acréscimo de mais uma camada reduz a portabilidade e a velocidade de acesso, mas tende a aumentar de certa forma a segurança e a facilitar operações de backup e recovery.

Caso se deseje utilizar este recurso na aplicação, utilizando as funções dentro do SQL, o postgresql apresenta o recurso das Server-Side Functions, bastante similar e que oferece maior controle.

E você, como lida com os seus BLOBs e CLOBs?

Fonte:
http://postgresqlbr.blogspot.com.br/2013/04/trate-com-blobs-e-clobs-diretamente-no.html

Recuperando Informações de Sessão no PostgreSQL

Qual é o banco de dados corrente? E qual o usuário corrente? Qual é exatamente a versão do postgres que estamos utilizando? Estas informações são úteis para se poder trabalhar com bancos de dados, e são fornecidas por funções informacionais de sessão. Vamos apresentar neste post uma listagem com as funções disponibilizadas no postgres e alguns exemplos de utilização:

current_database()nameNome do banco de dados corrente.
current_schema()nameNome do esquema corrente.
current_schemas(boolean)name[]Nomes dos esquemas no caminho de procura incluindo, opcionalmente, os esquemas implícitos.
current_usernameNome do usuário do contexto de execução corrente.
inet_client_addr()inetEndereço da conexão remota.
inet_client_port()int4Porta da conexão remota.
inet_server_addr()inetEndereço da conexão local.
inet_server_port()int4Porta da conexão local.
session_usernameNome do usuário da sessão.
usernameEquivalente à função "current_user"
version()textInformações relativas à versão corrente do PostgreSQL

* Banco de dados e Esquema

Para recuperar informações de banco de dados e esquema, utilizam-se as funçõescurrent_database(), current_schema() e current_schemas(boolean).

Exemplo 1: Dados dos esquemas.

postgres=# SELECT current_database(), current_schema();
 current_database | current_schema
------------------+----------------
 postgres         | public
(1 registro)
Exemplo 2: Caminho de procura com e sem os esquemas implícitos.

postgres=# SELECT current_schemas(true) as SCHEMAS_TODOS, current_schemas(false) AS SCHEMAS_EXPLICITOS;
    schemas_todos    | schemas_explicitos
---------------------+--------------------
 {pg_catalog,public} | {public}
(1 registro)

* Informações de Conexão

Permitem a recuperação dedados sobre os servidores e clientes utilizados na conexão, e as portas de comunicação utilizadas. Funções: inet_client_addr(), inet_client_port(), inet_server_addr() einet_server_port().

Exemplo 3: Informações da conexão corrente.

postgres=# SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port();
 inet_client_addr | inet_client_port | inet_server_addr | inet_server_port
------------------+------------------+------------------+------------------
                  |                  |                  |               
(1 registro)

* Dados do usuário da sessão.

São consultados com as funções sem parênteses current_user, session_user e user. 

Exemplo 4: Dados do usuário e mudança de usuário.
postgres=# SELECT current_user, session_user, user;
 current_user | session_user | current_user
--------------+--------------+--------------
 postgres     | postgres     | postgres
(1 registro)

postgres=# SET SESSION AUTHORIZATION 'user1';
SET
postgres=> SELECT current_user, session_user, user;
 current_user | session_user | current_user
--------------+--------------+--------------
 user1        | user1        | user1
(1 registro)

* Versão do PostgreSQL

Exemplo 5: Consulta à versão do postgresql.

postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.1 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit
(1 registro)


* Recuperando todos os dados da sessão

Exemplo 6: Consulta a todas as informações de sessão:

postgres=# SELECT 'BANCO    : ' || current_database() AS INFO UNION SELECT 'ESQUEMA  : ' || current_schema() AS INFO UNION
SELECT 'ESQUEMAS : ' || CAST(current_schemas(true) AS VARCHAR) AS INFO UNION
SELECT 'USUARIO  : ' || current_user AS INFO UNION
SELECT 'SES. USR.: ' || session_user AS INFO UNION
SELECT 'USER     : ' || user AS INFO UNION
SELECT 'CLI. ADR.: ' || inet_client_addr() AS INFO UNION
SELECT 'CLI. POR.: ' || inet_client_port() AS INFO UNION
SELECT 'SER. ADR.: ' || inet_server_addr() AS INFO UNION
SELECT 'SER. POR.: ' || inet_server_port() AS INFO UNION
SELECT 'VERSAO   : ' || version() AS INFO;
                                                        info                                                        
--------------------------------------------------------------------------------------------------------------------

 ESQUEMAS : {pg_catalog,public}
 ESQUEMA  : public
 USER     : postgres
 SES. USR.: postgres
 USUARIO  : postgres
 VERSAO   : PostgreSQL 9.2.1 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 32-bit
 BANCO    : postgres
(8 registros)

Fonte:http://postgresqlbr.blogspot.com.br/2012/11/recuperando-informacoes-de-sessao-no.html

sexta-feira, 20 de dezembro de 2013

[ORACLE] - Desabilitar expiração de senha no Oracle


-- Desabilita expiração de senha no Oracle --
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;


-- Seleciona usuário/ data_expiração / status_conta --
select username,expiry_date,account_status from dba_users;

-- Altera senha e desbloqueia a conta --
alter user teste identified by "teste123" account unlock;