Error in using bind variables in tablespace creation script

[balasmg]

PROMPT Start of Script - Tablespace creation
variable tsp varchar2(300);
PROMPT
ACCEPT dbcs PROMPT 'Enter TNS Name of database :'
PROMPT
ACCEPT syspwddb PROMPT 'Enter password for sys user of database :'
PROMPT
PROMPT Connecting as SYS user to database
CONN sys/&syspwddb@&dbcs AS SYSDBA;

/* CREATE DEMODATA TABLESPACE, SIZE 2 GB WITH EXTENT MANAGEMENT LOCAL*/
PROMPT
PROMPT Create a tablespace named DEMODATA with Datafile named DEMODATA01.DBF Size 2 GB
SELECT 'Ex. of Datafile Location - Name :' || SUBSTR(NAME,1,INSTR(NAME,'/',-1)) ||'DEMODATA01.DBF'
into :tsp
FROM V$DATAFILE
WHERE ROWNUM < 2;


PROMPT PL. WAIT...! TABLE SPACE CREATION IN PROGRESS...!

CREATE TABLESPACE DEMODATA DATAFILE :tsp SIZE 2048 M EXTENT MANAGEMENT LOCAL;


CREATE USER SEED IDENTIFIED BY SEED
DEFAULT TABLESPACE DEMODATA
TEMPORARY TABLESPACE TEMP;

CONN SEED/ENCOVER@&DBCS;
-- End of script

i got the following error.

CREATE TABLESPACE DEMODATA DATAFILE :tsp SIZE 2048 M EXTENT MANAGEMENT LOCAL
*
ERROR at line 1:
ORA-02236: invalid file name

I need to create a tablespace where the datafiles are stored by as shown below.
I am able to get the path and concatenated the new demo data file DEMODATA01.DBF.
But while using the same from tablespace creation i face the following error.
how to rectify or is there any other method.

thanks in advance.

[kamal_k_joshi]

Try something like this:

SQL> column file_name new_value tsp
SQL>
SQL> SELECT SUBSTR(NAME,1,INSTR(NAME,'\',-1)) ||'DEMODATA01.DBF' file_name
2 FROM V$DATAFILE
3 WHERE ROWNUM < 2;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\ORADATA\KKISHORE\DEMODATA01.DBF

1 row selected.

SQL>
SQL> CREATE TABLESPACE DEMODATA DATAFILE '&tsp' SIZE 2048 M EXTENT MANAGEMENT LOCAL;
old 1: CREATE TABLESPACE DEMODATA DATAFILE '&tsp' SIZE 2048 M EXTENT MANAGEMENT LOCAL
new 1: CREATE TABLESPACE DEMODATA DATAFILE 'E:\ORACLE\ORADATA\KKISHORE\DEMODATA01.DBF' SIZE 2048 M EXTENT MANAGEMENT LOCAL

Tablespace created.

SQL>

[balasmg]

my purpose is to get the path of the datafile from v$datafile and use the same to create a tablespace in that path.

this to be run in a script.

VAR TSP VARCHAR2(200);

SELECT '''' || SUBSTR(NAME,1,INSTR(NAME,'/',-1)) ||'demodata01.DBF'''
into :tsp
FROM V$DATAFILE
WHERE ROWNUM < 2;


--ACCEPT tsp PROMPT 'Enter the Datafile Location and Name:'

PROMPT PL. WAIT...! TABLE SPACE CREATION IN PROGRESS...!

exec execute immediate 'CREATE TABLESPACE demodata DATAFILE' || :tsp || ' SIZE 2048 M EXTENT MANAGEMENT LOCAL';

I GET THE FOLLOWING ERROR

ERROR at line 1:
ORA-02236: invalid file name
ORA-06512: at line 1

PL. LET ME KNOW THE SCRIPT

[davey23uk]

the answer was given to you already, why not try it

then learn how variables work in oracle (i.e lose the : all over the place)