System procedures sa_describe_query and sa_describe_cursor issue

[Former Member]

Hi all,
How can i get a list of alias column names of the result set for a query?

begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  message 'describe list: '+(select list(name) from sa_describe_query(t_msg));
end

result -> SqlCode: -894, SqlState: "0AW14", Message: "Plan cannot be generated for this type of statement"


begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  begin
    declare crsr cursor using t_msg;
    open crsr;
    message 'cursor list: '+(select list(name) from sa_describe_cursor('crsr'));
    close crsr;
  end
end

result -> cursor list: expression,expression

begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  drop procedure if EXISTS  tt;
  execute IMMEDIATE 'create procedure tt() begin '+t_msg+' end;';
  message 'procedure list: '+(select list(parm_name)
    from sysprocparm join sysprocedure
    where parm_type = 1 and proc_name = 'tt') to client;
end

result -> procedure list: col name 1,col name 2

Is there another way to get alias column names of the result set for a query without creating a procedure?

Regards,
Veselin Ivanov

[VolkerBarth]

It's way easier, you can use the procedure sa_describe_query() in the FROM clause and access its result set that way, such as:

select name
from sa_describe_query('select 123 as "col name 1", 456 as "col name 2"') sp
order by column_number;

which returns

col name 1
 col name 2    

[Former Member]

For a simple query sa_describe_query returns column names but when the query is more complex it may return an SqlCode: -894 error. Possible option is to obtain the out parameters of a temporary procedure like a permanent procedure but I did not find any information in the documentation.

[VolkerBarth]

IMHO, that is a misunderstanding. The procedure is meant to describe a single query but not a code block, so you cannot use it with BEGIN/END statements as you tried.

When using stored procedures, you need to use them as part of the FROM clause, not via a CALL statement. This works fine:

call sa_describe_query('select * from sa_conn_info() s');