Explicit Cursor !! Want list of all columns ?

[surajitmitra78]

Hi,

I have a dynamic cursor.

declare....
begin..
open cur_test1 for 'select * from table1;';
...

now in the above scenario i don't know what all columns are available in the table 'table1'.

At runtime is it possible to know the list of all columns in the cursor and how do i point to those columns for value.

Please help.

[LKBrwn_DBA]

You can check out USER_TAB_COLUMNS

[surajitmitra78]

No, i am not looking at that.
I will rephrase my problem in a more clear manner.

Look at the code that i am pasting below.

Assumption:
For a given table there is only 1 primary key column.

Objective:
Provide table name
Provide primary key value

Result: retreive values from the tablename as per the primary key value passed.

--- Code start here
create or replace procedure sp_proc_1
(
ls_tablename user_tables.table_name%type,
ls_pkval1 varchar2
)
is
-- Fetch the primary key column
cursor cur_metadata
is
SELECT uc.table_name, ucc.column_name
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
AND uc.constraint_type = 'P'
AND uc.table_name = ls_tablename
ORDER BY ucc.POSITION;

cursor cur_metadata_1
is
SELECT column_name
FROM user_tab_columns
WHERE table_name = ls_tablename
ORDER BY column_id;

metadata_cur cur_metadata%rowtype;
metadata_1_cur cur_metadata_1%rowtype;

TYPE dyna_cursor_def IS REF CURSOR;
dyna_cursor dyna_cursor_def;

ls_selectstring_1 varchar2(4000) := 'SELECT ';
ls_wherestring varchar2(4000) := ' WHERE ';
ls_finalqry varchar2(4000);
ll_totcolumns number(1) := 0;

begin
open cur_metadata;
loop
fetch cur_metadata into metadata_cur;
exit when cur_metadata%notfound;

ls_wherestring := ls_wherestring || rtrim(upper(metadata_cur.column_name)) || '=''' || ls_pkval1 || '''' || ' AND ';

ll_totcolumns := ll_totcolumns + 1;
dbms_output.put_line('ls_wherestring ' || ls_wherestring);
end loop;
close cur_metadata;

open cur_metadata_1;
loop
fetch cur_metadata_1 into metadata_1_cur;
exit when cur_metadata_1%notfound;
ls_selectstring_1 := ls_selectstring_1 || rtrim(upper(metadata_1_cur.column_name)) || ',';
end loop;
close cur_metadata_1;

ls_selectstring_1 := substr(ls_selectstring_1, 1, (length(ls_selectstring_1)-1)) || ' FROM ' || ls_tablename ;
ls_wherestring := substr(ls_wherestring,1,(length(ls_wherestring)-4));

-- [START] PROBLEM STARTS HERE
-- Dynamic cursor execution
open dyna_cursor_def for ls_selectstring_1;
loop
fetch dyna_cursor_def into dyna_cursor;
exit when dyna_cursor_def%notfound;
dbms_output.put_line('Rownum : ' || dyna_cursor_def%rowcount);
-- i want to fetch the column name here, so that i can trace the values
-- but there should not be any hardcoding of table names
-- the record type variable should be dynamic in nature
end loop;
close dyna_cursor_def;
-- [COMPLETED] PROBLEM ENDS HERE

ls_selectstring_1 := substr(ls_selectstring_1, 1, (length(ls_selectstring_1)-1)) || ' FROM ' || ls_tablename ;
ls_wherestring := substr(ls_wherestring,1,(length(ls_wherestring)-4));

ls_finalqry := ls_selectstring_1 || ls_wherestring;

dbms_output.put_line('ls_finalqry : ' ||lower(ls_finalqry));

exception
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(SQLCODE, 'UNKNOWN APPLICATION ERROR - ' || SQLERRM );

end sp_proc_1;
/

EXEC sp_proc_1(ls_tablename=>'TABLENAME',ls_pkval1=>'TEST')
--- Code ends here

Look at the block which is mentioned like "[START] PROBLEM STARTS HERE"

Let me know if anyone can resolve the issue.

[marist89]

I think there are some methods of dbms_sql that will let you get that information.

[surajitmitra78]

No Help with DBMS_SQL .. can someone help me in this..

[gamyers]

Check out
http://asktom.oracle.com/pls/ask/f?p...37336448016847

A more complex alternative would be to use DBMS_SQL to
1) PARSE the query
2) DESCRIBE the result set
3) DEFINE_COLUMN according to the results from (2)
4) Loop round with FETCH_ROWS for each row returned (in this case there should be 1 as you are using a primary key)
4a) Loop round with COLUMN_VALUE for each column in the row