How do I make the 'order by' clause in an Oracle function use a variable representing a numeric index?
I'm emulating the DESCRIBE tablename available in Sqlplus to make similiar functionality available via regular sql.
I have the objects/function listed below, and it works - except for the order_param
clause. It's basically ignored. I can call the function via:
select * from table(describe('my_table',1));
or
select * from table(describe('my_table',2));
and it results in the same ordering.
If I replace order by order_param
with either order by 1
or order by 2
in the sql clause in the function, it works as expected.
There must be a better way to get this to work other than to use dynamic sql (or have the function actually choose between 2 sql clauses).
CREATE OR REPLACE TYPE table_description as object (
column_name varchar2(30),
column_id number,
data_type varchar2(30),
nullable varchar2(1)
);
CREATE OR REPLACE TYPE table_description_type as table of table_description;
CREATE OR REPLACE FUNCTION describe (tname IN VARCHAR2, order_param in NUMBER default 2) RETURN table_description_type AS
-- function to describe a table
-- eg. use via select * from table(describe('table_name'));
v_ret table_description_type;
BEGIN
select cast(multiset(
select column_name, data_type || data_suff "Data Type", nullable from (
select column_name, column_id, data_type,
case when data_type = 'DATE' then '' else '(' ||
case when data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR')
then to_char(char_le开发者_StackOverflowngth) else data_precision || ', ' || data_scale end || ')'
end data_suff,
nullable, data_default, num_nulls, last_analyzed, avg_col_len
from all_tab_columns where table_name = upper(tname)
order by order_param
)
) as table_description_type) into v_ret from dual;
return v_ret;
END;
/
You can't do this the way you're attempting to. In order for the order
clause to interpret a numeric value as a column reference, the number has to be there when the query is parsed. Since you're binding the variable in, it will always interpret your parameter as a value, not a column reference. To fix this, you'll want to change the order by
clause logically to select a column based on the value:
order by case order_param
when 1 then column_name
else data_type || data_suff
end
精彩评论