空Oracle DEF CURSOR中的列名

在PL / SQL中,我可以使用这样的技巧来查找REF CURSOR中每行的每个列的名称/值对：

TO_CHAR of an Oracle PL/SQL TABLE type

这是一个很棒的技巧.但是当REF CURSOR为空时它不起作用,例如这里的那个(这只是一个例子.真正的光标不从DUAL中选择)：

OPEN cursor FOR SELECT 1 FROM DUAL WHERE 1 = 0;

空REF CURSOR是否有列名/类型信息？

解决方法

1)Dbms_sql包检索列的名称.

2)检索数据的tbone方法.

程序

create or replace procedure demo(sqlText in varchar2) is
    refCur sys_refcursor;
    curId  integer;
    cnt    number;
    ret    dbms_sql.desc_tab;
    recTab dbms_sql.desc_tab;
    FORMAT_STRING constant pls_integer := 20;

    procedure printDescTab(desctab in sys.dbms_sql.desc_tab) is
    begin
        -- do what you want with the columns
        for i in 1 .. desctab.count
        loop
            dbms_output.put(lpad(desctab(i).col_name,FORMAT_STRING));
        end loop;
        dbms_output.new_line;
    end printDescTab;

    procedure PrintCur(cv in sys_refcursor) is
    begin
        for c in ( --select t2.COLUMN_VALUE.getrootelement() name,select EXTRACTVALUE(t2.COLUMN_VALUE,'node()') value
                  from   table(XMLSEQUENCE(cv)) t,table(XMLSEQUENCE(EXTRACT(COLUMN_VALUE,'/ROW/node()'))) t2)
        loop
            DBMS_OUTPUT.put(lpad(c.VALUE,FORMAT_STRING));
        end loop;
        dbms_output.new_line;
        dbms_output.new_line;
    end;

begin
    dbms_output.put_line('dynamic sql: ' || sqlText);
    curId := dbms_sql.open_cursor();
    --  checks for sql injection to do...
    dbms_sql.parse(curId,sqlText,dbms_sql.native);
    dbms_sql.describe_columns(curId,cnt,recTab);
    printDescTab(recTab);
    dbms_sql.close_cursor(curId);

    open refCur for sqlText;
    PrintCur(refCur);
    close refCur;
exception
    when others then
        if dbms_sql.is_open(curId) then
           dbms_sql.close_cursor(curId);
        end if;
        if refCur%isopen then
            close RefCur;
        end if;
        dbms_output.put_line(sqlcode || ' - ' || sqlerrm);
end demo;

测试

declare
    sqlText varchar2(2000);
begin
    sqlText := 'select 1 as one,2 as two  from  dual where 1=0';
    demo(sqlText);
    sqlText   := 'select name,type || chr(13) type' -- chr(13) specific ASCII Carriage return
                ||' from   user_plsql_object_settings'
                ||' where name not like ''%$%'' and  rownum <= 10';      
    demo(sqlText);   
    sqlText := 'select 1 as one,2 as two  from  dual ';                  
    demo(sqlText);

exception
    when others then
        dbms_output.put_line(sqlcode || ' - ' || sqlerrm);
end;

结果

dynamic sql: select 1 as one,2 as two  from  dual where 1=0
                 ONE                 TWO


dynamic sql: select name,type || chr(13) type from   user_plsql_object_settings where name not like '%$%' and  rownum <= 10
                NAME                TYPE
     ADD_JOB_HISTORY          PROCEDURE
    AFT_INS_TEST_TRG            TRIGGER
    BEF_DEL_TEST_TRG            TRIGGER
    BEF_INS_TEST_TRG            TRIGGER
            BETWNSTR           FUNCTION
                BOOL           FUNCTION
    CACHED_FIBONACCI           FUNCTION
               DEBUG            PACKAGE
               DEBUG       PACKAGE BODY
          DEBUG_TEST          PROCEDURE


dynamic sql: select 1 as one,2 as two  from  dual 
                 ONE                 TWO
                   1                   2

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!