1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
| function execute_request(aSql in clob) return clob is
myCur number := dbms_sql.open_cursor;
myClob clob := null;
myStr varchar2(1000);
myLong number;
myDate date;
myTab dbms_sql.desc_tab;
myCnt number;
begin
--Init
dbms_sql.parse(myCur, aSql, dbms_sql.native);
dbms_sql.describe_columns(myCur, myCnt, myTab);
myLong := dbms_sql.execute(myCur);
--Cast Columns
for j in 1 .. myCnt loop
case myTab(j).col_type
when 1 then
dbms_sql.define_column(myCur, j, myStr, myTab(j).col_max_len); --String
when 2 then
dbms_sql.define_column(myCur, j, myLong); --Number
when 3 then
dbms_sql.define_column(myCur, j, myDate); --Date
end case; end loop;
--Title
for j in 1 .. myCnt loop
myClob := myClob || rpad(myTab(j).col_name, myTab(j).col_max_len, ' ') || '|';
end loop;
myClob := myClob || Chr(13);
for j in 1 .. myCnt loop
myClob := myClob || rpad('-', myTab(j).col_max_len, '-') || '|';
end loop;
myClob := myClob || Chr(13);
--Data
loop
myLong := dbms_sql.fetch_rows(myCur);
exit when myLong = 0;
for j in 1 .. myCnt loop
case myTab(j).col_type
when 1 then
dbms_sql.column_value(myCur, j, myStr);
when 2 then
dbms_sql.column_value(myCur, j, myLong);
myStr := to_char(myLong);
when 3 then
dbms_sql.column_value(myCur, j, myDate);
myStr := to_char(myDate, 'DD/MM/YYYY');
end case;
myStr := nvl(myStr, '(null)');
myClob := myClob || rpad(myStr, myTab(j).col_max_len) || '|';
end loop;
myClob := myClob || Chr(13);
end loop;
dbms_sql.close_cursor(myCur);
return myClob;
end execute_request; |
Partager