Re: Dynamic Tablespace using SqlPlus

In article <IgaR6.141$SU4.866_at_client>, "Bill" says...
>
>Hi,
>
>I am interested in writing a simple script which creates a table and gives
>it a dyamic tablespace name .
>
>For example:
>CREATE TABLE test1
> (TEST VARCHAR2(32) NOT NULL)
> TABLESPACE <name>;
>
>I want the <name> to come from following query:
>select tablespace_name from user_indexes where index_name = 'WHATEVER';
>
>I have created the following script:
>
>variable ts_name varchar2(64);
>EXECUTE :ts_name := TEST_GETUSERINDEX('WHATEVER'); -- TEST_GETUSERINDEX
>executes the above query
>CREATE TABLE test1
> (TEST VARCHAR2(32) NOT NULL)
> TABLESPACE ts_name;
>/
>

try

column tablespace_name new_value TS
select tablespace_name frm user_indexes where index_name = 'WHATEVER'; create table test1 ( test varchar2(32) not null ) tablespace &TS;

or, more procedurally:

declare

   l_tablespace_name user_indexes.tablespace_name%type; begin

   select tablespace_name into l_tablespace_name      from user_indexes
    where index_name = 'WHATEVER';

   execute immediate
   'create table test1 ( test varchar2(30) not null ) tablespace ' ||

        l_tablespace_name;
end;
/

>But I get the following error from sqlplus:
>SQL> _at_t
>PL/SQL procedure successfully completed.
>CREATE TABLE test1
>*
>ERROR at line 1:
>ORA-00959: tablespace 'TS_NAME' does not exist
>
>Does anyone have an idea how to do this?
>
>thank you in advance,
>Bill
>
>
>
>
>
>
>
>
>

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Sat Jul 21 2001 - 23:46:57 CEST