tecnologia | consultoria | banco de dados

Oracle - Exportar dados para Excel (Spool csv)

Excel.bat:
Arquivo com a configuração de conexão com o banco de dados e o arquivo SQL a ser executado.

SQLPLUS schema@servidor/senha @SQL/Excel.sql

Excel.sql:
Arquivo com as configurações do spool e local para geração do arquivo CSV.

SET UNDERLINE OFF 
SET TERM OFF
SET ECHO OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGESIZE 0
SET LINESIZE 2000
SET VERIFY OFF
SET COLSEP ';'
SPOOL RETORNO/EXCEL.CSV
SELECT
A.EMPLOYEE_ID
,A.FIRST_NAME
,A.LAST_NAME
,A.EMAIL
,A.PHONE_NUMBER
,TO_CHAR(A.HIRE_DATE,'DD/MM/RRRR') AS HIRE_DATE
,A.JOB_ID
,A.SALARY
,TO_CHAR(A.COMMISSION_PCT, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.') AS COMMISSION_PCT
,A.MANAGER_ID
,A.DEPARTMENT_ID
FROM EMPLOYEES A;
SPOOL OFF
EXIT;
Data publicação: 23:53 24/11/2015

Oracle - Expressões regulares

-- Manter apenas números na coluna

SELECT
REGEXP_REPLACE('REGISTRO 123.456.789-10', '[^0-9]+', '')
FROM dual;
-- Resultado: 12345678910
SELECT
REGEXP_REPLACE('ABC12345DEF', '[^[:digit:]]+', '')
FROM dual;
-- Resultado: 12345
SELECT
REGEXP_REPLACE('1A2B3C4D', '\D+', '')
FROM dual;
-- Resultado: 1234

-- Selecionar linhas numéricas

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:digit:]]+$');

-- Selecionar linhas com letras e espaços

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:alpha:][:space:]]+);

-- Contar espaço no início da string antes de algum carácter

SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Four Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
-- Resultado: 4
SELECT
REGEXP_INSTR(' Four Spaces', '\S',1,1) AS Result
FROM dual;
-- Resultado: 4
SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Two Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
--Resultado: 2

-- Extrair string que possui | como delimitador

SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,1) AS Result
FROM dual;
-- Resultado: One
SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,4) AS Result
FROM dual;
-- Resultado: Four

-- Extrair primeiro e último valor da string

SELECT
REGEXP_REPLACE('Leandro Sacramento de Paula', ' .+ ', ' ')
From dual;
-- Resultado: Leandro Paula


CARÁCTER FUNÇÃO
\ Carácter de escape
^ Posição inicial da linha (string)
$ Posição final da linha (string)
[ ] Corresponde ao caracteres listados entre os colchetes
[^ ] Não corresponde aos caracteres listados entre os colchetes
( ) Sub-expressão ou grupo. Pode ser uma string ou uma expressão complexa
. Qualquer caractere ("coringa")
+ Uma ou mais ocorrências da sub-expressão precedente
? Zero ou uma ocorrência da sub-expressão precedente
* Zero ou mais ocorrências da sub-expressão precedente
{n} Número de caracteres exatas da sub-expressão precedente
{m,} Número de ocorrências de pelo menos m vezes da sub-expressão precedente
{m,n} Número de ocorrências de pelo menos m vezes, mas não mais que n vezes da sub-expressão precedente
| Alternativa de padrão de busca, por exemplo ('leandro'|'sacramento')
Data publicação: 01:27 13/11/2015

Oracle - Criar tabela particionada

CREATE TABLE tb_clientes
(
id_cliente NUMBER,
nm_cliente VARCHAR2(200),
ano_mes_cadastro VARCHAR2(6) DEFAULT TO_CHAR(SYSDATE,'RRRRMM')
)
PARTITION BY RANGE (ano_mes_cadastro)
(
PARTITION P_198612 VALUES LESS THAN (198701)
);

Criar partição baseado em data:

CREATE TABLE tb_teste
(
data DATE
)
PARTITION BY RANGE(data)
(
PARTITION P_201312 VALUES LESS THAN (TO_DATE('01/01/2014','dd/mm/yyyy'))
);

Criar tabela com particionamento automático:

CREATE TABLE POS_DATA
(
START_DATE DATE,
STORE_ID NUMBER,
INVENTORY_ID NUMBER(6),
QTY_SOLD NUMBER(3)
)
PARTITION BY RANGE (START_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION POS_DATA_P2 VALUES LESS THAN (TO_DATE('1-07-2007', 'DD-MM-YYYY')),
PARTITION POS_DATA_P3 VALUES LESS THAN (TO_DATE('1-08-2007', 'DD-MM-YYYY'))
);

Adicionar uma nova partição em uma tabela já particionada:
--SEM COMPRESSÃO
ALTER TABLE tb_clientes ADD PARTITION P_198701 VALUES LESS THAN (198702) NOCOMPRESS;

--COM COMPRESSÃO
ALTER TABLE tb_clientes ADD PARTITION P_198701 VALUES LESS THAN (198702) COMPRESS FOR QUERY HIGH;

Publicações relacionadas:
- Oracle - Criar tabela sub-particionada;
- Oracle - Criar tabela com compressão.
Data publicação: 22:09 11/11/2015
Perfil
Olá jovem Padawan, seja bem vindo! Este site foi criado com o intuito de compartilhar um pouco de conhecimento de Tecnologia da Informação e Business Intelligence.

Linkedin  Youtube

"A nova fonte de poder não é o dinheiro na mão de poucos, mas a informação na mão de muitos." (John Naisbitt)


Leandro Sacramento, Todos os direitos reservados - 2012 - 2020