Dicas Gerais de SQL*Loader

-O TOAD versão 9 (não sei as anteriores) possui um SQLLOADER Wizard

-Procedures PL/SQL também poderia ser usada, mas via de regra um SQL puro simples e direto é
**muito muito ** mais eficiente que PL/SQL.

"Regra Geral" de Validação / Correção de dados para carga, dos experts:

PRIMEIRO tentar em SQL apenas

SEGUNDO tentar pra stored PL/SQL

TERCEIRO tentar java ou C dentro do banco

EM último caso aí sim usar a linguagem cliente

Chamando SQL*Loader dentro de uma procedure

sendo o sql*loader um simples programa-executável externo,
a pergunta passa a ser "como executar um programa externo ao Pl/SQL",
e a resposta é : é *** FACÍLIMO **** fazer isso, há muitas muitas
opções mas infelizmente a pessoa ** terá que ** escrever e usar algo
externo ao PL/SQL, nativo em PL/SQL simplesmente não há como... As
opções seriam dependem do banco usado, mas de modo geral : na versão
10g do
banco pode ser um job, na 9i e acima pode ser external procedure (ie,
chamar uma .DLL ou no caso de unix um .so), na 8i e acima pode ser
uma rotina escrita em java, OU em todas essas e nas anteriores pode-se
ter um programa
externo (shell script mesmo poderia ser em unix), em pooling
esperando receber comandos via PIPE ou arquivo de texto - pesquise em
asktom por essas palavras-chave que vc acha exemplo de TODAS as
técnicas citadas.

Links uteis para Oracle SQL*Loader

Versao SQL*Loader Oracle 8i


Versao SQL*Loader Oracle 9i


PDF do doc SQL*Loader Oracle 9i - Oracle Database Utilites

nolog

/ is a default logon using operating system authentication

/nolog establishes no initial connection to Oracle Database.

When you connect using sysdba or sysoper, then you are sys or public user, no matter what username/password you provide :


C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 06:11:39 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> show user
USER is ""

SQL> conn / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 06:13:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> conn / as sysoper
ERROR:
ORA-01031: insufficient privileges

SQL> CONN SCOTT/TIGER AS SYSDBA
Connected.
SQL> SHO USER
USER is "SYS"
SQL> CONN WILLIAM/pass AS SYSOPER
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.




-------------------------------
Comandos em sequencia para inicio do banco de dados manual

>sqlplus /nolog
>conn / as SYSDBA
>startup


Clarificando ainda mais:

Clique aqui

Começando em PL/SQL

Os códigos abaixo sao codigos de PL/SQL para Oracle.
Abrir o sqlplus copiar e colar...
depois so digitar / e pressionar. Um script de cada vez.


Hello World em pl/sql
---------------------inicio script--------------
set serveroutput on

BEGIN
DBMS_OUTPUT.put_line('Hello World');
END;
---------------------fim script-----------------


Codigo para mostrar o quadrado de cada valor de 0 a 10.

---------------------inicio script-----------------
DECLARE
square INT;
BEGIN
FOR i IN 1..10 LOOP
 square := i * i;
 DBMS_OUTPUT.put_line( to_char(square)  ' ' );
END LOOP;
END;

---------------------fim script-----------------

Falha de instancia ou mudança de IP

Quando a instancia nao esta mais rodando ou mudanca de IP do server da o seguinte erro:

SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-12571: TNS:packet writer failure

SQL> /
select sysdate from dual
*
ERROR at line 1:
ORA-03114: not connected to ORACLE

SQL> conn william/xxxxx@delldb9i
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Configurando o ambiente Oracle

VI como editor default do sqlplus

Para aqueles que adoram esse editor, como eu, é muito bom para praticar os comandos:

1 baixar e instalar VI para windows.
2 colocar no path do windows C:\vim63\
3 No sqlplus executar o comando define _editor= "C:\vim63\vim.exe"
4 Se funcionou com o comando ed, colocar o comando do passo 3 no arquivo para ser permanente

