Creating an oracle temp table with a variable table name

------_=_NextPart_001_01C693B0.B327A829
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi
Is there a way to use a bind variable to pass a tablename into an oracle
create table statement - e.g.=20
CREATE TABLE ?=20
(
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)
)
=20
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.
=20
Thanks
Mike R.
=20
Michael P. Ready
Texas ACCESS Alliance
Work 512-533-3959, Mobile 512-796-7085, Fax 512-437-6532
AIM: michaelpready
mike.ready@txaccess.com
=20

------_=_NextPart_001_01C693B0.B327A829--

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

The easiest way to do this is to use PL/SQL and the 'execute immediate'
statement.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ready, Mike [mailto:Mike.Ready@txaccess.com]=20
Sent: Monday, June 19, 2006 8:58 AM
To: dbi-users@perl.org
Subject: Creating an oracle temp table with a variable table name

Hi
Is there a way to use a bind variable to pass a tablename into an oracle
create table statement - e.g.=20
CREATE TABLE ?=20
(
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)
)
=20
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.
=20
Thanks
Mike R.
=20
Michael P. Ready
Texas ACCESS Alliance
Work 512-533-3959, Mobile 512-796-7085, Fax 512-437-6532
AIM: michaelpready
mike.ready@txaccess.com
=20

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

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

Ready, Mike [mailto:Mike.Ready@txaccess.com] 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.

No. Bind variables can only be used with DML (e.g. SELECT, INSERT), not
with DDL (e.g. CREATE, ALTER), and only in place of values, not in place of
object names.

I might write the code something like this:

my $table_name = 'tmp_' . $date;

my $create_sql = <<"EndOfSQL";
CREATE TABLE $table_name
(
CASE_NUM NUMBER,
...
)
EndOfSQL

I'd probably put it in a subroutine that allowed the table name to be passed
in.

HTH,
Ronald

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

Just a hint: If you need temporary tables, this is usually an indicator
that something is terribly wrong with either the database or the way you
want to use the database. Usually, you can use a view or a simple
select, if that way has performance issues, think about indexes and in
really hard cases some low-level database tuning (i.e. ask your local DB
admin or some Oracle consultant).

Alexander

Ready, Mike 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@txaccess.com
>
>
>
>

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/