create table语句在存储过程中报错?

使用listagg函数拼接了动态建表语句:

 SELECT listagg(n.create_tab, ' ') WITHIN GROUP(ORDER BY ROWNUM DESC) NAME,
           i_id as id
      FROM (Select 'create Table ' || Tab_Name || ' ( ' || Tab_Body || ');' ||
                   chr(10) As Create_Tab
              From (Select Tab_Name,
                           tab_cn_name,
                           Listagg(Tab_Body) Within Group(Order By index_id) As Tab_Body
                      From (Select a.en_name As Tab_Name,
                                   a.chn_name As tab_cn_name,
                                   b.en_name || ' ' || b.data_type || Case
                                     When b.index_id < A1.Max_Id Then
                                      ',' || chr(10)
                                     Else
                                      ''
                                   End As Tab_Body,
                                   b.index_id,
                                   'comment on Table ' || a.en_name || ' is ' ||
                                   a.chn_name || ''';' as tab_comments,
                                   'comment on Column ' || a.en_name || '.' ||
                                   b.en_name || ' is ''' || b.chn_name ||
                                   ''';' as col_comments
                              From dmp.t_ds_model a --Table
                             Inner Join (Select count(*) As Max_Id, a.parent_id
                                          From dmp.t_ds_model a
                                         Where a.class_code = 'Column'
                                         group by a.parent_id) A1
                                On a.id = a1.parent_id
                              Left Join dmp.t_ds_model b --Column
                                On a.id = b.parent_id
                             Where b.class_code = 'Column'
                               AND a.id = i_id) --更改
                     Group By Tab_Name, tab_cn_name)
            union
            select 'comment on Table ' || T.En_Name || ' is ''' ||
                   t.chn_name || '''; ' || chr(10)
              from dmp.t_ds_model t
             where t.class_code = 'Table'
               AND t.id = i_id
            union
            select Listagg(col_comment) Within Group(Order By index_id) As com_body
              from (select 'comment on Column ' || t1.en_name || '.' ||
                           t2.en_name || ' is ''' || t2.chn_name || ''';' ||
                           CHR(10) as col_comment,
                           t2.index_id
                      from dmp.t_ds_model t1 --Table   
                     inner join dmp.t_ds_model t2 --Column
                        on t1.id = t2.parent_id
                     where t1.class_code = 'Table'
                       and t2.class_code = 'Column'
                       AND t1.id = i_id)) n;

之后使用execute immediate 运行建表语句:

select t.model_sql
    into v_sql
    from dmp.t_ds_model_sql t
   where t.model_id = i_id;
  execute immediate v_sql;

测试存储过程报错:
图片说明
把单独的建表语句在SQL窗口中执行可以正常执行。
求问大神这种情况要怎么改!??

1个回答

用 select v_sql; 输出一下sql,看看里面有没有缺少或多了标点符号

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