oracle cursor 为空,空Oracle DEF 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