#1481 create view source loses column names

[Chris Bitmead]

Consider the following (tested in Oracle)...

create view foo(bar) as
select 1 from dual;

Now go to the SquirrelSql Objects tab, VIEW, FOO table, go to the Source tab and you see this:

CREATE OR REPLACE VIEW FOO AS
select
1
from dual

Run this sql and you get:

Error: ORA-00998: must name this expression with a column alias
SQLState: 42000
ErrorCode: 998
Position: 72

In other words, the generated create view has lost the column names.

[Gerd Wagner]

SQuirreL reads the view source (for Oracle) by
SELECT *
FROM SYS.ALL_VIEWS
WHERE OWNER = '...'
AND VIEW_NAME = 'FOO'

That is where you find "select 1 from dual".

If you know a better place to read view sources from Oracle please let me know.
Otherwise you may consult Oracle for the problem.

[Chris Bitmead]

You would notice that when you query SYS.ALL_VIEWS it doesn't give you the...

CREATE OR REPLACE VIEW FOO AS ...

Squirrel must be a prepending that to what Oracle gives you. However this is not sufficient.

I think what you need to be doing is finding the column definition by doing

SELECT * FROM FOO WHERE 1=0, and examining the resulting metadata for the column names. Or alternatively, I think JDBC can give you the metadata...

databaseMetaData = connection.getMetaData();
ResultSet columns = databaseMetaData.getColumns(null,null, "FOO", null);
String sql = "CREATE OR REPLACE VIEW FOO(";
while(columns.next())
{
String columnName = columns.getString("COLUMN_NAME");
sql +=","
sql += columnName;
}
sql += ") AS ";