Dynamic Tablespace using SqlPlus

Hi,

[Quoted] 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;
/

[Quoted] 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 Received on Sat Jul 21 2001 - 23:46:53 CEST