Ideas for a query

[Rafael_Santos]

Hello everyone,

I'm creating a procedure that will load a table.

I have a query + - 30 lines I need to extract only quantity obtained [/ u], field identifier [/ u] and date relationand u].

I soon thought I could create a temp table in the procedure and popular through the query mentioned above, but it is not possible to create a temp table in the procedure, am I correct?

I read that the best thing to do would be to create a cursor, but cursor spends a lot of time and memory.

Create a monster Query with Sub Quies would also slow down.

Can anyone give me any idea how to proceed with this problem?

Thanks for everyone's attention, hugs!

[DanielNN]

Query of 30 lines depending on the relationship done, it is nothing.
If you notice, via the time and implementation plan of the quieu, that it is satisfactory, I do not see problems in using cursor.
Unless your queries do not have the correct realization or tables are denied and / or without appropriate indexes.

It would be interesting to show you at least skeleton than you want to do.

[Rafael_Santos]

Hello Noctifero,
Thank you so much for answering and for granting your attention.

I had read your post on the day you had posted, but I was doubtful in my own response.

I found an alternative to the problem and would like to thank the attention granted.

[stcoutinho]

Rafael,

If you thought about creating a "Temp Table", take a look at the subject GLOBAL TEMPORARY TABLE
Hugs,

Sergio Coutinho

[gpereira]

Good Morning !

Depending on the mass of data, I believe that it has no impact to create a cursor with this query.

If you feel more comfortable working with temporary tables, it is possible to create the procedure.

I do not have your query, but it follows an example below:

Select all

CREATE OR REPLACE PROCEDURE [NOME_PROCEDURE] IS 
 
BEGIN 
 
EXECUTE IMMEDIATE  
'CREATE GLOBAL TEMPORARY TABLE [TMP_TABLE_NAME] 
on commit preserve rows 
AS 
[QUERY_CITADA]' ; 
 
END ;

Note: I'm not sure and I did not testify if it is Creation works with this "AS [Query]", I think so, but if it does not work, create field in the field then insert into the table.


More information on temporary tables:
http://www.devmedia.com.br/tabelas-temp ... racle/1915
{} 's

[stcoutinho]

Hi,

Only contributing with the explanations of GPeira, a temporary table does not necessarily need to be created dynamically.

If for example your temporary table will be used continuously, simply create it aside (the "GLOBAL TEMPORY TABLE" command that the GPEIERE showed). Then you can mention it in any PL / SQL (procedures, packages, etc.) code.

Understand that for a temporary table, only the data are temporary. The definition / structure of the table remains "as permanent" as any other normal table. In this case, only a Drop Table command could remove a Temporary global table.

An Oracle temporary table has its setting stored in the bank data dictionary, but can only store data during the current database session that made some insert / update transaction. Data temporarily stored in this table are only visible to the bank session that populated it.

or whether I could have 100 sessions - for example - making several insert operations, delete, update, select, that the data of a session would never be seen by the other sessions. It is as if Oracle took the table and generate an empty clone thereof for each bank session that needed popular or manipulate data in it.

Hugs,

Sergio Coutinho