-
Explicit Cursor !! Want list of all columns ?
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.
Regards,
Surajit K Mitra
-
You can check out USER_TAB_COLUMNS
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Explicit Cursor !! Want list of all columns ?
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.
Regards,
Surajit K Mitra
-
I think there are some methods of dbms_sql that will let you get that information.
Jeff Hunter
-
No Help with DBMS_SQL .. can someone help me in this..
Regards,
Surajit K Mitra
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|