Hi All,
I have this strange problem. I am user of two different database (both are same version 9i). I created a procedure and within this procedure i used a cursor to select the values, this is working very fine.
When i copy this procedure in other database and try to run the procedure, the procedure runs successful, but the cursor does not populate the values, when i run the query written in the cursor, query returns the correct values.
Another interesting thing is that when i run the cursor not within procedure it runs successfully but when i try to use within procedure it does not work.
Another thing is that if i run the same procedure with cursor but omitting the where clause the procedure works fine again, but with where it does not populate the values in the cursor and cursor row count is = 0.
Anyone have the answer please.
CREATE OR REPLACE PROCEDURE proc_dps_check
as
PM_TABLE_NAMES VARCHAR2 (50);
CURSOR pmC IS SELECT LOWER(RTRIM(LTRIM(tname))) AS v_TABLENAME FROM dps_prc;-- WHERE LOWER(RTRIM(LTRIM(tname))) IN (SELECT LOWER(RTRIM(LTRIM(TABLE_NAME))) FROM ALL_TABLES WHERE OWNER = 'TEST');
BEGIN
FOR I IN PMC LOOP
Dbms_Output.PUT_LINE(I.v_TABLENAME);
--Code goes here...
--COMMIT;
END LOOP;
END;
Thanks.