0

I am trying to get the default dbf file location from a select statement and use this location to create tablespaces later on. I am having some trouble doing it in the sql script. I tried to use pl/sql and bind variables. Please let me know how i could fix this. Thanks

VARIABLE v_name VARCHAR2;
BEGIN
 select value into :v_name from v$parameter where name = 'db_create_file_dest';
END;
/

CREATE TABLESPACE cyyou_test DATAFILE  :v_name||'/test.dbf' SIZE 100M .....;
jiii
  • 71
  • 4
  • Does this answer your question? [How to set Oracle bind variables when using SQLPlus?](https://stackoverflow.com/questions/39993497/how-to-set-oracle-bind-variables-when-using-sqlplus) – OldProgrammer Mar 15 '22 at 20:08
  • Why are you trying to explicitly set the location of the datafile, if you have DB_CREATE_FILE_DEST set? The entire purpose of this parameter is to tell Oracle where to put datafiles automatically. – Dave Costa Mar 15 '22 at 20:11
  • You can't use bind variables in DDL statements. If this is a SQL\*Plus script you could use substitution variables. – William Robertson Mar 27 '22 at 13:32
  • I realized that I do not need explicitly set the location of the datafile. – jiii Apr 13 '22 at 19:28

0 Answers0