WMCore
WMCore copied to clipboard
dmwm
WMAgent: Add SqlCl client for Oracle based agents
Impact of the new feature WMAgent
Fixed by: https://github.com/dmwm/CMSKubernetes/pull/1455
Is your feature request related to a problem? Please describe. This is an unplanned issue, which I was not supposed to work, but it emerged as a side effect from my work on: https://github.com/dmwm/WMCore/issues/11720 and, since I had to resolve it for my work there, i decided to create a separate issue and track this here, because of the additional requirements that it implies.
This issue relates to the extremely not user friendly interface of sqlplus, which is the masic client we use in our db-prompt for the oracle based agents. While one can handle bad printouts with all SET <system var> commands as explained here: Ora-Set-Variables there is still the need of readjusting column size with the COLUMN command while working with the sqlPlus client for different queries and the column width needs to be adjusted every time depending on the tables and the table's contents etc. . In my previous work I tried to switch off line wrapping for sqlplus, but there is still much more that can be done.
e.g.
cmst1@vocms0290:wmagent $ docker exec -it wmagent bash
(WMAgent-2.3.0) [cmst1@vocms0290:current]$ manage db-prompt
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Mar 11 08:18:14 2024
Version 21.5.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon Mar 11 2024 07:54:56 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL> select * from user_tables;
rows will be truncated
rows will be truncated
...
TABLE_NAME |TABLESPACE_NAME |CLUSTER_NAME
================================================================================================================================|==============================|=============================================================================
WMBS_FILESET |CMS_WMBS_PREPROD4_DATA01 |
WMBS_FILE_DETAILS |CMS_WMBS_PREPROD4_DATA01 |
WMBS_FILESET_FILES |CMS_WMBS_PREPROD4_DATA01 |
WMBS_FILE_PARENT |CMS_WMBS_PREPROD4_DATA01 |
WMBS_FILE_RUNLUMI_MAP |CMS_WMBS_PREPROD4_DATA01 |
WMBS_LOCATION_STATE |CMS_WMBS_PREPROD4_DATA01 |
WMBS_LOCATION |CMS_WMBS_PREPROD4_DATA01 |
WMBS_PNNS |CMS_WMBS_PREPROD4_DATA01 |
WMBS_FILE_LOCATION |CMS_WMBS_PREPROD4_DATA01 |
WMBS_LOCATION_PNNS |CMS_WMBS_PREPROD4_DATA01 |
WMBS_USERS |CMS_WMBS_PREPROD4_DATA01 |
...
49 rows selected.
but the real number of columns is much much bigger:
SQL> describe user_tables;
Name Null? Type
--------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(128)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(128)
IOT_NAME VARCHAR2(128)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
...
As one can see only two of them are fit in the terminal's width. The alternative is to have line wrpap on and make the printout extremely unreadable. like :
SQL> set wrap on
SQL> select * from user_tables;
text
TABLE_NAME |TABLESPACE_NAME
================================================================================================================================|==============================
CLUSTER_NAME
================================================================================================================================
IOT_NAME |STATUS | PCT_FREE| PCT_USED| INI_TRANS| MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS
================================================================================================================================|========|==========|==========|==========|==========|==============|===========|===========|===========
PCT_INCREASE| FREELISTS|FREELIST_GROUPS|LOG|B| NUM_ROWS| BLOCKS|EMPTY_BLOCKS| AVG_SPACE| CHAIN_CNT|AVG_ROW_LEN|AVG_SPACE_FREELIST_BLOCKS|NUM_FREELIST_BLOCKS|DEGREE |INSTANCES |CACHE|TABLE_LO|SAMPLE_SIZE|LAST_ANAL|PAR|IOT_TYPE
============|==========|===============|===|=|==========|==========|============|==========|==========|===========|=========================|===================|==========|==========|=====|========|===========|=========|===|============
T|S|NES|BUFFER_|FLASH_C|CELL_FL|ROW_MOVE|GLO|USE|DURATION |SKIP_COR|MON|CLUSTER_OWNER |DEPENDEN|COMPRESS
=|=|===|=======|=======|=======|========|===|===|===============|========|===|================================================================================================================================|========|========
COMPRESS_FOR |DRO|REA|SEG|RESULT_|CLU|ACTIVITY_TRACKING |DML_TIMESTAMP |HAS|CON|INMEMORY|INMEMORY|INMEMORY_DISTRI|INMEMORY_COMPRESS|INMEMORY_DUPL
==============================|===|===|===|=======|===|=======================|=========================|===|===|========|========|===============|=================|=============
DEFAULT_COLLATION |D|S|EXT|HYB|CELLMEMORY |CON|CON|EXT|EXT|INMEMORY_SER
====================================================================================================|=|=|===|===|========================|===|===|===|===|============
INMEMORY_SERVICE_NAME
=============================================================================================================================================================================================================================================
CON|MEMOPTIM|MEMOPTIM|HAS|ADM|DAT|LOGICAL_
===|========|========|===|===|===|========
WMBS_FILESET |CMS_WMBS_PREPROD4_DATA01
|VALID | 10| | 1| 255| | | |
| | |YES|N| | | | | | | | | 1| 1| N|ENABLED | | |NO |
N|N|NO |DEFAULT|DEFAULT|DEFAULT|DISABLED|NO |NO | |DISABLED|YES| |DISABLED|DISABLED
|NO |NO |NO |DEFAULT|NO | | |NO |NO |DISABLED| | | |
USING_NLS_COMP |N|N|NO |NO | |NO |NO |NO |NO |
NO |DISABLED|DISABLED|NO |NO |NO |ENABLED
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WMBS_FILE_DETAILS
Describe the solution you'd like
There is an alternative client provided by oracle which solve those issues (by using the SQLFORMAT ansiconsole option) and much more as presented here: sqlcl on slideshare.
- The client is still under a free license: https://www.oracle.com/downloads/licenses/oracle-free-license.html
- It is part of the SQLdeveloper package, but could be downloaded and run independently
- The
sqlclclient Download page is here: https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/ - The
sqlclclient Dcoumentations page is here: https://docs.oracle.com/en/database/oracle/sql-developer-command-line/index.html - Provides the ability to write java script inline (through a Client Side Scripting)
- The client itself is just 67 MB. Unfortunately it is java based and requires Java runtime environment, which on its own side increases the Docker image size significantly.
Describe alternatives you've considered Do nothing and continue fighting with SqlPlus
Additional context None
