Re: Printing a spool under SQL*Plus

Hello Xiongh,
I think I found the solution:
In the BEGIN-END-Block of the Trigger you can use any SQL statement. "SPOOL" is an SQL*Plus statement. So it is not allowed here. I made some small test and found the following you can try:

CREATE OR REPLACE TRIGGER get_this_data
AFTER UPDATE ON my_table
DECLARE
 CURSOR c1 IS
   SELECT my_field
   FROM my_table;
 my_rec c1%ROWTYPE;
BEGIN
 OPEN c1;
 LOOP
   FETCH c1 INTO my_rec;
   EXIT WHEN c1%NOTFOUND;
   INSERT INTO my_view_table
   VALUES (my_rec.my_field);
 END LOOP;
END;
/
spool <file_name>
select * from my_table
/
spool off

Like this the SPOOL statement is outside of the trigger action block. I hope this is about what you want to do. Another alternative is

SPOOL <filename>
Triger statement
SPOOL OFF Then you have the whole statement spooled.

Good luck

Corinna Becker

thoidi8 <xiongh_at_hotmail.com> wrote in article <Pine.A41.3.95.971217142510.152006A-100000_at_f1n11.spenet.wfu.edu>...
> hi CB,
>
> Thanks for your Help.
> I would like to create a trigger with some of you idea, as the following;
>
> CREATE OR REPLACE TRIGGER get_this_data
> AFTER UPDATE ON my_table
> DECLARE
> CURSOR c1 IS
> SELECT my_field
> FROM my_table;
> my_rec c1%ROWTYPE;
> BEGIN
> OPEN c1;
> LOOP
> FETCH c1 INTO my_rec;
> EXIT WHEN c1%NOTFOUND;
> INSERT INTO my_view_table
> VALUES (my_rec.my_field);
> SPOOL /export/home/my_datafile.lst
> SELECT *
> FROM my_view_table;
> SPOOL OFF
> END LOOP;
> END;
> /
>
> The following is sqlplus;
> SQL> @get_fy
> CREATE OR REPLACE TRIGGER get_this_data
> *
> ERROR at line 1:
> ORA-06550: line 15, column 10:
> PLS-00103: Encountered the symbol "/" when expecting one of the
 following:
> := . ( @ % ;
> ORA-06550: line 16, column 4:
> PLS-00103: Encountered the symbol "SELECT" when expecting one of the
> following:
>
  Received on Thu Dec 18 1997 - 00:00:00 CST