Re: Dynamic spool files

Mark C. Stock (mcstockX_at_Xenquery.com) wrote:

: "Alex" <avilner_at_gmail.com> wrote in message
: news:1110835153.369137.250650_at_o13g2000cwo.googlegroups.com...
: > Dear Netters,
: >
: > Seems that the question has been answered in the past, but for a single
: > file...
: >
: > What I am looking to do is to generate a set of spool files, where the
: > file name is based on the table_name from user_tables.
: >
: > So, in a nutshell, a script that would look like this:
: >
: > FOR rec IN (SELECT table_name FROM user_tables
: > LOOP
: > spool rec.table_name
: > <select some values to populate the file>
: > spool off
: > END LOOP;
: >
: > So, it is the spool file per entry in the user_tables, and it needs to
: > be done on the client side, rather than the server.
: >
: > Any ideas will be greatly appreciated!
: > Thank you in advance.
: >
: > --Alex
: >

: use that same approach to generate the SQL SELECT statements as well as the
: SQL*Plus spool commands (rather than actually execute the SQL SELECT
: statement within the loop)

Yes, but it's also very useful to know (cut/pasted from oracle.com, google: sqlplus column define variable, http://www.oracle.com/ technology/ support/ tech/ sql_plus/ htdocs/ sub_var9.html)

	Using COLUMN NEW_VALUE to set a substitution variable to a value
	stored in the database:

	    column last_name new_value mynv
	    select last_name from employees where employee_id = 100;

	This creates a substitution variable "mynv" set to the value in
	the "last_name" column.

--

This space not for rent.