JPablos JPablos - 3 months ago 17
SQL Question

View/edit table structure with SQL in Base

I'm JPablos and I trying to view the structure of "orders" table.

I'm using Base

LibreOffice Versión: 5.2.0.4 Id. de compilación: 1:5.2.0~rc4-0ubuntu1~xenial2 Subprocesos de CPU: 1; Versión de SO: Linux 4.4


SQL statement

select listagg(column_name ||','|| data_type ||','|| case
when data_type in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'RAW')
then to_char(data_length)
when data_type = 'NUMBER' and (data_precision is not null or data_scale is not null)
then data_precision || case
when data_scale > 0 then '.' || data_scale
end
end, ',') within group (order by column_id)
from all_tab_columns where table_name = 'orders';


Then SQL informs me


1: Access is denied: LISTAGG in statement [select listagg(]


Note: obviously... the easy way in Base UI: select "orders" / right click / Edit, and yes it opens the structure of table "orders". But, I want to use SQL to do it.

Thanks in advance

JPablos

Answer

after all it is a SQL statement to do the query object of my question above, and is:

SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_COLUMNS" WHERE "TABLE_NAME" = 'Students'

Where "Students" is the name of a table used for this answer.

The SQL statement reports:

Result of the query

Best regards

JPablos