Spool command broken in SQLcl 20.3 with 19.9 client

user832387

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 ]

thatJeffSmith-Oracle

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

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

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

user832387

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

https://community.oracle.com/tech/developers/discussion/comment/16778505#Comment_16778505

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