jeudi 22 janvier 2015

How to cast as type of a table column?


I have multiple views with union like this:



select column1, column2 from mytable
union
select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable


But later I change the size of column2 to nvarchar(20) and now I need to change cast(extcol2 as nvarchar2(10)) to cast(extcol2 as nvarchar2(20)), in every view or reference that could be exist.


I tried it:



select column1, column2 from mytable
join
select cast(extcol as mytable.column1%TYPE), cast(extcol2 as mytable.column2%TYPE) from exttable


It throw me an error:



SQL Error: ORA-00911: invalid character
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.


What can I do?





Aucun commentaire:

Enregistrer un commentaire