I have a dynamic sql like ' Select || fields|| from || tab|| where || where_clause||
here fields,tab & where_clause is populated dynamically.
I have a requirmeent, wherein,I have to execute this query ,which will result in multiple rows and result of each rows to be concatenated (1,'abc','23-03-2003') and retuned ,to be used in a Insert proc which I have.
I have the logic for concatenation of values depending on the datatype,but how do I loop through each record for the above query????

Can anybody help me or anybody has a reusable code for doing this.

Hello,

when you use Oracle you can use the DBMS_SQL package to open,
bind vars, fetch records and of course close the cursor.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

I am able to achieve it if the dynamic sql return only one row. If the query return more than one row,how do I achieve that.
Once I execute th query,how do I find that ,it has reurned more than 1 row and how do I bind it later???
I would appreciate ur ans in this rgd

Hello,

here is an example:

handle Integer;
dbms_return Integer;
tablename varchar(30);
colname varchar(30);
datatype varchar(9);
counter integer := 0;

BEGIN

handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(handle,
'select distinct table_name,column_name,data_type ' ||
'from user_tab_columns ', DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(handle, 1, tablename, 30);
DBMS_SQL.DEFINE_COLUMN(handle, 2, colname, 30);
DBMS_SQL.DEFINE_COLUMN(handle, 3, datatype, 9);


dbms_return := DBMS_SQL.EXECUTE(handle);

counter := 0;
loop
if DBMS_SQL.FETCH_ROWS(handle) = 0 then
exit;
else
DBMS_SQL.COLUMN_VALUE(handle, 1, tablename);
DBMS_SQL.COLUMN_VALUE(handle, 2, colname);
DBMS_SQL.COLUMN_VALUE(handle, 3, datatype);

counter := counter + 1;

... do everything you want with that values !!!
end if;
end loop;
n := counter;
DBMS_SQL.CLOSE_CURSOR(handle);
END;


Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com