SYS.DBMS_DDL.WRAP不允许杂注和intctx

Question

我使用下面的过程包装PL/SQL代码。

declare
  l_source  DBMS_SQL.VARCHAR2A;
  l_wrap    DBMS_SQL.VARCHAR2A;
  l_wrap1    clob;
  typ_ibt   utl_file.file_type;
  cnt       number := 0;
  v_directory varchar2(400) := 'd:\ftpedi\eqpm\eqpm_hold\';

  cursor cur_name_get is
  select distinct name object_name,type object_type
  from   user_source
  where  type = 'PROCEDURE'
  and    name = 'PROCESS_TIME_INSERT';

  cursor cut_text_get ( p_type in varchar2 , p_name in varchar2 )  is
  select replace(text,chr(10),'') text
  from   user_source
  where  type = p_type
  and    name = p_name;
begin

for i in cur_name_get
loop
     l_source.delete;l_wrap.delete;
     open cut_text_get ( i.object_type,i.object_name );
     fetch cut_text_get bulk collect into l_source;
     close  cut_text_get;
     l_source (1) := 'CREATE OR REPLACE ' || l_source (1);
     l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,
                                lb  => 1,
                                ub  => l_source.count);
     for i in 1..l_wrap.count
     loop
        if i = 1
        then
            l_wrap1 := l_wrap(i);
        else
            l_wrap1 := l_wrap1 || l_wrap(i);
        end if;
        insert into ibt_global_inter_transfer ( git_process_id,git_c_1)
        values ( 3004, l_wrap1 );
     end loop;                                
  end loop;  

exception when others
then
    dbms_output.put_line('sqlerrm '||sqlerrm||dbms_utility.format_error_backtrace);
end;

上面的过程扭曲了正常的过程，但不允许使用特殊字符，如“PRAGMA”。

下面是示例过程，它不是包装。

CREATE OR REPLACE
PROCEDURE xml_insert ( p_in_xml in xmltype ,p_status out varchar2,p_message out varchar2) is
intctx DBMS_XMLSTORE.ctxtype;
rows number;
begin
    p_status := 'S';
    p_message := 'Success';

    intctx := Dbms_xmlstore.newcontext('IBT_GLOBAL_INTER_TRANSFER');
    dbms_xmlstore.clearupdatecolumnlist(intctx);
    dbms_xmlstore.setupdatecolumn(intCtx,'GIT_PROCESS_ID');
    dbms_xmlstore.setupdatecolumn(intCtx,'GIT_SESSION_ID');
    rows := Dbms_xmlstore.insertxml(intctx,p_in_xml);
    dbms_xmlstore.closecontext(intctx);
exception when others
then
    p_status := 'R';
    p_message := sqlerrm||dbms_utility.format_error_backtrace;
    return;    
end;

有人能帮上忙吗？

Answer 1

更新

(我误解了这个问题。我以为您的意思是包装的代码不是创建的，现在我明白了真正的问题是包装的输出不能编译。)

删除replace，需要在某些行之间留出空格。

替换：

  --select replace(text,chr(10),'') text

通过以下方式：

  select text

操作步骤：

CREATE OR REPLACE
PROCEDURE xml_insert ( p_in_xml in xmltype ,p_status out varchar2,p_message out varchar2) is
intctx DBMS_XMLSTORE.ctxtype;
rows number;
pragma autonomous_transaction; --ADDED
begin
    p_status := 'S';
    p_message := 'Success';

    intctx := Dbms_xmlstore.newcontext('IBT_GLOBAL_INTER_TRANSFER');
    dbms_xmlstore.clearupdatecolumnlist(intctx);
    dbms_xmlstore.setupdatecolumn(intCtx,'GIT_PROCESS_ID');
    dbms_xmlstore.setupdatecolumn(intCtx,'GIT_SESSION_ID');
    rows := Dbms_xmlstore.insertxml(intctx,p_in_xml);
    dbms_xmlstore.closecontext(intctx);
exception when others
then
    p_status := 'R';
    p_message := sqlerrm||dbms_utility.format_error_backtrace;
    return;    
end;
/

PL/SQL块包装代码：

declare
  l_source  DBMS_SQL.VARCHAR2A;
  l_wrap    DBMS_SQL.VARCHAR2A;
  l_wrap1    clob;
  typ_ibt   utl_file.file_type;
  cnt       number := 0;
  v_directory varchar2(400) := 'd:\ftpedi\eqpm\eqpm_hold\';

  cursor cur_name_get is
  select distinct name object_name,type object_type
  from   user_source
  where  type = 'PROCEDURE'
  and    name = 'XML_INSERT'; --CHANGED

  cursor cut_text_get ( p_type in varchar2 , p_name in varchar2 )  is
  --select replace(text,chr(10),'') text  --WOOPS!
  select text
  from   user_source
  where  type = p_type
  and    name = p_name;
begin

for i in cur_name_get
loop
     l_source.delete;l_wrap.delete;
     open cut_text_get ( i.object_type,i.object_name );
     fetch cut_text_get bulk collect into l_source;
     close  cut_text_get;
     l_source (1) := 'CREATE OR REPLACE ' || l_source (1);
     l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,
                                lb  => 1,
                                ub  => l_source.count);
     for i in 1..l_wrap.count
     loop
        if i = 1
        then
            l_wrap1 := l_wrap(i);
        else
            l_wrap1 := l_wrap1 || l_wrap(i);
        end if;
        --insert into ibt_global_inter_transfer ( git_process_id,git_c_1)
        --values ( 3004, l_wrap1 );
        dbms_output.put_line(l_wrap1);
     end loop;                                
  end loop;  

exception when others
then
    dbms_output.put_line('sqlerrm '||sqlerrm||dbms_utility.format_error_backtrace);
end;
/