Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> calling sql script from pl/sql
Hi,
I wrote a sql script that takes a table name and output filename as parameters, and generates a "CREATE TABLE" statement for the table. I can call the script from SQL*Plus using the following syntax:
@c:\GenerateDDL.sql MY_TABLE output.sql
This works just fine, and it spools an output file that contains the DDL statement for the specified table. But now I want to create DDL statements for a bunch of tables automatically by querying user_tables and user_tab_columns. I tried writing a separate PL/SQL block where the loop looks like:
FETCH table_cur INTO current_table;
WHILE table_cur%FOUND LOOP
@c:\GenerateDDL.sql current_table current_table;
FETCH table_cur INTO current_table;
END LOOP;
This did not work because PL/SQL doesn't understand the "@" call. Is
there a way to call GenerateDDL.sql (exactly as written) using
SQL*Plus from within the PL/SQL block? I cannot use UTL_FILE to
create the DDL files because the init.ora file on our server is not
set up to support it (and changing it is not an option). DBMS_PIPE
will not work because the user I'm using to execute the script does
not have permission to that object (and granting permission is not an
option either).
Any help is much appreciated!
-John Received on Fri Apr 19 2002 - 20:47:02 CDT