SQLDeveloper/SQLPlus: can one get a bit more verbose error messages?

Question

I have an issue running an SQL script in SQL developer (the issue is not SQL developer spe=cific though):

Its structure is like so:

CURSOR MY_CURSOR IS (SELECT * FROM ... JOIN ... WHERE ...);

BEGIN
    FOR I IN MY_CURSOR
        LOOP
            ...
        END LOOP;
...
END;

i.e. I first define myself a cursor using some SQL select and then I want to iterate over it.

The select part works fine (i.e. it uses several joins and it produces a valid table), but as soon as I define the FOR I IN MY_CURSOR LOOP .... END LOOP; I get a compile error:

ORA-06550: line 57, column 5:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names
ORA-06550: line 57, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

The error location (line & column) as usual is way off (no idea why the Oracle DB can't properly count these...) and the error message is often rather terse:

How can one efficiently debug this? Can one somehow get the Oracle DB to emit more details, here esp. the offending column identifier(s) that is/are obviously ambigious? Or to display the cursor, so that one could see the statement causing those clashing column names?

My guess is that this issue is due to the misc joins (there are several in my actual statements) causing some column names to be duplicated. In the resulting list I see these having column names with an appended "_1". Is there a way to omit or ignore these (since I am not really interested in these) to prevent this error? Or do I really have to spell out each and every column name in the select (instead of just stating select *)?

Answer 1

A SQL statement can have duplicate column names in its top-level block if being fetched by a client that permits it. When invoked within PL/SQL, the PL/SQL engine is the "client" of the SQL statement, and it does not permit the duplicate column names. That's why it works standalone but not within a PL/SQL block.

Also, the error stack will usually point to the line number of a SQL statement where a parsing error is encountered, if the SQL is standalone. Once you embed it within PL/SQL, you will only get a pointer to the first line (where the SQL begins). The debug, copy-paste it out to a standalone statement, replace any PL/SQL variables with dummy values, and try to reproduce the error. Then you should be given the line number within the SQL where the problem is.

To reproduce your duplicate column error outside of PL/SQL, simply wrap your SQL in select * from ():

SELECT * FROM (
  SELECT * FROM ... JOIN ... WHERE ...
)

That will throw the duplicate column error because the outer query block asks for * which will choke on finding more than one column in the inner block with the same name. However, the error number will point to where the parsing error was found, and that will be the * on the first line, not where the column is duplicated, because that's in the inner block where it isn't actually a problem until something (like an outer query block) needs to reference the columns. So Oracle is being accurate: the error occurs not where the duplicate is located and so where the root problem is, but elsewhere where it can't handle the duplicate.

There is a trick you can use, however, to find the duplicate. Try creating a view with your SQL:

CREATE VIEW test AS SELECT col1,col2,col2,col3 FROM ... JOIN ... WHERE ...

Make sure you have not wrapped it in select *. It will throw the duplicate column error but it should give you a proper line and column number pointing to the duplicate.

Answer 2

The error location (line & column) as usual is way off (no idea why the Oracle DB can't properly count these...)

Oracle only processes a single command at a time (which helps prevent some SQL injection vulnerabilities - such as this one). If you have a script then the client application you are using will parse the script and split it up into individual statements and if there are errors then the line number will refer to the line from the start of that command (and not from the start of the script, because the database will never see the entire script).

the error message is often rather terse

ORA-06550: line 57, column 5:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

The error message here is fairly self-explanatory, you have a query:

SELECT *
FROM   table1 t1
       JOIN table2 t2
       ON t1.something = t2.something

and table1 and table2 have columns with the same identifier in both tables and SELECT * does not know how to name the identical columns. To fix it, explicitly name the columns so that they have unique identifiers/aliases and there are not multiple columns trying to have the same identifier.

SELECT t1.column1,
       t1.column2,
       t1.something AS something1,
       t2.column1 AS other1,
       t2.column2 AS other2,
       t2.something AS something2
FROM   table1 t1
       JOIN table2 t2
       ON t1.something = t2.something

Is there a way to omit or ignore these (since I am not really interested in these) to prevent this error? Or do I really have to spell out each and every column name in the select (instead of just stating select *)?

You could select all from one table but if you want columns form both tables then you will have to de-ambiguate the names from the second table by explicitly listing the columns and assigning duplicates an alias:

SELECT t1.*,
       t2.column1 AS other1,
       t2.column2 AS other2,
       t2.something AS something2
FROM   table1 t1
       JOIN table2 t2
       ON t1.something = t2.something

Or use a USING clause in the join if the only duplicate columns are in the join condition.

SELECT *
FROM   table1
       INNER JOIN table2
       USING (something)

If there are columns that are not in the join condition that have the same identifier then you will still get the same problem as it is only the USING columns that would be de-duplicated in this case.