Oracle Cursor Problem

[BilalGhazi]

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(LTR IM(tname))) AS v_TABLENAME FROM dps_prc;-- WHERE LOWER(RTRIM(LTR IM(tname))) IN (SELECT LOWER(RTRIM(LTR IM(TABLE_NAME)) ) FROM ALL_TABLES WHERE OWNER = 'TEST');

BEGIN

FOR I IN PMC LOOP
Dbms_Output.PUT _LINE(I.v_TABLE NAME);
--Code goes here...
--COMMIT;
END LOOP;
END;


Thanks.

[BilalGhazi]

HI ALL,

I HAVE FOUND THE PROBLEM, AND IM RESOLVING IT. THANKS

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(LTR IM(tname))) AS v_TABLENAME FROM dps_prc;-- WHERE LOWER(RTRIM(LTR IM(tname))) IN (SELECT LOWER(RTRIM(LTR IM(TABLE_NAME)) ) FROM ALL_TABLES WHERE OWNER = 'TEST');

BEGIN

FOR I IN PMC LOOP
Dbms_Output.PUT _LINE(I.v_TABLE NAME);
--Code goes here...
--COMMIT;
END LOOP;
END;


Thanks.

[debasisdas]

Hi
BilalGhazi
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.