DBMS_SQL

[Anon]

Hi,

I'm trying to build a dynamic select statemnet (each time the function is called I need to select different column and fetching it's value).

I wrote the following statement:
DBMS_SQL.PARSE(cursor_id,
'select '||col_name||' from XXX',
DBMS_SQL.native);

I want to fetch the value whithin the col_name but I don't know how.

Many thanks

[Anon]

If the problem is that you don't know how to refer to your column in the fetched value, then you may refer to it by it's index: you have only one column in your SELECT statement, so with MySQL it will be like this:

$res=mysql_query('SELECT '.$col_name.' FROM dbname');
while ($arr_res=mysql_fetch_array($res)){
print($arr_res[0]);
}

Or if you have several columns, they will have different indexes.
Hope, that's what you need :-)

[Anon]

Using the Below code I got solution for it. Hope it works for you.

cursor1 := dbms_sql.open_cursor;
dbms_sql.parse (cursor1, 'select '||colName||' from '||all_tables_rec.tname,dbms_sql.v7);
dbms_sql.define_column (cursor1, 1, myCount,30);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows (cursor1) > 0 then
dbms_sql.column_value (cursor1, 1, myCount);
dbms_output.put_line('Table '||all_tables_rec.tname || ' has '|| myCount ||' Value in XXX Column.');
else
exit;
end if;
end loop;
dbms_sql.close_cursor (cursor1);