ryeandi
asked on
Get Column Names From SELECT statement
Oracle 10.2.0.3
I have a PL/SQL procedure that is passed a valid SELECT statement as a VARCHAR2 parameter. I would like to be able to get the column names from this statement. Below is a simple example of that gets to the root of what i'd like to do.
CREATE OR REPLACE PROCEDURE proc_print_col_names (p_select IN VARCHAR2)
AS
v_column_name VARCHAR2(255) := NULL;
BEGIN
v_column_name := ????? -- Get first column from p_select
WHILE v_column_name IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Colu
v_column_name := ????? -- Get next column from p_select
END LOOP;
END;
Please help!
I have a PL/SQL procedure that is passed a valid SELECT statement as a VARCHAR2 parameter. I would like to be able to get the column names from this statement. Below is a simple example of that gets to the root of what i'd like to do.
CREATE OR REPLACE PROCEDURE proc_print_col_names (p_select IN VARCHAR2)
AS
v_column_name VARCHAR2(255) := NULL;
BEGIN
v_column_name := ????? -- Get first column from p_select
WHILE v_column_name IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Colu
v_column_name := ????? -- Get next column from p_select
END LOOP;
END;
Please help!
ASKER
Can you give me an example, using the code skeleton above, where I can pass the following SELECT statements:
SELECT statement 1:
SELECT
emplid,
full_name,
TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS date_today
FROM
employees
WHERE last_name = 'Jones';
SELECT statement 2:
SELECT
address1,
city,
state_code AS state,
potal_coe AS zip
FROM
addresses
WHERE emplid = '12345';
**************************
And get the following results:
Results from passing SELECT statement 1:
Column Name = emplid
Column Name = full_name
Column Name = date_today
Results from passing SELECT statement 2:
Column Name = address1
Column Name = city
Column Name = state
Column Name = zip
Looking at the REF CURSOR links you sent I did not see anything that pointed to me being able to do this.
SELECT statement 1:
SELECT
emplid,
full_name,
TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS date_today
FROM
employees
WHERE last_name = 'Jones';
SELECT statement 2:
SELECT
address1,
city,
state_code AS state,
potal_coe AS zip
FROM
addresses
WHERE emplid = '12345';
**************************
And get the following results:
Results from passing SELECT statement 1:
Column Name = emplid
Column Name = full_name
Column Name = date_today
Results from passing SELECT statement 2:
Column Name = address1
Column Name = city
Column Name = state
Column Name = zip
Looking at the REF CURSOR links you sent I did not see anything that pointed to me being able to do this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1122
there is sys_refcursor defined in 9i,10g to not need define own type
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2376