How to get column names for SQL object in PeopleCode?

Question

I have an iscript that runs one of a collection of previously created SQL statements, binds several parameters, and generates an XML result.

The SQL used in each request varies in the number of parameters and the number of (and name of) columns returned.

Everything was pretty straightforward to develop except for one outstanding issue: how can I collect the column names and include that information in the returned data?

Currently we are using the CreateSQL command and a SQL object. As far as I know, we can only loop through the result values, and not a dictionary of the column names and values.

How can I return the column names with the results in PeopleCode, in the context of an iscript, with (essentially) dynamic SQL that can't be known ahead of time?

Answer 1

I have a way to get the column name by PLSQL, just a little complex.

First, create a table to store a long string in the Application Designer:

-- 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
  );

Second, use DBMS_SQL in a new funciton:

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 ;

Last, use peoplecode to get the column names:

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, ";");