cursor query not working

Hi,

I had created a stored procedure with the cursor and was working correctly before but know it does not give me any execution error but neither gives the desired output.

Only thing that has changed is more tables are added to the schema. The Cursor query is to retrieve all the tables from schema. I have kept dbms_output statement before and after the cursor for loop to determine where it stops. The last statement it executes is the statement before the cursor for loop.

It would be great if anybody can help me solve this issue.

Thanks.

Could you post your code - from your description I can't think of anything.

CREATE OR REPLACE Procedure UpdateServiceID is

cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;

rows_processed INTEGER;
strSql VARCHAR2(256);

cursor c_tableName is select table_name from all_tables where owner = 'SysOperator' ;

BEGIN

for vc in c_tablename
LOOP

strSql := 'update ' || vc.TABLE_NAME || ' set ( serviceoperatorID ) = ( select opertorID from Services where serviceID = ServiceNo ) where ServiceNo is not null ' ;

-- dbms_output.put_line(strSql);
cur := dbms_sql.open_cursor;
dbms_sql.PARSE( cur , strSql , dbms_sql.v7);

rows_processed := dbms_sql.execute(cur);

dbms_sql.close_cursor(cur);

end loop;
commit;
EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || ' at pos ' || DBMS_SQL.LAST_ERROR_POSITION);

END;

MMmm - have not see this style of Dynamic SQL for some time.

I would advise you to look at EXECUTE IMMEDIATE this should do as you want.

let me know if this fails and I will dig out my old dynamic examples.

Thanks for the suggestion, I had already tried 'execute immediate' and still it does not load the cursor.

One more interesting thing I noticed, the query executes when logged in as 'sys'.

Or copying into another schema and making the necessary changes.

Feel free to give any suggestions/guess, as it may help me to narrow down on my issue.

Please tell me the version of oracle.
you have put a loop and endloop between the loop and endloop you are opening a cursor and closing it.
when you execute it is very fast and internally it is not getting closed immediatly. for that u can use immediate execute in oracle 8 and above and in oracle 9i you can use sys_refcursors.
thanks,