CODEWITHSUNDEEP
sqloracle-database
egor7
egor7

Reputation: 4941

'create tablespace' from VARIABLE

I want to

CREATE TABLESPACE l_tablespace ...

where l_tablespace defined as

VARIABLE l_tablespace VARCHAR2( 100 CHAR )
EXEC l_tablespace := 'my_tablespace';

Not CREATE TABLESPACE l_tablespace ... nor CREATE TABLESPACE :l_tablespace ... doesn't work.

Upvotes: 2

Views: 1806

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You can't use bind variables in DDL, even in dynamic SQL, which is why your second attempt didn't work and why you have to use concatenation as @bunting showed. (It's generally helpful to show the error you got, rather than just 'doesn't work').

If you're running a script from SQL*Plus and want to be able to specify the tablespace at run-time, you can define a substitution variable instead:

DEFINE l_tablespace=my_tablespace
CREATE TABLESPACE &l_tablespace ...

The same variable can then be reused in subsequant commands in the same file, e.g. creating tables in your new tablespace.

You can also get the value from the user running the script with ACCEPT. Or use a positional parameter passed from the command line (&1 etc.), though I'd be less comfortable with that in here.

(Not sure if ACCEPT works in SQL Developer, but I think the rest does).

Upvotes: 2

Kai Sternad
Kai Sternad

Reputation: 22830

You can do this with execute immediate

execute immediate 'create tablespace '||l_tablespace||'  ...';

Upvotes: 5

Related Questions