PL / SQL - Cursor and Loop

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
phscuritiba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 21 Feb 2008 8:52 pm
Location: Paraná

Good evening to all,

I am starting in the forum, in PL / SQL and Oracle database administration. I have the following situation:

I need to copy data from some tables to an instance to other tables from another instance.
First I created a DB LINK (BLK_PROD_BKP) to connect with the other instance, so far so good. Attached Normal, I tested a SELECT:

Select all

select * from GVTPRD.S_CFG_CLASS@DBLK_PROD_BKP
and everything worked, the problem is that when I will insert it on the other base, I have a problem with Table Space. So I can not do the following command:

Select all

insert into GVTPRD.S_CFG_CLASS(select * from GVTPRD.S_CFG_CLASS@DBLK_PROD_BKP)
An option, I think, would do for parts, since I can not change the Table Space, doing the insertion by groups, for example: of 500 In 500 records and making a commit every time you reach this number. The problem is how to do it ??? Can someone help me by creating an example in an PL block, doing select on a base and inserted into another ??? Because the solution I elaborated, only works for a number that does not exceed Table Space. Am I right ???

Thanks for help !!!


ruevers
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 324
Joined: Fri, 02 Jun 2006 1:48 pm
Location: sp
Contact:

There are several ways, but let's go to a gambiarra master.

is like this ...

Select all

insert into GVTPRD.S_CFG_CLASS(select * from GVTPRD.S_CFG_CLASS@DBLK_PROD_BKP) 
This table must have a primary key, I will call the key field OK .

would be like this.

Select all

insert into GVTPRD.S_CFG_CLASS( 
select *  
from GVTPRD.S_CFG_CLASS@DBLK_PROD_BKP  
where chave not in (select chave from gvtprd.s_cfg_class)  
and rownum <1000)
Where RunaM <1000 would be the number of lines that will enter at a time.
In case 999 would be 999 at a time

ATT

Anderson
DBA Mr. http://www.ruevers.webs.com
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

So have MacGyver 8)
phscuritiba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 21 Feb 2008 8:52 pm
Location: Paraná

Dear Anderson,

First, I'm sorry for the delay in thanking, is that they had blocked this site here in the company. A friend helped me with a script here, looking at your solution using the Rownum column I will need to run for every 999 records ??? For example, if I have 900,000 records I have to run as many times as it is necessary until you reach this number, or can I make a counter for each time it reaches this number to restart running until you reach the end ??? How could I do ??? Excuse me if you're a stupid questions, but as I mentioned I'm a beginner in the database area. Thank you very much in advance.

's

Paulo Henrique.
Julian Campagnoli
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 82
Joined: Tue, 26 Feb 2008 11:33 am
Location: MG e SP

From what I understand you want an example in PL to insert one base to another ?????

In PL-SQL The steps would be basically like this:

Create a procedure:
- Store the table data in a cursor
- Then store the cursor data in the table.
- From the Commit when you want

Note: It was not very clear if that is exactly what you want to do.


But you should also check the original database with the one that will be generated. For the example of Anderson, I think this according to what you would need to do.


Hugs
phscuritiba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 21 Feb 2008 8:52 pm
Location: Paraná

Thanks Julian,

is just that. Could you please pass an example ??? Thank you very much in advance.

's

Paulo Henrique.
rafasch
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 23
Joined: Thu, 18 Oct 2007 8:05 pm
Location: Blumenau

See:

Select all

create or replace procedure YYY is 
 
  cursor c is 
    select blalala from lalala; 
 
  contador number; 
 
begin 
   
   for c1 in c loop 
 
    begin 
 
       insert into tabela(campos) values(c1.campo...); 
       contador := contador + 1; 
       if(contador = valor_que_você_quer)then 
          commit; 
          contador := 0; 
       end if; 
          
     exception 
        when... 
    end; 
 
 
   end loop; 
 
end;
phscuritiba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 21 Feb 2008 8:52 pm
Location: Paraná

Thank you very much for the help!
by kindness, someone tells me a good book from Oracle ???

's

Paulo Henrique.
Julian Campagnoli
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 82
Joined: Tue, 26 Feb 2008 11:33 am
Location: MG e SP

Brother, I really liked Oracle 9i PL-SQL programming.
But a look here in the forum ... There are enough materials available.
Abra
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brothers,

I do not know how to tell you much about the books.
Maybe Oracle Press's own helps him.

But, here in the forum we have several handouts, like this:
[url=http://en.glufke.net/oracle/download/Ap ... cle_9i.zip]http://en.glufke.net/oracle/download/Ap ... cle_9i.zip. .
and also the video lessons, in this topic:
[url=http://en.glufke.net/oracle/viewtopic.php?t=1440]http://en.glufke.net/oracle/viewtopic.php?t=1440 .
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest