Deleta a tabela com uma condição

[FALL]

Pessoal, preciso montar um script que delete a tabela somente se ela existir. Resumo: Utilizo uma tabela de um terceiro que não está nem aí se ela foi recriada ou não ( sofre drop e novo create todos os dias ).
Faço create table utilizando alguns campos dessa que de vez em quando existe e de vez em quando some.
Pensei(óóóó):
Vou deletar a minha atual, SOMENTE SE, a outra existir.

Segue o que fiz e o erro que aparece:
Esou utilizando Oracle 9i e fazendo o script no Command Window pois vou deixar um bath rodando todos os dias pela manhã e nunca mais quero saber disso...

Selecionar tudo

DECLARE 
QTD_REG NUMBER(9) :=0;

BEGIN
	SELECT COUNT(*)*1
		INTO QTD_REG
		FROM TESTE;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			QTD_REG :=0;
END;
/

BEGIN
IF QTD_REG > 6  THEN
		DROP TABLE TESTE2;
    COMMIT;
		CREATE	TABLE TESTE2 AS
		SELECT *
		FROM TESTE;
    COMMIT;
END IF;
END;
EXIT;
/

Resultado do erro:

Selecionar tudo

ORA-06550: line 3, column 3:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge
   <a single-quoted SQL string> pipe

Pessoal, conto com vocês. Qualquer dica será muito be vinda.

[dr_gori]

Não pode usar comandos DDL com PL/SQL.
Tem que fazer assim:

Selecionar tudo

execute immediate 'Drop table TESTE2';

O mesmo pra CREATE TABLE.

Mas sinceramente, é muito estranho ficar fazendo DROP e CREATE...

[FALL]

Já aconteceu algo novo...
A mensagem foi:

Selecionar tudo

PL/SQL procedure successfully completed

Só que ele não deletou e não criou uma nova.

Fatos:
1 - tabela teste2 não existe;
2 - tabela teste tem 5 registros;
3 - No IF a condição é mais de 4, ou seja deveria criar a teste2.
script:

Selecionar tudo

DECLARE 
QTD_REG NUMBER(9) :=0;

BEGIN
	SELECT COUNT(*)*1
		INTO QTD_REG
		FROM TESTE;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			QTD_REG :=0; 
IF QTD_REG > 4  THEN
		execute immediate 'DROP TABLE TESTE2';
    COMMIT;
		execute immediate 'CREATE	TABLE TESTE2 AS	SELECT * FROM TESTE';
    COMMIT;
END IF;
END;
/

Obs.: Quanto ao estranho, em telefonia nada é estranho...

[dr_gori]

OK.
1.

Selecionar tudo

SELECT COUNT(*) 

nunca vai cair num exception.
2.

Selecionar tudo

SELECT COUNT(*)*1  

porque *1 ?

3. Todo seu código está dentro do bloco de exception. Como o excpetion NUNCA vai ser disparado (item 1), seus creates nunca vão rodar.

:-/

[FALL]

Me expressei mal, o código atribui a quantidade de linhas da TESTE na variável QTD_REG.
Agora uma pergunta, se ela não existir, o scritp continuará?

Pois aí é que entra o IF, se a TESTE existe então a variável QTD_REG tem mais de 4 registros então ela irá deletar a TESTE2 para criar uma nova TESTE2.

[dr_gori]

Informações extras:
* Se a tabela não existe, não vai cair num NO DATA FOUND. Cairia num WHEN OTHERS.

Porque você não testa se a tabela existe ou não pelo dicionário de dados ?

Selecionar tudo

SELECT 5
INTO QTD_REG 
FROM ALL_TABLES
WHERE TABLE_NAME='TESTE' AND OWNER='DONO';

esse sim vai gerar um exception when no_data_found;

[Trevisolli]

É isso aí Dr_Gori.

Uma outra Dica:
Após comandos DDL, não é necessário um COMMIT.
Ele já realiza implicitamente.

[FALL]

Vou fazer o teste no decorrer do dia e retorno. Por favor não me abandone... hehehehehe

[FALL]

Pessoal, vocês são os caras... fiz uma pequena modificação pois interessante que isso:

Selecionar tudo

execute immediate 'DROP TABLE TESTE2';

só funciona se a tabela existir mesmo. Então fiz uma modificação para confirmar se as duas existem.

Essa dica da ALL_TABLES foi show...
Fiquei um pouco confuso porque tenho scripts que tem o Drop e funcionam normalmente sem precisar por o execute immediate. Além disso, com o Drop, se a tabela não existe ele indica o erro no log mas continua executando o script. Com o execute immediate se ele não encontrar a tabela dará erro e não fará mais nada.

Enfim muito obrigado mesmo e fiquei fã... não visita os foruns, agora vou ficar mais assiduo, até que a empresa decida bloquear este site também.
Um abraço e valeu...

Segue o exemplo que funcionou perfeitamente.

