title: ORACLE How to use spool with dynamic spool location
body: <p>Ok, so i'm a complete newb with oracle. Now that that's out of the way;</p>
  
  <p>I think you can get an understand of what i'm trying to do below.
  For each stored procedure found, output the DDL to a filename with it's name.</p>
  
  <p>The problem is i can't figure out how to get the spool target to pick up the value of FileName which is being set by the cursor.</p>
  
  <pre><code>DECLARE 
  objName varchar2(50);
  FileName varchar2(50);
  
  cursor curProcs is
      select OBJECT_NAME into objName
        FROM ALL_PROCEDURES WHERE OWNER = 'AMS' 
        ORDER BY OBJECT_NAME; -- get all procs in db
  BEGIN
  open curProcs;
    if curProcs%ISOPEN THEN
     LOOP
      FETCH curProcs into objName;
      EXIT WHEN curProcs%NOTFOUND;
      FileName := 'C:\ ' || objName || '.PRC.SQL';
      spool FileName; --BREAKS
       DBMS_METADATA.GET_DDL('PROCEDURE',objName);
      spool off;
     END LOOP;
    END IF;
  END; 
  </code></pre>
  
  <p>Any ideas as to where i'm going wrong? and if anyone has an example of this i would appreciate it greatly. </p>
  
  <p>I feel i must be dancing around it because if i create a column initially
  then </p>
  
  <pre><code>spool &amp;ColName
  </code></pre>
  
  <p>i get a result, i just can't seem to dynmically change that &amp;colname</p>
  
  <p>Thanks for your help.</p>