print Printer View e-mail E-mail this page Bookmark Bookmark
Purpose
To inform migration specialist how to resolve naming conflicts between PL/SQL variables and column names in migrations from T_SQL.
Scope & Application
The audience for this document is Oracle Customers, Oracle Partners and Oracle Migration Specialists performing SQL Server or Sybase Migrations
Naming Conflicts Between PL/SQL Variables And Column Names

In T-SQL variable names are always preceeded by the @ character. SQLServer and Sybase column names
cannot begin with the @ character, so it is not possible to have naming conflicts between variable
names and column names in a T-SQL block.

Oracle does not allow a PL/SQL variable name to start with the @ character. Therefore, as part of the migration effort, any T-SQL variable names must be stripped of their leading @ character. Doing so can result in a naming conflict.

Table 1 shows an example of a naming conflict resulting from removing the leading @ character.

Table 1 - Naming Conflict:
Before After
declare @ename varchar(10)
select @ename = 'jdoe'
update emps set status = 'sacked'
where @ename = ename 		declare ename varchar2(10);
begin
ename := 'jdoe';
update emps set status = 'sacked'
where ename = ename;
end;


The text in bold in the right hand column represents a naming conflict where PL/SQL cannot distinguish
between the column name `ename¿ and the variable name `ename¿.

NOTE: A naming conflict will not result in a compilation error. The code will execute fine but the results
will be incorrect.

The solution is to preceed the PL/SQL variable name with some prefix like `v_¿.
Latest versions of Oracle Migration Workbench have a parser option to use the stored procedure name
as prefix. This is explained in the Related Documents.

Table 2 shows how to resolve the naming conflicts manually.

Table 2 - Naming Conflict Resolved:
Before After
declare @ename varchar(10)
select @ename = 'jdoe'
update emps set status = 'sacked'
where @ename = ename 		declare v_ename varchar2(10);
begin
v_ename := 'jdoe';
update emps set status = 'sacked'
where ename = v_ename;
end;

Related Documents
<Note:239068.1> Procedure Name Appended To Variables

<Bug:2700225> Procedure Name Appended To Variables
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy