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 .....;
Improve this question
4
  • Does this answer your question? How to set Oracle bind variables when using SQLPlus? Commented Mar 15, 2022 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. Commented Mar 15, 2022 at 20:11
  • You can't use bind variables in DDL statements. If this is a SQL*Plus script you could use substitution variables. Commented Mar 27, 2022 at 13:32
  • I realized that I do not need explicitly set the location of the datafile. Commented Apr 13, 2022 at 19:28
Related questions
1
Passing SQL to an Oracle bind variable
2
Mixing Bind Variables with Literals in Oracle
6
passing table and column name dynamically using bind variables
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.