How to customise/format the output of DESCRIBE command in SQL (Oracle)?-CodePudding

I have created a table BORROW with the following attributes and datatypes of the corresponding columns as shown below:


SQL> CREATE TABLE BORROW
  2  (
  3  LOANNO VARCHAR2(5),
  4  CNAME VARCHAR2(20),
  5  BNAME VARCHAR2(20),
  6  AMOUNT NUMBER(8,2)
  7  );

Table created.

SQL> DESCRIBE BORROW;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOANNO                                             VARCHAR2(5)
 CNAME                                              VARCHAR2(20)
 BNAME                                              VARCHAR2(20)
 AMOUNT                                             NUMBER(8,2)

When I am running the DESCRIBE command, I want it to show like it is shown in the picture instead: (https://i.stack.imgur.com/V001t.jpg)

Does anyone know how to do this?

Thanks in advance :)

CodePudding user response：

DESCRIBE is a SQLPlus command and is not configurable. But you can query the data dictionary yourself and present it any way you want. If you want a single size column you have to do some conditional logic since how Oracle represents "size" and depends on the datatype and what you mean by "size" (character length vs bytes needed to store it, number precision vs. precision scale vs. bytes needed to store it, etc. etc..) Here's something to start with:

SELECT column_name field_name,
       data_type,
       CASE WHEN (data_type LIKE '%CHAR%') THEN TO_CHAR(char_length)
            WHEN (data_type LIKE '%NUMBER%') THEN '('||NVL(TO_CHAR(data_precision),'*')||','||NVL(TO_CHAR(data_scale),'*')||')'
            WHEN (data_type = 'RAW') THEN TO_CHAR(data_length)
            ELSE NULL
       END "size"
  FROM all_tab_columns
 WHERE table_name = 'BORROW'
 ORDER BY column_id

CodePudding user response：

The DESCRIBE command in Oracle SQL does not have an option to display the field names, data types, and sizes in the format you specified. However, you can use the DESCRIBE command in combination with other SQL commands to achieve the desired output. One way to do this is to query the USER_TAB_COLUMNS view, which contains information about all columns in all tables in the current schema.

You can use the following query to get the desired output:

SELECT column_name as "FIELDNAME", data_type as "DATATYPE", data_length as "SIZE"
FROM user_tab_columns
WHERE table_name = 'BORROW';