This content has been marked as final. Show 4 replies
01sar01 wrote:You can't do select like this in pl/sql, as the tbs_name is passed as a parameter change to dynamic and make sure you have access to the dba_tablespaces table too.
I want to create a procedure that will accept two parameters. The name of a tablespace and the name of its datafile, with the path.
i don't know if this will work...currently it gives an error. Is it something that cannot be done using dynamic sql like this or is there another method.
Thanks for your help!
create or replace procedure create_tbs(tbs_name varchar2, filename varchar2)
str_tbs := 'create tablespace ' || tbs_name || ' datafile '''||filename|| ''' size 1m' ;
--> select tablespace_name into v_tbs --
--> from sys.dba_tablespaces --
--> where tablespace_name=tbs_name;--
when no_data_found then
execute immediate str_tbs;
dbms_output.put_line('tablespace was created');
SQL> execute create_tbs('mytab','/u01/app/oracle/oradata/orcl/mytab01.dbf')
ERROR....No such file or directory. (The directory does exist !!)
hi i think you have to create a directory for that
and after that
create directory TST_DATA_DIR as '/u01/app/oracle/oradata/orcl/mytab01.dbf';
The directory can reference only a directory on the OS, i cannot attach the filename to it. Maybe with three parameters. tablespace_name, directory_name and then filename.
The syntax looks OK. Did you try having the procedure display the generated CREATE TABLESPACE command and then copying & pasting it manually onto the command line to test it?