Re: calling sql script from pl/sql

"John Hume" <jdhume_at_earthlink.net> wrote in message news:749ff512.0204191747.2bf912c6_at_posting.google.com...
> 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

You should differentiate between sql, pl/sql and sql*plus. You are trying to accomplish sql/sql*plus tasks in pl/sql. You can call pl/sql from sql, NOT the other way around. If you can't use the above alternatives, and also don't want to address them (that would look like an option to me) you have no other alternative than using execute immediate (8i and higher) or dbms_sql (8.0 and before) to create your tables.
Creating tables on the fly however must be considered very bad practice.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address