Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Unanswered: sql spool problems

    Hello!

    Can anyone explain me what this error means:
    SP2-0768: Illegal SPOOL command
    Usage: SPOOL { <file> | OFF | OUT }
    where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]] ?

    It occurs when I try to run an sql script with parameters. If I run it without parameters, everything is ok. The strange part is that I run similar sql scripts having parameters, but on a different database.

    Thank you very much,
    Diana

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    Maybe if you post the commands you are executing we could better help you.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2007
    Posts
    7
    ok...sorry!

    so, if I run like this
    @c:\dde_diana\test.sql, it is ok, it creates the output file,
    but when I use this commad spool C:\DDe_diana\test.sql 31-MAR-2007 ,the SP2 error ocurrs.

    The script itself is a very simple one, just a select from dual.

    Thank you!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm unable to reproduce your error; could you give some more information? How does your case differ from mine?
    Code:
    SQL> $type test.sql
    spool a.txt
    select * from dual;
    spool off
    
    
    SQL> @test
    
    D
    -
    X
    
    SQL> @test 31-mar-2007
    
    D
    -
    X
    
    SQL>
    As of the error you got, it is obvious: illegal use of the SPOOL command.

    So, would you mind to share EXACT code you used, not "a very simple one", "strange part" and similar descriptions? Shortly: this is Oracle problem, not English literature one.

  5. #5
    Join Date
    Apr 2007
    Posts
    7
    this is the script:

    spool C:\DDE_DIANA\DEPOZITE_TERMEN_CLIENTI_&1 .XLS

    select
    SYSDATE
    from dual;

    spool off


    and here is the sql response:

    SQL> @C:\DDE_DIANA\TEST.SQL
    SP2-0768: Illegal SPOOL command
    Usage: SPOOL { <file> | OFF | OUT }
    where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

    SYSDATE
    ---------
    11-APR-07

    not spooling currently


    As I can see, it doesn't like the space from the output file name:
    spool C:\DDE_DIANA\DEPOZITE_TERMEN_CLIENTI_&1 .XLS.
    And I say that it's strange because I used this kind of spools many times before.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have a real kludge of a suggestion.
    Code:
    SPOOL DAY_SPOOL.SQL
    SELECT 
    'SPOOL C:\DDE_DIANA\DEPOZITE_TERMEN_CLIENTI_'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'.xls' 
    from dual;
    spool off
    @DAY_SPOOL.SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Hello,

    at last a piece of useful information. Although according to 'I used this kind of spools many times before' note I will end with just guessing.

    You are right the space causes the problem. Suppose you know &1 represents a substitution variable given as parameter. If you would remove the space, it would work, although the generated file would not contain suffix. This is correct behaviour as period ('.') is supposed to be terminating the substitution variable identifier (as noted in the Examples section of the previous link). It can be changed by setting SQL*Plus CONCAT system variable.

    There are two workarounds of this issue:
    - add extra period ('.') after substitution variable (spool C:\DDE_DIANA\DEPOZITE_TERMEN_CLIENTI_&1..XLS)
    - SET CONCAT <some_other_not_so_often_used_character>

    Seems, when used before, second workaround was used and CONCAT was set to space (" ").

  8. #8
    Join Date
    Apr 2007
    Posts
    7
    Thank you very much for your, FLYBOY, it works if I replace the space with some other character, or "."
    Also many thanks to all who replied my message.

    All the best!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •