6

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?

Community
  • 1
  • 1
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • 1
    If you are limited to Oracle 12.1, then no there is no way you can create an identifier that is longer than 30 characters –  Jul 08 '19 at 14:13
  • @a_horse_with_no_name see my 2 options I edit – Ori Marko Jul 08 '19 at 14:13
  • What export method do you use? – Alex Poole Jul 29 '19 at 18:01
  • @AlexPoole with PlSql developer – Ori Marko Jul 29 '19 at 18:16
  • I think you may be confusing column aliases in your SQL statement with real table column names; SQL Plus and SQL Developer will let you label a column in the output with most anything like `selectlongtextfromveryverylongtablename` but that is just the reporting/exporting of the results, not any actual column names. – Mark Stewart Jul 30 '19 at 23:12
  • @MarkStewart excel's column names after export – Ori Marko Jul 31 '19 at 03:50
  • @AlexPoole Can you suggest other ways exporting columns with long names besides PL/SQL developer? – Ori Marko Aug 04 '19 at 15:03
  • @user7294900 - you still haven't explained exactly what you're doing now. But if you're just generating a CSV text file I'd do [something like this](https://stackoverflow.com/a/31836837/266304) (not sure if PL/SQL has a `prompt` equivalent, but a query to generate the fake headers should still work), and save the output. If you aren't already then concatenate the real column together. (This is why I wanted to know what you're actually doing now...). Or [another example](https://stackoverflow.com/a/40532627/266304); or tools like SQLcl have more options for CSV extracts. – Alex Poole Aug 04 '19 at 19:17

2 Answers2

0

According to AllRoundAutomations it isn't possible

On Oracle 12.1 this is not possible. On Oracle 12.2 and later you can use long identifiers.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
0

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.

Subash
  • 76
  • 6