dbms_sql.parse
451285Feb 21 2006 — edited Feb 22 2006Well, what I am trying to do is that I have a procedure which has literals in them and so they get parsed and executed everytime. So I want to replace those with bind variables. But since i dont know the number of bind variables I will have, I cant use " open cur for sql using v1, v2...vn as I dont know the vn. So I am using dbms_sql.But the issue here is that it does this:
DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_id,':l_del_status', l_del_status);
DBMS_SQL.BIND_VARIABLE(cursor_id,':l_open_status_id', l_open_status_id);
for i in 1..p_bindvar_to_list.count loop
DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_to_list(i).bind_name, p_bindvar_to_list(i).value);
end loop;
dummy := DBMS_SQL.EXECUTE(cursor_id);
if DBMS_SQL.FETCH_ROWS(cursor_id) <> 0 then
DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_del_nw_item.WORK_ITEM_ID);
DBMS_SQL.CLOSE_CURSOR(cursor_id);
Now I think that since i am forced to dbms_sql.parse everytime, It will parse the sql everytime and my advantage of using bind variables is completely lost. Am I correct in my assumption? If so what is the way around this? How do I avoid dbms_sql.parse?
Thanks in advance!