Search code examples
exceloracle-databaseexport-to-excelplsqldeveloperora-00972

Oracle - export results to excel with headers/columns more than 30 characters

I can export a Oracle (12.1) SQL results to excel using PL/SQL developer

But sometimes the requirements are to give a meaningful name for the column/header

for example "total amount for previous 21 days"

Obviously, it exceed 30 characters and gets ORA-00972 identifier is too long

prior to Oracle version 12.2, identifiers are not allowed to exceed 30 characters in length. See the Oracle SQL Language Reference.

However, from version 12.2 they can be up to 128 bytes long. (Note: bytes, not characters).

  • This question relevant also to newer version limit

Can I export with different column names without manually renaming in output excel?

EDIT

When I define a not explicit alias it can pass the 30 limit, e.g. using inner select

(select 'longtext' from veryverylongtablename),

Will create a column selectlongtextfromveryverylongtablename

Or

'total amount for previous 21 days'||id

Will create a column totalamountforprevious21daysis

So is there a workaround for showing meaningful headers?

Solution

  • No, it isn't possible to do this. As stated in the docs, the maximum length of object name (tables, columns, triggers, packages, etc.) is 30 bytes:

    http://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51129

    The only exceptions are database names (8 byte limit) and database links (128 bytes).

    As of Oracle Database 12.2, the maximum length of names increased to 128 bytes (provided compatible is set to 12.2 or higher). Database names are still limited to 8 bytes. And the names of disk groups, pluggable databases (PDBs), rollback segments, tablespaces, and tablespace sets are limited to 30 bytes.