Toad World® Forums

Running a script that usually outputs to "User Files" area

Hi I’m after some help. Our software suppliers occasionally ask us to run a script against our database that creates a spool file that outputs to our "User “Files” folder for TOAD. We’ve recently been issued with new laptops for work and am getting an error message when running the script that says "Open spool file “C:\Users\colinwe\AppData\Roaming\Quest Software\Toad for Oracle\12.12\User Files.”
<file_error msg=“Access is denied.” n=“5”/>

The folder itself appears to be marked as read-only and I can change the preferences on that folder and replicate it to sub-folders as many times as I like and it reverts back to read-only.

I’ve also changed the output path thinking that could be the issue and sadly that’s not changed anything. I’ve renamed the user files folder to OLD and tried again and I get the same error and find that TOAD has created another user files folder instead with the same error message.

I’ve also renamed 2 files (as suggested on a forum) ToadActions.dat file to ToadActions.dat.backup & ToadActions.log to ToadActions.log.backup and this didn’t make any difference.

If anyone can help find a solution for this problem I’d be really grateful.

Have you tried saving to desktop?

On Dec 7, 2017 6:43 AM, “colin.west” bounce-colinwest@toadworld.com wrote:

Running a script that usually outputs to “User Files” area

Thread created by colin.west
Hi I’m after some help. Our software suppliers occasionally ask us to run a script against our database that creates a spool file that outputs to our "User “Files” folder for TOAD. We’ve recently been issued with new laptops for work and am getting an error message when running the script that says "Open spool file “C:\Users\colinwe\AppData\Roaming\Quest Software\Toad for Oracle\12.12\User Files.”
<file_error msg=“Access is denied.” n=“5”/>

The folder itself appears to be marked as read-only and I can change the preferences on that folder and replicate it to sub-folders as many times as I like and it reverts back to read-only.

I’ve also changed the output path thinking that could be the issue and sadly that’s not changed anything. I’ve renamed the user files folder to OLD and tried again and I get the same error and find that TOAD has created another user files folder instead with the same error message.

I’ve also renamed 2 files (as suggested on a forum) ToadActions.dat file to ToadActions.dat.backup & ToadActions.log to ToadActions.log.backup and this didn’t make any difference.

If anyone can help find a solution for this problem I’d be really grateful.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

It doesn’t seem to matter what location I try and spool to, I get the same error.

Can you share the script so we can try to reproduce it? Feel free to remove whatever part of it is not relevant and replace it with “select * from dual” or something like that.

WHENEVER SQLERROR EXIT

set pages 0
set feed off
set verify off
set trims on
set term off
set linesize 4000
set recsep off

REM ********************************************************************************
REM DML START
REM ********************************************************************************

REM Part1 creates files where there is one file for several batches

UNDEFINE fnamevar
UNDEFINE fnamevar2
UNDEFINE filpath
COLUMN filname NEW_VALUE fnamevar NOPRINT
COLUMN filname2 NEW_VALUE fnamevar2 NOPRINT
COLUMN file_path NEW_VALUE filpath NOPRINT

SELECT DISTINCT eba_filename filname
,‘CTL.’||eba_filename filname2
,eta_file_path file_path
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eta_multiple_files = ‘N’
AND eba_bat_class = eta_bat_class
ORDER BY eba_filename;

spool &&filpath&&fnamevar…

COLUMN etr_id NOPRINT
COLUMN eba_id NOPRINT

SELECT TO_NUMBER(eba_id) eba_id
,‘0’ etr_id
,eba_text etr_text
FROM o_external_batches
WHERE eba_filename = ‘&&fnamevar’
AND eba_text <> ‘DUMMY’
UNION
SELECT TO_NUMBER(eba_id)
,etr_id
,etr_text
FROM o_external_transfer, o_external_batches
WHERE etr_eba_id = eba_id
AND eba_filename = ‘&&fnamevar’
AND etr_type <> ‘CTL’
ORDER BY 1,2;

spool &&filpath&&fnamevar2

SELECT etr_text
FROM o_external_transfer
,o_external_batches
WHERE etr_eba_id = eba_id
AND eba_filename = ‘&&fnamevar’
AND etr_type = ‘CTL’;

spool off

UPDATE o_external_batches
SET eba_status = ‘PROCESS’
WHERE eba_filename = ‘&&fnamevar’;

INSERT INTO O_LOGS(log_id,log_type,log_message,log_date)
SELECT o_log_seq.nextval
,eba_bat_class
,‘OBFX001S v1.01: Created extract data files &&fnamevar, &&fnamevar2’
,sysdate
FROM o_external_batches
WHERE eba_filename = ‘&&fnamevar’
AND rownum = 1;

COMMIT;

REM Part2 creates files where there is one file for each external batch

UNDEFINE idvar
UNDEFINE fnamevar
UNDEFINE filpath
COLUMN eba_id NEW_VALUE idvar NOPRINT
COLUMN filname NEW_VALUE fnamevar NOPRINT
COLUMN file_path NEW_VALUE filpath NOPRINT

SELECT eba_id
,eba_filename filname
,eta_file_path file_path
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eta_multiple_files = ‘Y’
AND eba_bat_class = eta_bat_class
ORDER BY TO_NUMBER(eba_id) DESC;

spool &&filpath&&fnamevar…

COLUMN etr_id NOPRINT

SELECT eba_id
,‘0’ etr_id
,eba_text etr_text
FROM o_external_batches
WHERE eba_id = ‘&&idvar’
AND eba_text <> ‘DUMMY’
UNION
SELECT etr_eba_id
,etr_id
,etr_text
FROM o_external_transfer
WHERE etr_eba_id = ‘&&idvar’
AND etr_type <> ‘CTL’
ORDER BY 1,2;

spool off
UNDEFINE fnamevar2
COLUMN filname2 NEW_VALUE fnamevar2 NOPRINT

REM only print out control file on last batch file created

SELECT ‘CTL.’||eba_filename filname2
FROM o_external_batches eba, o_external_transfer etr
WHERE eba_status = ‘READY’
AND etr_eba_id = eba_id
AND etr_type = ‘CTL’
AND eba_id = ‘&&idvar’;

spool &&filpath&&fnamevar2

SELECT etr_text
FROM o_external_transfer
WHERE etr_eba_id = ‘&&idvar’
AND etr_type = ‘CTL’;

spool off

UPDATE o_external_batches
SET eba_status = ‘PROCESS’
WHERE eba_id = ‘&&idvar’;

INSERT INTO O_LOGS(log_id,log_type,log_message,log_date)
SELECT o_log_seq.nextval
,eba_bat_class
,‘OBFX001S v1.01: Created extract data files &&fnamevar &&fnamevar2’
,sysdate
FROM o_external_batches
WHERE eba_id = ‘&&idvar’;

COMMIT;

UNDEFINE pnamevar
COLUMN pname NEW_VALUE pnamevar NOPRINT

REM reruns where there are more files to be created

SELECT ‘OBFX001S’ pname
FROM o_external_batches, o_extracts
WHERE eba_status = ‘READY’
AND eba_bat_class = eta_bat_class;

@&&pnamevar

exit

REM ********************************************************************************
REM DML END
REM ********************************************************************************