Re: calling sql script from pl/sql

John Hume (jdhume_at_earthlink.net) wrote:
: 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

If you generate an anonymous PL/SQL block then the PL/SQL can use @ to suck in dynamic code. What will happen is that SQL*Plus will include the lines you generated earlier and send them as part of the text of the block.

I don't have access to a working example to show you, but if you run code such as the following from SQL*Plus, then you should be able to get it to work.

        SQL> @run_script_to_generate_code NEW_CODE.TMP

	SQL> DECLARE
	>    variables go here
	>    BEGIN
	>    @NEW_CODE.TMP
	>    END ;
	>    /

The anonymous block will run. It will include the @NEW_CODE.TMP as part of itself. Note that the code is only included once at compile time, so I'm not sure if this is quite what you want.

As for the issue of making tables on the fly... This reminds me of arguments regarding making and removing directories on the fly. In the VMS world, the gurus frowned upon users to making directories, and the operating system makes it hard to remove directories. The directory structure is considered an important part of the configuration of the system. On Unix, or DOS/Windows, directories are made and removed all the time without a second thought. It's very flexible and convenient, and (unlike VMS) the OS helps you do this, instead of making it hard.

It really seams to be an attitude thing. Received on Mon Apr 22 2002 - 13:57:55 CDT