G'day.
I am trying to retrieve the colum name from the first column selected in a query. I have no idea what the column name is, however I was pointed in the direction of the following code snippet to help me retrieve the name:
DECLARE
v_rc_sv SYS_REFCURSOR;
v_query VARCHAR2(4000);
v_value_col_name VARCHAR2(4000) := NULL;
v_updated_query VARCHAR2(4000);
BEGIN
v_query := 'SELECT val FROM a_table';
OPEN v_rc_sv FOR v_query;
FOR gen_cursor IN (
SELECT ROWNUM rn,
t2.COLUMN_VALUE.getRootElement () NAME,
EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE
FROM TABLE (XMLSEQUENCE (v_rc_sv)) t,
TABLE (XMLSEQUENCE (EXTRACT (COLUMN_VALUE, '/ROW/node()'))) t2)
LOOP
IF v_value_col_name IS NULL THEN
v_value_col_name := gen_cursor.NAME;
EXIT;
END IF;
END LOOP;
CLOSE v_rc_sv;
-- Now we have the column name, we can construct a query string which
-- wraps the original query with a standard select statement that includes the column
-- name.
v_updated_query := 'SELECT ' || v_value_col_name || ' FROM (' || v_query || ')';
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
From the last line, you can see I use the name of the column to wrap the query string, so that eventually I can use the updated query to retrieve the value from the query.
My question is, can I also use the same method (by using XMLSEQUENCE) to tell me how many columns have been selected in the unknown string ? Also can it tell me if the unknown query has put an alias on any of the select columns (and what they might be) or the query is using the original column name ? ie: select name from person_table VS select name the_name from person_table (where the_name is the column alias) ?
Edited by: Greg Block on May 27, 2010 5:59 PM
Apologies for the formatting, I had typed it nicely :)
Edited by: Greg Block on May 27, 2010 8:55 PM
Updated with the formatting tag, thanks for the tip