SQL*PLUS - Create a new folder while spooling

Question

I've got these dynamic scripts. The first one stores the DDL of the tables in a file with the name of the table that is currently spooling, inside the specify folder:

set pagesize 0
set linesize 100
set long 90000

SET TERMOUT OFF
spool out.sql

select 'spool C:\Users\personal\MAIN_USR\table\'||REPLACE(table_name, '$', '_')||'.txt'||chr(13)||chr(10)||
       'SELECT DBMS_METADATA.GET_DDL'||chr(13)||chr(10)||
       '(''TABLE'','''||table_name||''',''MTO_TABL'') '||chr(13)||chr(10)||
       'FROM DUAL;'||chr(13)||chr(10)||
       'UPDATE MTTO_TAB_EXISTE_ALL'||chr(13)||chr(10)||
       'SET MCA_BACKUP_DDL = ''S'''||chr(13)||chr(10)||
       'WHERE table_name = '''||table_name||''';'||chr(13)||chr(10)||
       'COMMIT;'||chr(13)||chr(10)||
       'spool off' as cmd
FROM MTTO_TAB_EXIST_ALL tea
WHERE tea.MRK_DEL_PERM = 'Y'
AND tea.OWNER_NM = 'MTO_TABL'
AND MCA_BACKUP_DDL != 'Y';

spool off

@OUT.SQL

exit

This one stores makes the same thing that the script above, but with the indexes:

set pagesize 0
set linesize 100
set long 90000

SET TERMOUT OFF
spool outidx.sql

select 'spool C:\Users\personal\MAIN_USR\index\'||REPLACE(index_name, '$', '_')||'.txt'||chr(13)||chr(10)||
       'SELECT DBMS_METADATA.GET_DDL'||chr(13)||chr(10)||
       '(''INDEX'','''||index_name||''',''MTO_TABL'') '||chr(13)||chr(10)||
       'FROM DUAL;'||chr(13)||chr(10)||
       'spool off' as cmd
FROM user_indexes ui
INNER JOIN MTTO_TAB_EXIST_ALL tea USING(table_name)
WHERE tea.MRK_DEL_PERM = 'Y'
AND tea.OWNER_NM = 'MTO_TABL'
AND MCA_BACKUP_DDL != 'Y';

spool off

@OUTIDX.SQL

exit

What I'll like to do, is create a dynamic folder with the name of the table that is spooling and store in that folder the DDL of the table and its indexes. Something like this

set pagesize 0
set linesize 100
set long 90000

SET TERMOUT OFF
spool out.sql

select 'C:\Users\personal\test\'||REPLACE(table_name, '$', '_')||'\'||REPLACE(table_name, '$', '_')||'.txt'||chr(13)||chr(10)||

The script above runs without problem, but it doesn't create a new folder because Spool only stores query results. This is a kind of idea of what a I want to do though.

Answer 1

you can use the HOST command for that. It calls out to the operating system. example for windows:

SQL>  HOST "md mydirectory"