Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    21

    Unanswered: "Spool" from within a PL/SQL script?

    Is there a way to dynamically execute spool statements from within PL/SQL? I tried using DBMS_SQL but it doesn't seem to work. I would like to be able to write to output files using DBMS_OUTPUT, because that would be so much more straightforward and flexible than using the UTL_FILE routines.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: "Spool" from within a PL/SQL script?

    Originally posted by Pithecanthropus
    Is there a way to dynamically execute spool statements from within PL/SQL? I tried using DBMS_SQL but it doesn't seem to work. I would like to be able to write to output files using DBMS_OUTPUT, because that would be so much more straightforward and flexible than using the UTL_FILE routines.
    SPOOL is a SQL*Plus command & can't be used from PL/SQL.
    So exactly what is stopping you from using DBMS_OUTPUT?
    It works fine & last a long time.
    It works for me.
    Have you actually tried RTFM regarding DBMS_OUTPUT?
    If not, please go do so before expecting list members to spoon feed you.

  3. #3
    Join Date
    Aug 2003
    Posts
    21
    I do want to use DBMS_OUTPUT, but the problem is that I don't want to have all the output go to one file. My idea was to create multiple output files by "spooling off" and then starting the spool again, but with a new file name, for as many files as I want to create. The program needs to create many files, each relating to a specific business partner. I could do it in Pro-C, except that I don't have login access to the machine where the database actually resides, and if I did, i would have to set up gnu and gcc and all that. I haven't done that in such a long time that it would be rather difficult to do it now.

    Of course, come to think of it, I could write a Unix shell script to loop and run the sqlsession multiple times, each time spooling out a different file. That woiuld actually be best, come to think of it, because that way I won't run into problems with the buffer limitations!

    Whoa, I just figured it out. go, me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •