Criação de procedure.

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
robertoraf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Qua, 16 Set 2009 11:49 am
Localização: RJ
Contato:
Roberto Rafael
Analista DBA

Amigos,
Sou iniciante no PL SQL.
E estou com uma duvida e acho que vocês podem me ajudar.
Estou precisando criar uma procedure para deletar uma tabela e depois criá-la novamente.
Exemplo:

Selecionar tudo

TRUNCATE TABLE AD_OIDIRETO_R2
--
CREATE TABLE AD_OIDIRETO_R2 AS
(
SELECT * FROM adiant_oidireto_r2
)
 
--
TRUNCATE TABLE AD_FRANQUIASP
--
CREATE TABLE AD_FRANQUIASP AS
(
SELECT * FROM adiant_franquiasp
)
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5026
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Você quer DELETAR a tabela ? (DROPAR ?)
Ou apenas apagar os dados dela ?
SergioLBJr
Rank: Oracle Guru
Rank: Oracle Guru
Mensagens: 448
Registrado em: Ter, 16 Jun 2009 3:07 pm
Localização: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Pra deletar a tabela tu usa drop table NOME_DA_TABELA, e pra criar create table NOME_DA_TABELA.

Se tu for simplismente limpara a tabela não tem nescessidade de dropa-la, dai tu usa delete from NOME_DA_TABELA, e depois insere o que tu precisar com insert into NOME_DA_TABELA.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

apos você dar o delete como foi explicado no post de cima, não esquece de executar o commit

commit;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

apos você dar o delete como foi explicado no post de cima, não esquece de executar o commit

commit;
Naturalmente, mas depende se ele quer fazer um delete de fato.

Truncate e Drop (e create table) não permitem roll back, pois tem commit implícito, por exemplo.

Truncate tem várias vantagens para isso que o Roberto pretende:
- Truncate é mais rápido do que deletar todas linhas numa tabela pois ignora triggers, constraints, etc.
- Dropar a tabela e recriá-la exige que você refaça todos grants de privilégios, rodar DDL para índices, check constraints, triggers e aqueles parâmetros de armazenamento que DBAs adoram.
robertoraf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Qua, 16 Set 2009 11:49 am
Localização: RJ
Contato:
Roberto Rafael
Analista DBA

escrevia a procedure da seguinte forma.

Selecionar tudo

create or replace procedure ATUALIZACAO_EC AS
d_tbl varchar2(500) :='DROP TABLE AD_OIDIRETO';
c_tbl varchar2(500) :='CREATE TABLE AD_OIDIRETO AS ( SELECT * FROM adiant_oidireto)'; 

begin
 
EXECUTE IMMEDIATE d_tbl;
EXECUTE IMMEDIATE c_tbl;

end ATUALIZACAO_EC;
porem quando mando executar da o seguinte erro:

Selecionar tudo

ORA-00900: Invalid SQL statement
vocês podem me ajudar?
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Fico na dúvida... porque você quer fazer isso numa procedure? Me parece desnecessário, além de não ser recomendado.
robertoraf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Qua, 16 Set 2009 11:49 am
Localização: RJ
Contato:
Roberto Rafael
Analista DBA

porque tenho que atualizar umas 15 tabelas.
se não e recomendado.
como devo fazer para automatizar esta rotina?
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

tenho quase certeza que você não conseguira concatenar comandos ..

acho que desse jeito funcionaria.. tenta ai..

Selecionar tudo

EXECUTE IMMEDIATE 'DROP TABLE ' || paramNomeTabela;
EXECUTE IMMEDIATE 'CREATE TABLE AD_OIDIRETO AS ( SELECT * FROM ' || paramNomeTabela || ')'' ;
ou isso... alterando menos

Selecionar tudo

