DBMS_SQL vs EXEC SQL
180271May 14 2008 — edited May 14 2008I have always used the DBMS_SQl package for creating dynamic SQL. I have been told my DBA's that this is causing a strain on the DB (using too much shared pool) becuse many users are running it and to look for other ways to accomplish this. I had found the EXEC_SQL. Can someone tell me if using this is better/worse? See sample of what I do below:
EXEC_SQL.PARSE(cursor_number, LC$sql_order);
EXEC_SQL.DEFINE_COLUMN(cursor_number,1,LC$first_name,255);
LN$count := EXEC_SQL.EXECUTE(cursor_number);
While EXEC_SQL.FETCH_ROWS(cursor_number) > 0 Loop
EXEC_SQL.COLUMN_VALUE(cursor_number,1,LC$first_name);
End Loop ;
EXEC_SQL.CLOSE_CURSOR(cursor_number);