Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    6

    Unanswered: Variable name in Spool

    Hi,

    I am having a PL/SQL block and I need to output the data from the block to a spool file. Now the problem is that the name of the spool file needs to be variable that is it is decided by the values selected in the block?
    Is it possible to achieve this?
    Can anybody please help me?

    Regards,
    VJ

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It can be done like this:

    1) Before your PL/SQL block define a bind variable like this:

    VAR spoolfile

    2) In your PL/SQL block set the bind variable to the required value:
    Code:
    BEGIN
      ...
      :spoolfile := 'Whatever';
      ...
    END;
    3) After your PL/SQL block, copy the bind variable into a substitution variable like this:

    COLUMN spoolcol NEW_VALUE spoolname
    SELECT :spoolfile AS spoolcol FROM DUAL;

    4) Now spool to that filename:

    SPOOL &spoolname.

Posting Permissions

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