Errors and Hanging Creating Views With an Underscore in the Alias Using SQL*Developer Migration Workbench (Doc ID 1220013.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle SQL Developer - Version 2.1 to 2.1.1
Information in this document applies to any platform.

Symptoms


A view is created in SQL*Server which uses an underscore in an alias name -

create view dbo.authors_v (lname, fname)
as
select aut_.au_lname,
aut_.au_fname
from dbo.authors aut_



This view is then captured using SQL*Developer Version 2.1.1.64 Build MAIN-64.39 and appears in the captured model as -

create view dbo.authors_v (lname, fname)
as
select aut_.au_lname,
aut_.au_fname
from dbo.authors aut_


This is then converted to Oracle and the create view becomes -

CREATE OR REPLACE VIEW authors_v
AS
SELECT aut.au_lname,
aut.au_fname
FROM authors aut_;



The 'underscore' is kept in the 'from' statement but not in the 'select' part of the statement.

Running this in an Oracle database then fails -

SQL> CREATE TABLE authors (
2 au_id VARCHAR2(11 CHAR) NOT NULL,
3 au_lname VARCHAR2(40 CHAR) NOT NULL,
4 au_fname VARCHAR2(20 CHAR) NOT NULL,
@ 5 phone CHAR(12 CHAR) DEFAULT 'UNKNOWN' NOT NULL,
6 address VARCHAR2(40 CHAR),
7 city VARCHAR2(20 CHAR),
8 state CHAR(2 CHAR),
9 zip CHAR(5 CHAR),
10 contract NUMBER(1,0) NOT NULL
11 );

Table created.

SQL> CREATE OR REPLACE VIEW authors_v
2 AS
3 SELECT aut.au_lname,
4 aut.au_fname
5 FROM authors aut_;
aut.au_fname
*
ERROR at line 4:
ORA-00904: "AUT"."AU_FNAME": invalid identifier


Because of the errors it may be that the migration appears to hang.

Removing the 'underscore' from the alias allows the view to be created -

SQL> CREATE OR REPLACE VIEW authors_v
2 AS
3 SELECT aut.au_lname,
4 aut.au_fname
5 FROM authors aut;

View created.

SQL>


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms