Home » SQL & PL/SQL » Client Tools » Cannot Create Spool File
Cannot Create Spool File [message #301975] Fri, 22 February 2008 10:12 Go to next message
deay
Messages: 48
Registered: August 2005
Member
Hello

we recently migrated from 9iR2 to oracle 10g.
some years back Barbara Boehmer(sorry if last name is not correct) helped me with a Batch Menu Screen I wrote for users
in SQLPLUS:
set serveroutput on size 1000000 
set echo off verify off trimspool on term on linesize 1000
cl screen
prompt ***************************************************
prompt * Select Batch Verification Processing Option:
prompt *  1.  Verify Fundcode
prompt *  2.  Verify Appealcode
prompt *  3.  Verify Dollars and Count
prompt *  4.  Update Batch Status
prompt *  5.  Verify Batch Flags
prompt *  6.  Verify NULL Acknowledgement
prompt *  7.  Verify NOT LIKE Appealcode
prompt *  8.  Verify NULL GiftType
prompt *  9.  Verify T_Date
prompt * 10.  Verify Year
prompt * 11.  Verify IntCode ATL NATL 
prompt * 12.  Verify IntCode 998 
prompt * 13.  Verify Salutations
prompt * 14.  Exit
prompt ***************************************************
accept process prompt ">"
SET TERM OFF
STORE SET saved_settings REPLACE 
SET TERM ON
SET FEEDBACK OFF HEADING OFF 
SPOOL query.sql
select decode(&process,1,'START checkFundcode',2,'START checkAppealcode',
3,'START checkDollars_Count',4,'START upd_BatchStatus',5,'START check_BatchFlags',
6,'START NullAcknowledgement',7,'START checkAppealcodeNL',
8,'START checkGifttype_Null',9,'START checkT_Date', 10, 'START checkYear_Null',
11,'START checkIntcode',12,'START checkIntcode2',13,'START check_Salutations',
14,'prompt','Please Enter Correct Selection')
from dual;
SPOOL OFF
START saved_settings
START query


when users run this in 10g they get:

>1
SP2-0332: Cannot create spool file.

START checkFundcode
not spooling currently
SQL>


the menu screen spooling has been working in 9iR2 with no issues.

thanks for any tips/help.



Re: Cannot Create Spool File [message #301977 is a reply to message #301975] Fri, 22 February 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have not the privilege to write in the directory you are spooling or you have not the privileges to overwrite the current spool file.

Regards
Michel

[edit: missing word]

[Updated on: Fri, 22 February 2008 10:54]

Report message to a moderator

Re: Cannot Create Spool File [message #301979 is a reply to message #301977] Fri, 22 February 2008 10:23 Go to previous messageGo to next message
deay
Messages: 48
Registered: August 2005
Member
hi Michel

not sure what mean, are you referring to privileges at the OS level or spooling privileges in Oracle? (never heard of that)

Re: Cannot Create Spool File [message #301983 is a reply to message #301979] Fri, 22 February 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes at OS level.

Regards
Michel
Re: Cannot Create Spool File [message #301986 is a reply to message #301979] Fri, 22 February 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference
Chapter 13 SQL*Plus Error Messages
Quote:
SP2-0332 Cannot create spool file
Cause: Possible causes:
* Insufficient privileges to create a file.
* A system error made it impossible to create a file.
Action: Take the following actions:
* Change privileges to allow creation of the file.
* Consult the operating system documentation or contact the System Administrator.

Regards
Michel
Re: Cannot Create Spool File [message #301990 is a reply to message #301975] Fri, 22 February 2008 11:01 Go to previous messageGo to next message
deay
Messages: 48
Registered: August 2005
Member
thanks Michel

the System Administrator checked all the User privileges at the
OS level made a modification and spool still didn't work, it raised the following error message.

SP2-0746 SQLPLUS Compatability option out of range (7.3.4 through 9.2.0)

I can spool from my machine running Windows 2000.
Re: Cannot Create Spool File [message #302002 is a reply to message #301990] Fri, 22 February 2008 11:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> set sqlpluscompat 0.0.0
SP2-0746: SQLPLUSCOMPATIBILITY option out of range (7.3.4 through 10.2.0)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Quote:
SP2-0746 SQLPLUS Compatability option out of range (7.3.4 through 9.2.0)

It looks like somewhere in your sql code you should be setting the variable sqlpluscompatibility to some value. Also by looking at the error message you received it looks like the database you are running or connecting to is oracle version 9i.

HTH

Regards

Raj
Re: Cannot Create Spool File [message #302003 is a reply to message #301990] Fri, 22 February 2008 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you check what the message says?

Regards
Michel

[Edit: too late!]

[Updated on: Fri, 22 February 2008 11:58]

Report message to a moderator

Re: Cannot Create Spool File [message #302020 is a reply to message #301975] Fri, 22 February 2008 13:08 Go to previous message
deay
Messages: 48
Registered: August 2005
Member
Raj, thanks

you are correct, we are currently using two versions on Oracle
on different servers 9ir2 and 10g..because of registry settings
in Windows the 10g db points to 9i SQLPLUS(this is temporary
until we get both servers to 10g)

in case anyone else encounters this scenario here is the fix.

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch13.htm#i2699351

SET SQLPLUSCOMPATIBILITY{10.1}

users can now spool.... Razz
thanks everyone.

Previous Topic: Problem debugging in SQL Developer
Next Topic: tools
Goto Forum:
  


Current Time: Sat Aug 30 07:14:31 CDT 2014

Total time taken to generate the page: 0.10774 seconds