User defined variable as column alias

[thessa]

Here's the question: How can I create a dynamic column alias using a user defined variable? For example I am creating reports where the column names of the report must be in the user's selected language. So before I fill my out cursor with a select statement, I gather the appropriate translation for the aliases into a variable. The appropriate piece of the proc looks something like this (haven't actually written it yet so suggestions on other methods are appreciated)

SELECT rpt_col_name INTO v_translated_name FROM col_descr WHERE c_lang='E' and db_field_name="Asset Class";

Open out_cursor For
Select Field_Name v_translated_name from table_name ....

And I want the value of the variable to print as the alias, not "v_translated_name".
Thanks in advance
Thessa

 

[thessa]

Oops, forgot to say I'm using 8i, so in theory I can use dynamic SQL if I have to...

 

[sem]

You may base your report on weak ref cursor. For sql*plus:

var c refcursor
begin
open :c for '<your select statement>';
end;
/
print c


You may also utilize the good old technique of calling main script from generated on the fly command one passing headers as parameters (substituting variables).

 

[thessa]

Thanks for the reply sem. I am not really understanding your suggestions. Am I mistaken or do they both come down to housing the alias in a variable in the end? For example, if I select the field that holds the alias name in the cursor example you have provided, don't I then have to pass it to the select statement that gathers the data for the report? I am thinking this would also apply to the second suggestion... But my problem is that once I get the alias into a variable, I don't know how to get it out of the variable again into an *actual* alias that prints the value of the variable and not the name of the variable. hmmm, something tells me I'm just not clueing in to what you have described for me. Do you mind elaborating? Thanks! Thessa

 

[rcurva]

If you can read metalink note:62592.1 there is an example that could fit your requirement.

declare
str varchar2(200);
type my_curs_type is REF CURSOR; -- must be weakly typed
curs my_curs_type;

-- Use a nested table to fetch into. This could equally be
-- a VARRAY or index by table.
type string_tab is table of varchar2(20);
ret_tab string_tab; -- don't need to initialise, fetching
-- will do this automatically
begin
str := 'select msg from msg';
OPEN curs FOR str;
FETCH curs BULK COLLECT INTO ret_tab;
dbms_output.put_line('Array fetch: ');
for i in 1..curs%rowcount loop
dbms_output.put_line(ret_tab(i));
end loop;
CLOSE curs;
end;

 

[sem]

Here's an example

var c refcursor
var lang varchar2(2)
exec :lang := 'EN';

-- your procedure makes a decision based on lang value
begin
if :lang = 'EN' then
open :c for 'select 1 english from dual';
elsif :lang = 'DE' then
open :c for 'select 1 german from dual';
elsif :lang = 'RU' then
open :c for 'select 1 russian from dual';
else
raise_application_error(-20999, 'Wrong language!')
end if;
end;
/
print c


Note, that you construct statement on the fly so may select alias names (headers) from some source.