Selecionar tudo

DECLARE 
QTD_T1 NUMBER :=0;
QTD_T2 NUMBER :=0;

BEGIN
     SELECT 1 INTO QTD_T1 FROM ALL_TABLES 
     WHERE TABLE_NAME='TESTE' AND OWNER='USUARIO';
     SELECT 1 INTO QTD_T2 FROM ALL_TABLES
     WHERE TABLE_NAME='TESTE2'AND OWNER='USUARIO';
     
IF (QTD_T1 =1 AND QTD_T2 =1) THEN
		execute immediate 'DROP TABLE TESTE2';
		execute immediate 'CREATE	TABLE TESTE2 AS	SELECT * FROM TESTE';
END IF;

END;
/

[Toad]

Cara, só por curiosidade, qual a necessidade de criar a tabela e dar o drop?

Pergunto isso pois pode existir uma maneira mais simples [talvez mais correta ou de melhor performance] de resolver seu problema.

[FALL]

Pois é ... como havia falado... em telefonia...
temos várias tabelas com milhões de registros. As vezes queremos alguns dentre esses milhões que se tornam milhares e desses milhares queremos outros dados de outras tabelas de outros milhões de registros.
Resumindo: Na pratica, vimos que é mais rápido drop e create do que delete e insert.

Agradeço pela dica, sabemos que não é a política saudável de TI, e é por isso que não estou em TI, estou na parte que resolve rápido o problema e não naquela que fica esperando uma OS ser aberta, analisada, etc...
Sei também que tem empresas que fazem de um TI algo ágil e rápido, mas estou na segunda operadora das grandes e não temos isso aqui.

[Toad]

FALL

temos várias tabelas com milhões de registros. As vezes queremos alguns dentre esses milhões que se tornam milhares e desses milhares queremos outros dados de outras tabelas de outros milhões de registros.

Eu e muitos aqui do Forum vivenciamos essa realidade de milhões de registros diariamente. Acredito ser de senso comum que a prática de drop e create não é mais eficiente que delete e insert e update.

O que acontece é que existe a necessidade de se resolver o problema. Porém, a qual custo? Não estou aqui para defender metodologias como Programação Ágil ou UML ou o que seja, tampouco questionar o interesse de sua empresa na Área de TI. De acordo com o que você disse, esse interesse é totalmente secundário, o que é uma pena. O ponto que talvez possa receber uma atenção no futuro é: Talvez uma análise minuciosa agora pode prover uma solução otimizada que evita surpresas no futuro.

Mas, como você disse, nem sempre isso é possível. E como eu disse, é realmente uma pena.

Bom, espero que seu problema seja resolvido. E estamos aqui para ajudar.

Boas festas.

[FALL]

Concordo plenamente com você. Quanto estamos perdendo com essas praticas não sabemos dizer.
Um pouco de filosofia de um camarada que trabalhava com a gente.

A grande sacada na telefonia é que vendemos o TEMPO!
Se eu demorar muito para trazer uma solução, talvez o Fulano ou o Beltrano não utilizem o seu celular. Então ao não consumir, não se fatura e não se cobra. Quanto perdemos ou deixamos de ganhar?

Seria preciso uma banca e tanto para defender as teses que por ai surgiriam.

Valeu mesmo e boas festas a todos.
Desculpem-me se me expressei mal em algum momento.

[FALL]

Pessoal, gostaria de postar uma solução bem mais eficaz para a situação de limpar uma tabela se a outra existir.
Para que isso? Tem uma tabela com os dados de ontem, e vou atualizar com os novos dados de hoje, mas o processo que atualiza depende de outra tabelas e que não quero ficar procurando para ver se todas estão ok ou não.
Com essa sistemática de consultar a tabela dba_segments, sempre haverá uma resposta, existe ou não existe? Se existir, sempre retornará 1, se não sempre retornará zero (cfe exemplo).

Selecionar tudo

DECLARE
			 EXISTE NUMBER;	
			 QTD NUMBER;		 
BEGIN
	SELECT COUNT(1)
	INTO EXISTE
	from DBA_SEGMENTS 
	where OWNER ='U51014486'
	AND SEGMENT_NAME='TESTE01';
	
	IF EXISTE=1 THEN
		 			SELECT COUNT(1)
		 			INTO QTD
		 			FROM TESTE01;
			IF QTD>1000 THEN
		 	 	 			INSERT INTO TESTE04 
		 	 				SELECT * FROM TESTE03;	
		 	 				COMMIT;	
			ELSE
			 				DELETE TESTE04;
			 				COMMIT;
			END IF;	
	END IF;
END;
/
EXIT;

Esse conteudo num arquivo TESTE.pdc chamado por bat TESTE03.bat de conteúdo:

Selecionar tudo

           sqlplusw Usuário/SENHA@banco @TESTE03.pdc

Utilizado Oracle 9.2.0.8.0