Procedure in package hangs sessions

From: Glen E. Siferd <siferd_at_admin.uwex.edu>
Date: Fri, 7 Aug 1998 15:46:42 -0500
Message-ID: <6qfp3j$hfc$1@news.doit.wisc.edu>

I'm experiencing frequent session hangs when submitting a procedure in a package that drops a table, creates a table from a dynamic query, then grants select privilege to PUBLIC. Seems to work fine as a standalone procedure from SQL*Plus. Any help is greatly appreciated. Procedure code is:

PROCEDURE dyntable (query_in IN CHAR, ret_value OUT INTEGER) IS
CURSOR col_cur (query_in IN CHAR) -- collect columns needed for query IS
SELECT UNIQUE mv.variable_id
FROM siferd.query_matrixes qm,
siferd.matrix_variables mv
WHERE qm.query_id = query_in
AND mv.matrix_id = qm.matrix_id
UNION
SELECT UNIQUE variable_id
FROM siferd.query_variables
WHERE query_id = query_in
;
CURSOR table_cur (query_in IN CHAR) -- collect table names needed for query IS
SELECT UNIQUE oracle_name
FROM siferd.variable_definitions vd,
siferd.query_matrixes qm
WHERE qm.query_id = query_in
AND SUBSTR(vd.variable_id,1,7) = qm.matrix_id UNION
SELECT UNIQUE oracle_name
FROM siferd.variable_definitions vd,
siferd.query_variables qv
WHERE qv.query_id = query_in
AND vd.variable_id = qv.variable_id
;
cur INTEGER := DBMS_SQL.OPEN_CURSOR; -- create dynamic SQL cursors col_list VARCHAR2(5000); -- strings for assembling DDL code table_list VARCHAR2(2000) := 'query_geographies f0'; join_list VARCHAR2(2000);
table_ind PLS_INTEGER := 0; -- counter for file aliases BEGIN
drop_object('table','temp_' || query_in); -- proc to drop temp tables in this schema
FOR col_rec IN col_cur(query_in) LOOP -- loop thru cursor to assemble list of columns
col_list := col_list || ', ' || 'v' || col_rec.variable_id; END LOOP;
FOR table_rec IN table_cur(query_in) LOOP -- loop thru cursor to assemble list of tables

table_ind := table_ind + 1; -- to query and join
table_list := table_list || ', ' || table_rec.oracle_name || ' f' ||
table_ind;

join_list := join_list || ' AND f0.geo_code = f' || table_ind ||
'.geo_code(+)';

END LOOP;
col_list := RTRIM(LTRIM(col_list, ', '), ', '); -- trim leading or trailing commas
table_list := RTRIM(LTRIM(table_list, ', '), ', '); DBMS_SQL.PARSE -- parse and execute DDL to create query table (cur, 'CREATE TABLE temp_' || query_in || ' UNRECOVERABLE' ||
' AS SELECT UNIQUE f0.geo_code, ' || col_list ||
' FROM ' || table_list ||
' WHERE f0.geo_code in ' ||
'(SELECT UNIQUE qg.geo_code ' ||
'FROM query_geographies qg '||
'WHERE query_id = ' || query_in || ')'

|| join_list, DBMS_SQL.V7);
DBMS_SQL.PARSE -- parse and execute GRANT statement (cur, 'GRANT SELECT ON temp_' || query_in || ' TO PUBLIC', DBMS_SQL.V7); DBMS_SQL.CLOSE_CURSOR(cur);
ret_value := 0; -- return success code to calling program EXCEPTION
WHEN OTHERS
THEN
DBMS_SQL.CLOSE_CURSOR(cur);
ret_value := 1; -- return failure code to calling program END dyntable;

Thanks! Received on Fri Aug 07 1998 - 15:46:42 CDT