Forum Stats

  • 3,727,527 Users
  • 2,245,403 Discussions
  • 7,852,848 Comments

Discussions

Spool command broken in SQLcl 20.3 with 19.9 client

user832387
user832387 Member Posts: 17 Blue Ribbon

Hello,

I recently began testing for 19c client rollout and found that spooling command is not working in 20.3 with client patched with 19.9 on Windows. It shows an error message but none the less it creates 0 byte file. [ Tried including path in double quotes but still same error message. On the same laptop with 12c it works ]


Comments

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,649 Employee

    what does 'with a 19.9 client' mean?

    SQLcl doesn't use an oracle client unless you tell it to use an -oci type connection


    If you run 'show connection', what's your jdbc connection URL?


    Also, what kind of query/file are you trying to spool?

  • user832387
    user832387 Member Posts: 17 Blue Ribbon

    Thanks for your response.

    >>Also, what kind of query/file are you trying to spool?

    Just normal spool file to capture script output. Command doesn't have to be in script to generate error. Just issuing spool at the prompt raises SP2 error. On the same machine, if I remove 19c client and put back 12c client, I don't get SP2-0556 error after issuing spool command at prompt

    >>what does 'with a 19.9 client' mean?

    I am using Oracle 19c client with latest patch applied

    Connection info:

  • user832387
    user832387 Member Posts: 17 Blue Ribbon

    One thing I forgot to mention is that it's a 32 bit client .

  • user832387
    user832387 Member Posts: 17 Blue Ribbon

    Update: I was able to find the cause (and work-around) for the issue

    sqlCl sets wrong encoding with Oracle 19 client (Patch 19.9 applied) but sets valid value with 12c client (windows-1252)

    I tried to set encoding in login.sql but it didn't work. I have to set at sql prompt to make it work.

    Is there any configuration to set proper encoding?

    [email protected]>show encoding

    Encoding:WE8MSWIN1252 <== Default with 19c client

    [email protected]>spool C:\Temp\test19c.log

    SP2-0556: Invalid file name.

    [email protected]>spool off

    not spooling currently

    [email protected]>set encoding windows-1252

    [email protected]>spool C:\Temp\test19c.log <== No error

    [email protected]>select sysdate from dual;

              SYSDATE

    __________________________

    20-JAN-2021 12:26:21 PM

    1 row selected.

    [email protected]>spool off

  • user832387
    user832387 Member Posts: 17 Blue Ribbon

    I tried with SQLcl 20.4 and it still somehow setting "invalid" encoding after login.[ It's this wrong value of encoding, the cause of spool not working ] "Invalid" meaning not listed in the show encodings. In fact, SQLcl will accept any value for set encoding thats not in the list.[set encoding zzzz]

    Its only happening on laptop with Oracle 19c client (patched to 19.9). Same issue with either tns entry or easy-connect.

    Encoding stays windows-1252 after connecting, on laptop with 12c client for SQLcl 20.4


Sign In or Register to comment.