In an Oracle 11_2 database, I am attempting to run a query on a table, client_info. In SQL Developer I see the full column with the first 10 client names. When I try to run the same query in sqlplus on my local machine inside WSL, or a remote linux server, the results come back as a single character wide column with nothing in the rows but empty space.
I've tried with Oracle sqlplus versions 11_2, 12_2, and 21_10.
I also tried the same query on sqlplus that exists on a citrix instance, using 11_2, that DOES show the results.
I checked the NLS_CHARACTERSET as US7ASCII.
I am guessing it's something to do with the encoding and my terminal settings are some how at fault, but I'm honestly not finding what. I've tried the same query in a python and perl script as well but no results return either.
Table setup:
CREATE TABLE client_info (
client_id NUMBER NOT NULL,
client_name VARCHAR2(35 BYTE),
account_type VARCHAR2(10 BYTE)
)
Failed Query:
SELECT client_name FROM client_info WHERE account_type = 'IMPORTANT' AND rownum <= 10 ORDER BY client_name;
Output:
C
-
10 rows selected.
SQL>
Working output:
CLIENT_NAME
-----------------------------------
CL0038
CL0105
CL0105
CL0116
CL0123
CL0128
CL0128
CL0276
CL0318
CL0318
10 rows selected.
SQL>
(edit: adding the output of dump as requested)
From sqlplus:
SQL> SELECT dump(client_name) FROM client_info WHERE account_type = 'IMPORTANT' AND ROWNUM <= 10 ORDER BY client_name;
DUMP(client_name)
--------------------------------------------------------------------------------
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
10 rows selected.
SQL>
From SQL Developer:
Typ=1 Len=6: 67,76,48,48,51,56
Typ=1 Len=6: 67,76,48,49,48,53
Typ=1 Len=6: 67,76,48,49,48,53
Typ=1 Len=6: 67,76,48,49,49,54
Typ=1 Len=6: 67,76,48,49,50,51
Typ=1 Len=6: 67,76,48,49,50,56
Typ=1 Len=6: 67,76,48,49,50,56
Typ=1 Len=6: 67,76,48,50,55,54
Typ=1 Len=6: 67,76,48,51,49,56
Typ=1 Len=6: 67,76,48,51,49,56
My wsl instance and linux systems show the following locale settings:
locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
Any suggestions on what to check next would be appreciated.
So I finally heard back from our DBA. I'm told that this data is in a VPD (Virtual Private Database) and has certain safeguards in place to prevent the data from displaying. In this case it is based on the combination of the database login and the username of the system (ie the linux username). If those two names aren't in the database as having permission to view that table/column/whatever it won't show up.
Hope this helps someone else out down the road.