Error in using bind variables in tablespace creation script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Error in using bind variables in tablespace creation script

  1. #1
    Join Date
    Jun 2005
    Posts
    20

    Error in using bind variables in tablespace creation script

    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.

  2. #2
    Join Date
    Jun 2005
    Posts
    2
    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>

  3. #3
    Join Date
    Jun 2005
    Posts
    20
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width