Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

View with function to handle "long" columns does fail on column naming, not on *

bveMar 27 2024 — edited Mar 27 2024

I created this view to handle columns of type “long”. Using Oracle 19.0.0.0
(I couldn't create it in live oracle - it fails on parsing I presume, please any hint why this is or a rewrite to fix is welcome).
But I can create this (exact same) view in our Oracle Enterprise Edition Release 19.0.0.0.

The peculiarity is that a “SELECT * FROM V_TABLE_ALIASES” works fine, but a “SELECT table_name FROM v_table_aliases” fails with a “ORA-00911: invalid character”, pointing to the view name.

Based on this I expect this to be a bug (select * from view works, select <some columns> from view fails … ).

create or replace view v_table_aliases
as
with 
function long_as_clob( p_table_name   in  user_tab_columns.table_name%type
                     , p_column_name  in  user_tab_columns.column_name%type )
     return clob
     as
       l_long  long;
     begin
       if p_column_name is not null
       then
         select tcn2.data_default
         into   l_long
         from   user_tab_columns  tcn2
         where  tcn2.table_name   =  p_table_name
         and    tcn2.column_name  =  p_column_name;
       end if;
       return to_clob(l_long);
     end long_as_clob;
select tab.table_name                                                                     as  table_name
,      replace( to_char( long_as_clob( tcn.table_name, tcn.column_name ) ), '''', null )  as  tech_tab_alias
from   user_tables                       tab
       left join user_tab_columns        tcn
         on  tcn.table_name   =  tab.table_name
         and tcn.column_name  =  'TECH_TAB_ALIAS'
/
This post has been answered by User_3ABCE on Mar 27 2024
Jump to Answer
Comments
Post Details
Added on Mar 27 2024
2 comments
62 views