Hi, could someone help me with execute immediate and the temp table

declare local temporary table #temp(name char(20));
execute immediate('my query here')
select * from #temp

what if there are 2 applications who start a stored procedure with
execute immediate right after each other ?
Will the data in the temp table be overwritten ?
How to avoid that ?

On Nov 19, 8:56*am, janis <jani... (AT) gmail (DOT) com> wrote:
From SQL usage.pdf

Temporary table Data in a temporary table is held for a single
connection only. Global temporary
table definitions (but not data) are kept in the database until
dropped. Local temporary table definitions
and data exist for the duration of a single connection only. For more
information about temporary tables,
see “Working with temporary tables” on page 87.

From SQL reference.pdf

CREATE LOCAL TEMPORARY TABLE table-name
( { column-definition [ column-constraint … ] | table-constraint |
pctfree }, … )
[ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ]
pctfree : PCTFREE percent-free-space
percent-free-space : integer

.....
Remarks
In a procedure, use the CREATE LOCAL TEMPORARY TABLE statement,
instead of the DECLARE
LOCAL TEMPORARY TABLE statement, when you want to create a table that
persists after the procedure
completes. Local temporary tables created using the CREATE LOCAL
TEMPORARY TABLE statement
remain until they are either explicitly dropped, or until the
connection closes.
Local temporary tables created in IF statements using CREATE LOCAL
TEMPORARY TABLE also persist
after the IF statement completes.
......
DECLARE LOCAL TEMPORARY TABLE table-name
( { column-definition [ column-constraint … ] | table-constraint |
pctfree }, … )
[ ON COMMIT { DELETE | PRESERVE } ROWS
percent-free-space : integer

Well temp table is PER connection. Other connection could not "see"
data from temp table created by another user.

See SQL usage.pdf for more details.