Putting Spool code into PL/SQL procedure

[dkwong]

I have the following sql script that successfully spools the result of a select statement into a textfile but I'm having trouble putting it into a stored procedure. Is there different syntax for the following in PL/SQL? How does it fit in a begin end block?

SET ECHO      OFF
SET HEADING   OFF
SET PAGESIZE    0
SET NEWPAGE     0
SET SPACE       0
SET LINESIZE  115
SET FEEDBACK  OFF
SET TERMOUT   OFF

SPOOL c:/spooltest.txt

SELECT RPAD(pr_number_tx,9,' ')||
       RPAD(NVL(pr_corp_code_tx,' '),5,' ')||
       RPAD(NVL(TO_CHAR(pr_date_dt,'YYMMDD'),' '),6,' ')FROM   purchase_requisition
WHERE  pr_status_cd = 'APP'
ORDER
BY     pr_number_tx;

SPOOL OFF

EXIT
[code]

 

[dkwong]

Update: I implemented the code above in PL/SQL using the UTL_FILE package, but it's still not working. Any ideas?

CREATE OR REPLACE PROCEDURE PR_EXPORT_INFOTRAC_APPO
IS
 extract_file UTL_FILE.FILE_TYPE;

 CURSOR pr_header_crsr IS
	SELECT RPAD(pr_number_tx,9,' ') pr_number_tx,
   	       RPAD(NVL(pr_corp_code_tx,' '),5,' ') pr_corp_code_tx,
	       RPAD(NVL(TO_CHAR(pr_date_dt,'YYMMDD'),' '),6,' ') pr_date_dt
	FROM   purchase_requisition
	WHERE  pr_status_cd = 'APP'
	ORDER
	BY     pr_number_tx;

BEGIN

 extract_file := utl_file.FOPEN('c:\','spooltest.txt','W');

 FOR i IN pr_header_crsr
 LOOP
   utl_file.put_line
    (extract_file,
    i.pr_number_tx||
    i.pr_corp_code_tx||
    i.pr_date_dt;
 END LOOP;

utl_file.FCLOSE(extract_file);

END PR_EXPORT_INFOTRAC_APPO;

 

[dkwong]

Another bit of info, the execution fails at:

act_file := utl_file.FOPEN('c:\','spooltest.txt','W');

 

[rcurva]

You can check the UTL_FILE_DIR parameter from init.ora or the v$parameter view.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[DaPi]

If I remember correctly the path is case sensitive - the value in your FOPEN must match exactly with the value in init.ora