For dyn sql selects, when should I create temp table? 1

[CapsuleCorpJX]

http://www.teradata.com/t/go.aspx/index.html?id=117793

That website documents how to make dynamic sql selects.

However, I'm confused on where I should put the following code (which creates a temp, session specific table):

-- create temporary table to be used by procedure
create global temporary table dyn_sql_t (col varchar(30))
on commit preserve rows;

I think I should put it right before a series of calls to "dyn_sql", i.e.:

[start]
REPLACE PROCEDURE a_procedure_that_use_dyn_sql()

begin

create global temporary table dyn_sql_t (col varchar(30))
on commit preserve rows;

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2b', resultval);
-- resultval is returned as 'some_val1b'

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2a', resultval);
-- resultval is returned as 'some_val1a'

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2c', resultval);
-- resultval is returned as 'some_val1c'

call dyn_sql ('some_t', 'some_col2', 'some_col1', 'some_val1a', resultval);
-- resultval is returned as 'some_val2a'

end;
[stop]

Also, would the session end (and therefore the temp table get deleted) once "a_procedure_that_use_dyn_sql" ends?

 

[dnoeth]

create global temporary table dyn_sql_t (col varchar(30))
on commit preserve rows;"

This statement dosn't create "a temp, session specific table", it creates a kind of Template, which may be used by any user (with appropriate access rights).
A "Insert Into dyn_sql_t" will materialise a session local temp table using the structure defined by the Create statement.

So ou create the Global Temporary *before* you create the SP.
Within your SP you just use it (and if you run the same SP repeatedly within the same session don't forget to "Delete From dyn_sql_t")

Dieter

 

[dnoeth]

Forgot to mention, if you really want to return any kind of select, then use

"create volatile table dyn_sql_t as
select ...
on commit preserve rows;"

as dynamic SQL within your SP and then

"CALL my_dyn_SP(...);
SELECT * FROM dyn_sql_t;"

And regarding security google for "SQL Injection" ;-)

Dieter

 

[CapsuleCorpJX]

Thanks! That helps.
Also, if several korn shell scripts are calling the same procedure, using the same login id to access the database, would they step over each other? Or would the global temp table be a unique and seperate instance for each running script?

 

[dnoeth]

Each shell script will be assigned a unique session number during logon and a seperate instance of a temporary table is always materialized per *session*.

Btw, you can submit a "select session" to retrieve the session number.

Dieter