oracle - 如何获取 PeopleCode 中 SQL 对象的列名？

Question

我有一个 iscript运行先前创建的 SQL 语句集合中的一个，绑定(bind)多个参数，并生成 XML 结果。

每个请求中使用的 SQL 在参数数量和返回的列数(和名称)方面有所不同。

除了一个悬而未决的问题外，一切都非常容易开发:我如何收集列名并将该信息包含在返回的数据中？

目前我们正在使用 CreateSQL 命令和一个 SQL 对象。据我所知，我们只能遍历结果值，而不是列名和值的字典。

在 iscript 的上下文中，如何使用无法提前知道的(本质上)动态 SQL 返回 PeopleCode 中的列名和结果？

Answer 1

我有办法通过PLSQL获取列名，只是有点复杂。

首先，在应用程序设计器中创建一个表来存储一个长字符串:

-- create table
create table ps_sql_text_tbl 
(
  comments clob
)
tablespace hrapp
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 40k
    next 104k
    minextents 1
    maxextents unlimited
  );

其次，在新函数中使用 DBMS_SQL:

create or replace function get_column_name return clob is
  l_curid      integer;
  l_cnt        number;
  l_desctab    dbms_sql.desc_tab3;
  l_sql        dbms_sql.varchar2s;
  l_upperbound number;
  l_stmt       clob;
  l_result     clob;
begin
  /*get a sql text into a clob var*/
  select comments into l_stmt from ps_sql_text_tbl where rownum = 1;

  /*200 chars for every substring*/
  l_upperbound := ceil(dbms_lob.getlength(l_stmt) / 200);
  for i in 1 .. l_upperbound loop
    l_sql(i) := dbms_lob.substr(l_stmt, 200, ((i - 1) * 200) + 1);
  end loop;

  l_curid := dbms_sql.open_cursor();

  /*parse the sql text*/
  dbms_sql.parse(l_curid, l_sql, 1, l_upperbound, false, dbms_sql.native);
  /*describe column names*/
  dbms_sql.describe_columns3(l_curid, l_cnt, l_desctab);
  /*concatenate all column names*/
  for i in 1 .. l_desctab.count loop
    /*max length limited to 30 chars for every column name*/
    l_result := l_result || rtrim(rpad(l_desctab(i).col_name,30)) || ';';
  end loop;

  dbms_sql.close_cursor(l_curid);

  return l_result;
exception
  when no_data_found then
    return '';
end get_column_name ;

最后，使用 peoplecode 获取列名:

Local string &sqlText="select * from dual";

SQLExec("truncate table ps_sql_text_tbl");
SQLExec("insert into ps_sql_text_tbl values(%TextIn(:1)) ", &sqlText);
SQLExec("commit");

Local string &columnNames;
SQLExec("select get_column_name() from dual", &columnNames);

Local array of string &arrayColumnNames= Split(&columnNames, ";");