C:\oracle\ora90\sqlplus\admin\glogin.sql

Arquivos *.bat (windows) - Para Inicializacao e finalizacao num so clique.

Para inicializacao ou finalizacao dos servicos oracle, coloque esses arquivos no desktop para facil acesso:

Oraclestart.bat:
net start OracleOraHome90TNSListener
net start OracleServiceDELLDB9I

OracleStop.bat:
net stop OracleOraHome90TNSListener
net stop OracleServiceDELLDB9I

ORA-27101: shared memory realm does not exist

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 03:12:33 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Solution :

sqlplus /nolog

Uma vez no sqlplus, veja a minha sessão abaixo :

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> quit

A partir daqui a instancia volta a funcionar !

Melhorando SQL*Loader exemplo

Vi que o exemplo anterior de sql*loader esta com alguns problemas então decidi fazer um melhor, já q nada se cria e tudo se copia (e melhora). Acho q vale a pena fazer o exemplo abaixo, eh muito fácil.

Boa carga...


No sqlplus:
1) VERIFIQUE SE O USUARIO SCOTT ESTA HABILITADO E A SENHA ESTA CORRETA "TIGER" NO SQLPLUS

2) CRIE UMA TABELA PARA Seu TESTE, PARA NAO MODIFICAR A DE EXEMPLO. DIGITE:"CREATE TABLE EMP2 AS SELECT * FROM EMP;"

3) O ORACLE RECLAMARÁ QUE A TABELA CONTEM DADOS, ENTAO DIGITE: "TRUNCATE TABLE EMP2;"

No Windows:
4) ABRA O NOTEPAD, COLOQUE O CONTEUDO ABAIXO E SALVE COM NOME c:\LOADER.CTL

CONTEUDO DO MEU ARQUIVO LOADER.CTL
-----------------------------------INICIO DO ARQUIVO---------------------------------------------
load data
infile 'c:\mydata.csv'
into table emp2
fields terminated by "," optionally enclosed by '"'
( empno, ename, sal, deptno )
-----------------------------------FIM DO ARQUIVO---------------------------------------------

ABRA O NOTEPAD NOVAMENTE, COLOQUE O CONTEUDO ABAIXO E SALVE COM NOME c:\mydata.csv

1001,"Scott Tiger", 1000, 40
1002,"Frank Naude", 500, 20


5) Digite esse comando no DOS (command prompt) colocando o prompt no c:\

sqlldr scott/tiger control=loader.ctl

Isso gerará um log (c:\LOADER.LOG) onde será descrito o evento de carga.

6) vERIFIQUE O c:\LOADER.LOG abrindo o arquivo, vc verá o que causou problema e se as linhas foram inseridas.

CONTEUDO DO MEU ARQUIVO LOADER.LOG:

-----------------------------------INICIO DO ARQUIVO---------------------------------------------

SQL*Loader: Release 9.0.1.1.1 - Production on Thu Aug 9 13:30:13 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control File: loader.ctl
Data File: c:\mydata.csv
Bad File: mydata.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table EMP2, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
SAL NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER


Table EMP2:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Thu Aug 09 13:30:13 2007
Run ended on Thu Aug 09 13:30:13 2007

Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.03

----------------------------------FIM DO ARQUIVO----------------------------------------------

7) No sqlplus Verifique os dados na tabela emp2 com o usuario scott/tiger
>select * from emp2

Voilà


#ATENÇAO#
SE ESSA MENSAGEM TE AJUDOU, DEIXE UMA MENSAGEM ANONIMA ME XINGANDO
#########

Scritps substituindo ferramenta ETL

Existem empresas que não possuem ferramenta de ETL (Extration Tranformation Loading), o caso mais comum para quem usa o Oracle, é usuar o SQL*Loader e scripts pl/sql.

Assim, deixo um link de SQL*Loader:

basico sql*loader

e programação pl/sql:

pl/sql


Bom treino.