EXECUTE IMMEDIATE '' || d_tbl;
EXECUTE IMMEDIATE '' || c_tbl;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Seu código acima ficou faltando quotes (') por isso deu comando inválido.

Já que você quer automatizar em schedule ou algo assim, fazendo rotineiramente, dá para parametrizar a parte de DDL mais ou menos assim:

Selecionar tudo

CREATE OR REPLACE PROCEDURE trunca_tabela(tab_entrada IN VARCHAR2) AS
  v_nome_tab varchar2(100);
BEGIN
  v_nome_tab := DBMS_ASSERT.SQL_OBJECT_NAME(UPPER(tab_entrada));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || DBMS_ASSERT.ENQUOTE_NAME(v_nome_tab);
END;
Repare que como tem SQL dinâmico e nesse caso DDL, é extremamente importante validar contra sql injection e a package dbms_assert ajuda bastante.
A parte do insert você consegue fazer tranquilo numa outra procedure que chame essa de cima, por exemplo.
Qualquer dúvida manda ver.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Só para lembrar, se alguém mexer na estrutura da tabela (incluir ou excluir colunas, por exemplo) talvez seja melhor dropar a tabela mesmo, só a parte chata vai ser dar grants de novo, etc...

O bloco anônimo abaixo faz um exemplo de recarga na tabela usando a procedure pra dar o truncate:

Selecionar tudo

DECLARE
  v_tab_trunc varchar2(100);
  v_tab_origem varchar2(100);
BEGIN
  v_tab_trunc := DBMS_ASSERT.SQL_OBJECT_NAME('tab_teste');
  trunca_tabela(v_tab_trunc);
  v_tab_origem := DBMS_ASSERT.SQL_OBJECT_NAME('DUAL');
  execute immediate 'INSERT INTO ' || DBMS_ASSERT.ENQUOTE_NAME(v_tab_trunc) || ' SELECT * FROM ' || DBMS_ASSERT.ENQUOTE_NAME(v_tab_origem);
  dbms_output.put_line('Inseridas em ' || upper(v_tab_trunc) || ': ' || SQL%ROWCOUNT || ' linha(s).');
  commit; -- em algum momento vai precisar commitar o insert
END;
robertoraf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Qua, 16 Set 2009 11:49 am
Localização: RJ
Contato:
Roberto Rafael
Analista DBA

desculpa mais ainda não rodou.
estou usando o Oracle 9i e acho que DBMS_ASSERT.SQL_OBJECT_NAME não funciona pois deu erro e quando tiro e rodo da procedure aparece o mesmo erro: ORA-00900: Invalid SQL statement

A procedure ficou assim:

Selecionar tudo

create or replace procedure ATUALIZACAO_EC (tab_entrada IN VARCHAR2) AS
v_nome_tab varchar2(100):= 'EC_TBL_OIDIRETO_R2_SEM_HISTO';
begin
v_nome_tab := (UPPER(tab_entrada));
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || tab_entrada; 
end ATUALIZACAO_EC;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

É, a versão 9i não tem dbms_assert, mas não vai impedir você de fazer.

Você está rodando sua procedure como?

Tenta o seguinte, simplifica, tira parâmetros e tudo.

Selecionar tudo

create or replace procedure trunca_tabela AS
v_nome_tab varchar2(100):= 'SUATABELA'; -- substitua pela sua tabela a truncar
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_nome_tab;
end;
depois roda no seu console:

Selecionar tudo

BEGIN
  ATUALIZACAO_EC;
END;
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

bah, copy/paste errado de minha parte... mas deu pra entender... :lol:

Selecionar tudo

BEGIN
  TRUNCA_TABELA; -- ou qualquer que seja o nome de sua procedure
END;
robertoraf
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 8
Registrado em: Qua, 16 Set 2009 11:49 am
Localização: RJ
Contato:
Roberto Rafael
Analista DBA

Obrigado,
Agora funcionou.
Tem como tirar mais uma duvida?
Tem como eu colocar vários Truncate na mesma procedure?
Outra coisa tem como colocar o comando para criar a nova tabela dentro da procedure?
Eu posso rodar um drop table no lugar do truncate?
Você pode me indicar algum livro para que eu possa estudar?
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Tem como eu colocar vários Truncate na mesma procedure?
Outra coisa tem como colocar o comando para criar a nova tabela dentro da procedure?
Eu posso rodar um drop table no lugar do truncate?
Você pode me indicar algum livro para que eu possa estudar?
Dá pra fazer quantos comandos você precisar, inclusive drop e create table.
O único porém é que se você dropar e recriá-la para que outro usuário utilize-a, você também terá que dar um "grant select, delete, insert on sua_tabela to seu_usuario" para que esse usuário consiga enxergar a tabela e operar nela.

Sobre livros, o caminho é a própria documentação Oracle. Lá tem PDF de todas versões, desde a 9i que você usa até a 11g release 2 que é a mais recente hoje. Pesquisa no site abaixo e põe nos seus favoritos que essa éa mina de ouro:
http://tahiti.oracle.com
Moaci Monteiro
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qua, 03 Fev 2010 3:26 pm
Localização: RIO DE JANEIRO - RJ

Boa Tarde, Pessoal,

Sou novo com Oracle mas o Google já me ajudou bastante, tanto que achei esse fórum e me cadastrei.
Criei um Procedure para gravar numa tabela os dias do mês (passado por parâmetro). A procedure funciona mas preciso apagar a tabela para não duplicar os dados.
Usei a função: "execute immediate 'DELETE TABLE zdatas', só que aparece o erro "invalid table name" .

Alguém poderia me ajudar, por favor.

Desde já agradeço.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

quem criou o objeto que você esta tentando deletar?
e o mesmo usuario da sua procedure?
qual usuario você usa para executar a sua procedure?

muito provavelmente você tera que colocar o owner da tabela antes de mencionar a tabela

Selecionar tudo

execute immediate 'DELETE TABLE UserDonoTabela.zdatas'

pode estar ocorrendo de você não ter grant para deletar o objeto dai você tera que criar a sua procedure com o mesmo usuario que criou a tabela
burga
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Qui, 26 Nov 2009 1:05 pm
Localização: SP

'DELETE TABLE zdatas' não existe...

O comando certo é

Selecionar tudo

DELETE FROM ZDATAS
e como victor disse, dependendo do caso é necessário informar o esquema em que a tabela se encontra, assim:

Selecionar tudo

DELETE FROM ESQUEMA.ZDATAS
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

o burga tem toda razao..

você ta tentando apagar a tabela ou os registros apenas??

apenas os registros siga o burga

agora se for a tabela você precisa usar o drop

Selecionar tudo

DROP TABLE table_name;
Moaci Monteiro
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qua, 03 Fev 2010 3:26 pm
Localização: RIO DE JANEIRO - RJ

Pessoal, Bom Dia,

Obrigado pela atenção de todos.

Realmente eu digitei errado o comando. Somente após postar minha dúvida, verifiquei que tinha escrito errado.
Mudei o comando para

Selecionar tudo

DELETE FROM ZDATAS;
e funcionou conforme eu precisava.

Mais uma vez gostaria de agradecer.
SergioLBJr
Rank: Oracle Guru
Rank: Oracle Guru
Mensagens: 448
Registrado em: Ter, 16 Jun 2009 3:07 pm
Localização: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Ou pode usar o truncate como o exemplo acima.

Só presta atenção que tu pode ter erros devido a triggers, constraints ,etc.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante