TEMPORY TABLE X NORMAL TABLE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Sat, 02 Jul 2005 11:27 am

Hello, I have a function that returns a cursor, in this function have to do several calculations to take various data from several tables. I have to put it all in a table so you can get a SELECT to return the cursor. There will be no commit in this table it is just to store the data while the function this processing. For this I created a regular table with the structure I need and use it as the example below. The question is: will have won if create this table as TEMPORARY TABLE (...) ON COMMIT DELETE ROWS, if so what??? the solution adopted involves the risk that if two or more users access the function at the same time, the data can be mixed together???

Code: Select all
package pktemp_2 is
TYPE tab_temp IS TABLE OF TabelaNormal%ROWTYPE;
function retorna_dados return tab_temp pipelined;
function CHAMA return RET_CURSOR.REF_CURSOR;
end;

function CHAMA return RET_CURSOR.REF_CURSOR
is
CRS_AAA RET_CURSOR.REF_CURSOR;
begin
   open crs_aaa for
   select * from table(pktemp_2.RETORNA_DADOS);
   return crs_aaa;
   close crs_aaa;
end CHAMA;
end pktemp_2;

function retorna_dados return tab_temp pipelined
is
e AGA.AGATBA01_GUICHE%ROWTYPE;
begin
   e.NU_GUICHE := 1;
   e.NU_UNIDADE := 1;
   pipe row(e);
   return;
end retorna_dados;

function CHAMA return AGA.AGAPC001_TIPOS.REF_CURSOR
is
CRS_AAA AGA.AGAPC001_TIPOS.REF_CURSOR;
begin
   open crs_aaa for
   select * from table(pktemp_2.RETORNA_DADOS);
   return crs_aaa;
   close crs_aaa;
end CHAMA;
end pktemp_2;
when executing Select * From Table (pktemp_2. CALLS) from dual, the data is returned.

Thank you
Lekich
Location: SP

Poston Fri, 22 Jul 2005 2:20 pm

Yes, you get performance, because the Global Temporary Tables are super fast. (because oracle does not need to have any consistency on them in case of fall, or something else ... Like, does not redo-log, etc) ...

For this reason, it's worth it. And I still don't have to worry about problems of 2 users access at the same time, because each one will be able to see their own information.
dr_gori
Location: Portland, OR USA

Thomas F. G

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


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: Bing [Bot] and 7 guests