Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Creating a Table with a variable in the name?

Re: Creating a Table with a variable in the name?

From: Karen <karen.abgarian_at_fmr.com>
Date: 2000/08/10
Message-ID: <3992AF47.6257FF6E@fmr.com>#1/1

The only problem with this approach will be to get SYSDATE into a shell variable.

Another way is to use COLUMN.. NEW_VALUE to get a string into the DEFINE variable and then to create a table using &, like this:

column dd new_value ddd
select to_char(sysdate,'DDMM') dd from dual; create table d&ddd( n date );

In my opinion, more elegant.

Regards
Karen Abgarian.

Iain Wiseman wrote:

> set -e
>
> $ORACLE_HOME/bin/sqlplus /NOLOG >> $LOGFILE 2>&1 <<-END
>
> whenever SQLERROR CONTINUE
>
> whenever OSERROR CONTINUE
>
> conn system/$SYSTEMPASSWORD;
>
> drop tablespace ABS_AUDIT INCLUDING CONTENTS;
>
> drop tablespace ABS_DATA INCLUDING CONTENTS;
>
> drop tablespace ABS_INDEX INCLUDING CONTENTS;
>
> whenever SQLERROR exit 2
>
> whenever OSERROR exit 2
>
> CREATE TABLESPACE abs_data
>
> DATAFILE '$DATAFILEPATH1/$DBNAME/abs_data_1.dbf'
>
> SIZE 100M reuse
>
> DEFAULT STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 PCTincrease 0) ;
>
> END
>
> Works for tablespaces. should work for tables.
>
> Also PL/SQL provides DBMS_SQL.EXECUTE and DBMS_SQL.BIND_VARIABLE
>
> Iain
>
> "jennetta" <jennetta_at_NOSPAM.hotmail.com> wrote in message
> news:WZlk5.18980$c8.5234842_at_typhoon-news1.southeast.rr.com...
> > I'm trying to script the creation of table to be used for backing up some
> > data. I want the table to include the value of SYSDATE, named something
> > like backup_table_20000809. I got the value of SYSDATE going into a
> > variable but I can't see how to create the table with a name partially
> > composed of the value of a variable. Any ideas?
> >
> > Thanks in Advance!
> >
> >
Received on Thu Aug 10 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US