I have a string say something like
select c1,c2,c3 from tab1 which i form dynamically.
Is there anyway i can have a cursor associated with it?
something like cursor c1 is (execute str)
I don't know the column names since i have generated them dynamically and formed them into string.
but i can get the column names into a var say
So if i have to get a particular data for the column using the cursor how do i do it?
You don't manually pull data from a cursor into variables. Let the syntax of the Execute Immediate command do it for you. For example:
cursor c1 is
select 'select count(*) from ' || object_name
where object_type = 'TABLE';
fetch c1 into v1;
exit when c1%notfound;
v2 := v1;
execute immediate (v1) into (v3);
1. the above code was from memory. I did not check for syntax.
2. if you are doing DDL then the dynamic sql must include BEGIN nd END statements around the command. This is not required when performing DML, as shown above.
3. I reassigned the dynamic SQL statement into variable v2 just prior to runtime due to an Oracle bug that occassionally yields error "end of communication on channel ..."