SQL & PL/SQL

Hi,

We have a global temporary table which is populated by the first query in a procedure. The data in this temporary table is then used by a second query in the same procedure to insert rows into a second table. More queries follow which insert further rows and update one or more columns in this second table, and finally the second table is read and returned to the calling application as a sys refcursor supplied as an OUT parameter to the procedure. When the procedure is executed, the procedure appears to hang. It has been left by our developers for over an hour with still no results returned.

Extracting the queries out of the procedure and running them individually in SQL*Plus, they all run OK in approx 0.5 seconds each. Executing the procedures as a script has the same result. However, if I rem out the second query in the procedure, the procedure runs OK. re-enabling the second query in the procedure results in the procedure hanging again. The second query is an insert into ... select statement which is properly formed and executed OK outside of the procedure. There is nothing in the procedure other than 9 sql statements of either an insert into ... select type or an update statement. All tables are fully qualified and privileges of the calling user consist of direct grants on all tables accessed in the execution of the procedure.

Is there any other reason why a sql statement will run ok on its own and yet fail to execute when included as part of a procedure, other than direct grant issues surrounding roles and PL/SQL? We are running an Oracle 10.2.0.3 database.