Re: Creating an oracle temp table with a variable table name

Mike Ready wrote:
Hi
Is there a way to use a bind variable to pass a tablename into an oracle
create table statement - e.g. CREATE TABLE ? (
CASE_NUM NUMBER,
TLM_PROG VARCHAR2(50 BYTE),
TLM_REASON VARCHAR2(100 BYTE),
TLM_CREATE_DT DATE,
TLM_TASK_NAME VARCHAR2(50 BYTE),
TLM_ACTION VARCHAR2(50 BYTE)
)
where ? gets replaced with a bind variable holding a temp table name
that contains the date? I can do it by substituting the date into the
sql string or by concatenating the tablename with a couple of strings to
create the sql statement, but it seems cheesy. If I try to do it using
a bind variable, it errors out.
Thanks
Mike R.
Michael P. Ready
Texas ACCESS Alliance
Work 512-533-3959, Mobile 512-796-7085, Fax 512-437-6532
AIM: michaelpready
mike.ready@xxxxxxxxxxxx



Mike, you misunderstood database binding process. Database can bind variables to a placeholder in statement that can be parsed. In order
to parse a SQL statement, database must know which objects are referred
to in the statement, what access privileges does the user have and which
access path will be used to access those objects. That means that there
can be no binding if the table name is not known in advance. What you
want is called "dynamic execution". You can always dynamically create
table like this

sub cr_tab {
my ($dbh,$table_name)=@_;
my $cr_table=qq( create table $table_name (
CASE_NUM NUMBER,
TLM_PROG VARCHAR2(50),
TLM_REASON VARCHAR2(100),
TLM_CREATE_DT DATE,
TLM_TASK_NAME VARCHAR2(50),
TLM_ACTION VARCHAR2(50)));
$dbh->do($cr_table);
}

--
Mladen Gogala
http://www.mgogala.com
.

Relevant Pages

  • RE: Finding out the type expected for a placeholder
    ... >> entering the SQL to use, with bind variables, and I would like to ... You would have to query the database object tables prior to executing ... your bind executes. ...
    (perl.dbi.users)
  • Re: Should prepare_cached() manage its total size or memory usage?
    ... >>I have a database application that selects about 50,000 rows one by one, ... > Depends on how you're using the caching. ... I think there could be a dozen or two bind variables, ... are some "IN" clauses which might have a dozen bind variables each. ...
    (perl.dbi.users)
  • Re: Tricky Error
    ... > what is this technique, or construct, or whatever you speak of? ... That is know as using bind variables. ... of choice is Oracle, I'll point you to an article written by an oracle guy: ... Bind variables were originally invented by IBM in their DL/I database ...
    (comp.lang.php)
  • RE: Creating an oracle temp table with a variable table name
    ... Is there a way to use a bind variable to pass a tablename into an oracle ... sql string or by concatenating the tablename with a couple of strings to ... No. Bind variables can only be used with DML, not ...
    (perl.dbi.users)
  • Re: table name
    ... create or replace procedure seltest (tablename in varchar2) ... bind variables, however, cannot occur in the from clause -- to the tablename ... you will need to user dynamic sql to specify the tablename dynamically ...
    (comp.databases.oracle.misc)