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' ;
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 !!)