SQL & PL/SQL

Hello

Im trying to spool my output and want the name of spool file to be a bound variable but I cannot get it work. I receive error Not all variables bound.

Basicly what Im doing (what the script does) is retreiving a message_id in message_tab.
Then in the function I retreive the name of this type of message and return file_name_ (I also want to name the spool file as the same name)


This is my script.

SET SERVEROUT ON;
spool on
set heading off
column spoolcol as spoolcol NEW_VALUE spoolname
SELECT :spoolfile from dual;
spool c:\&spoolname.
DECLARE
CURSOR csr_files IS
select message_id
from message_tab
where message_no = 9174;

file_name_ VARCHAR2(1000 CHAR);
bName_ BOOLEAN;
spoolfile varchar(1000);

FUNCTION getFileName(message_no_ IN NUMBER) RETURN VARCHAR2
IS
file_name_ VARCHAR2(1000 CHAR);

CURSOR csr_name IS
SELECT message_id
FROM message_tab
WHERE message_no = message_no_;

BEGIN
OPEN csr_name;
FETCH csr_name into file_name_;
CLOSE csr_name;

RETURN file_name_;
END getFileName;


BEGIN
bName_ := FALSE;
FOR rec_ IN csr_files LOOP
IF bName_ = FALSE THEN
file_name_ := getFileName (rec_.message_no);
:spoolfile := file_name_;
bName_ := TRUE;
END IF;

DBMS_Output.Put_Line(rec_.value);

END LOOP;


END;

set feedback off
set trimspool on
spool off